If you want to view multiple possibilities within a Microsoft Excel calculation, the answer is to create a data table.
Editor's note on March 21, 2019: This Excel article was first published in February 2012. Since then, the author has updated the tip and images and provided demo files, and we have added a video tutorial and updated the related resources.
Sometimes one answer isn't enough--occasionally, you need to see multiple possibilities. When this happens, consider adding a data table to your sheet. 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 does changing an input value change the results? Instead of viewing multiple sheets, you can examine the possibilities with a quick glance at one data table. In this article, I'll show you how to implement a data table.
I'm using Office 365 Excel (desktop) on a Windows 10 64-bit system, but this feature is available in older versions. You can work with your own data or download the demonstration .xlsx and .xls files. You can view a data table in the browser, but you can't create one.
LEARN MORE: Office 365 Consumer pricing and features
How to create a mortgage calculator in Excel
To illustrate their use, we'll add a data table to the simple mortgage calculator shown in Figure A. As is, you can change any of the input values to change the results. We won't spend any time on building the calculator. You can download the demo workbook or refer to Table A for the formulas. The IFERROR() function isn't necessary but consider using it to handle errors if you plan to distribute the workbook to others.
|B7|| ||Percentage with two decimal places|
Now, let's suppose you want to compare results when the interest rate changes, not once, but for several rates--that's where a data table comes in. The first step is to enter the data table's labels. They should be easy to glean from the original sheet. In addition, the data table's first column should contain your variable. In this case, that's the changing interest rate.
I recommend using AutoFill to create a list of interest rates as shown in Figure B. If you must use formulas, be sure to replace the formulas with values because data tables don't evaluate formulas in the first column. It doesn't matter what values you begin and end the list with.
The next step is to enter the data table's references as follows:
- Select B11 and enter =E5 (the cell that contains the monthly payment formula in the result cell section of your original sheet).
- Select C11 and enter =E6.
- Select D11 and enter =E7.
Figure C shows this step implemented.
There's one more step to take before you generate the data table. The variable input cell--in this case, that's the interest rate value--must equal the first value in your data table. The first interest rate in the data table is 2.5%. You must change the input value in B7 to 2.5% before you generate the data table, as shown in Figure D. Do not skip this step unless the values are the same.
Now, you're ready to generate the data table as follows:
- Select the data table range. In this case, that's A11:D29 (don't include the labels in row 10).
- Click the Data tab.
- Choose Data Table from the What-If Analysis option in the Data Tools group (Figure E).
In the resulting dialog box, enter the input cell, B7, in the Column Input Cell because the interest rate values are in a column vs. a row (Figure F). This is the input value Excel will change for each row in the data table.
Click OK and format the rest of the table as necessary.
The Data Table option is in the Data Tools group.
The resulting data table in Figure G shows how the monthly payment and other values change as the interest rate changes. By combining the original calculator sheet with a data table, you have the best of both worlds. You can still use the calculator portion to change all of the input values, and the data table will reflect the changing values with the fixed set of interest ranges (2.5% to 11.50%) for each mortgage scenario.
Once the data table is in place, you can't change any of the referencing cells in columns B, C, and D. Excel protects those cells. In addition, the data table formulas are a series of =TABLE() functions--this function is just for show. You can't use it in any other context.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. 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 when helping readers, nor do I ask for a fee from readers I help. You can contact me at email@example.com.
- How to add a drop-down list to an Excel cell (TechRepublic)
- DevOps: A cheat sheet (TechRepublic)
- 20 pro tips to make Windows 10 work the way you want (TechRepublic download)
- https://www.zdnet.com/article/microsoft-office-365-for-business-everything-you-need-to-know/System update policy template download (Tech Pro Research)
- Microsoft Office 365 for business: Everything you need to know (ZDNet)
- The 10 most important iPhone apps of all time (Download.com)
- It takes work to keep your data private online. These apps can help (CNET)
- Programming languages and developer career resources coverage (TechRepublic on Flipboard)