The last two months, we've reviewed Excel's what-if analysis tools, Goal Seek and Scenario Manager. This month, we'll continue with the mortgage example to explore the Data Table feature. We'll generate a data table that displays multiple interest and term options. This article completes the series on Excel's what-if tools.
I'm using Excel 2013 and Windows 7. For your convenience, you can download the example .xlsx and .xls files that contain the examples from the last two articles (although not all the solutions are supported by Excel 2003).
All three analysis tools are what-if tools. In other words, you can see the impact of changing input values without changing the actual data. In keeping with our first two articles, "How to use Excel's Scenario Manager analysis tool" and "How to use Excel's Goal Seek analysis tool," we'll use the Data Table feature to explore possible mortgage options by evaluating several interest rates and term values. Excel's Data Table tool works similarly to Goal Seek and Scenario Manager. You can use table values to replace variables in a formula or function. In this way, you can view what-if results without changing the original formula's references.
The mortgage calculator
Once again, we'll use the simple mortgage calculator shown in Figure A. Refer to Table A to create this sheet or download the example file. With the current input values of 6%, 180 months, and a principal of $200,000, a monthly payment is $1,687.71. Simply replace the input values, the interest rate, term, and principal to return a monthly payment.
We'll use this simple mortgage calculator to demonstrate Excel's Data Table.
Generating a data table
A data table is a range that evaluates changing variables in a single formula. In other words, it's a simple what-if analysis: how will changing an input value change the results? Let's find out. First, we need to generate the matrix shell using the static input values shown in Figure B. These values should be the values you want the function or formula to evaluate.
Add the matrix labels using input values you want the formula or function to evaluate.
The existing setup presents a bit of a problem—nothing we can't handle, though. I want you to see how easy it is to accommodate an existing sheet's configuration. You'll often have to work with an existing sheet that someone else provides. Reworking might be permissible, but it won't always be necessary.
Here's the problem, the feature needs to know what function or formula it's evaluating. Right now, the PMT() function in E4 isn't part of the matrix. The simplest solution is to create a quick link as follows:
- Select E4 and click Copy in the Clipboard group on the Home tab, or press [Ctrl]+[C].
- Select B6 (the corner of the matrix) and choose Paste Link (Figure C) from the Paste drop-down in the Clipboard group (Figure D).
With the input values in place, we're ready to generate the table as follows:
- Select the entire matrix area. In this case, that's B6:F15. Be sure to select the cell that contains the formula or function. In our example, the link to the function (B6).
- Click the Data tab. In Excel 2003, choose Table from the Data menu, and skip to step 4.
- From the What-If Analysis drop-down (in the Data Tools group), choose Data Table (Figure E).
- The Row input cell is the term value, so enter C4 (the original input value).
- The Column input cell is the interest rate, so enter B4 (Figure F).
- Click OK to generate the Data Table (Figure G).
Using the resulting table, you can quickly see a number of possibilities. For instance, at 4%, a 180-month loan will require a $1,479.38 monthly payment; at 2%, a 360-month loan will require a $739.24 monthly payment. You can see 36 mortgage configurations at a glance. To generate more, simply expand the input values in the matrix headers (row 6 and column B). The resulting values are negative, because the values are payments. You can format them any way you like.
This feature doesn't require two input variables. You can use only one by simply specifying only one (Figure F). In addition, the resulting values are the result of the TABLE() function; you can't use this function manually—it's just for show.
The Data Table feature is more flexible than you might realize. Read "Create an Excel data table to compare multiple results" to see a different, but perhaps more functional table using only one input value.
We've reviewed all three of Excel's what-if analysis tools: Goal Seek, Scenario Manager, and Data Table. Using the same example data, the mortgage calculator allows you to compare the three tools so you can determine which will work best for you.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at firstname.lastname@example.org.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.