Our editors pick the products and services we write about. When you buy through our links, TechRepublic may earn a commission.

Create an Excel data table to compare multiple results

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.

Figure A

exceldatatablea.jpg

We'll add a data table to this simple mortgage calculator.

Table A

Cell Formula Format
B4:B5
Currency
B6
General
B7
Percentage with two decimal places
E4 =B4-B5 Currency
E5 =IFERROR(PMT(B7/12, B6*12,-B4+B5),0) Currency
E6 =IFERROR(E5*B6*12,0) Currency
E7 =IFERROR(E6-E4,0) Currency

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.

Figure B

exceldatatableb.jpg

Add labels and then create the list of changing variables in the data table's first column.

The next step is to enter the data table's references as follows:

  1. Select B11 and enter =E5 (the cell that contains the monthly payment formula in the result cell section of your original sheet).
  2. Select C11 and enter =E6.
  3. Select D11 and enter =E7.

Figure C shows this step implemented.

Figure C

exceldatatablec.jpg

Add references to the original sheet's formulas.

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.

Figure D

exceldatatabled.jpg

Now, you're ready to generate the data table as follows:

  1. Select the data table range. In this case, that's A11:D29 (don't include the labels in row 10).
  2. Click the Data tab.
  3. Choose Data Table from the What-If Analysis option in the Data Tools group (Figure E).

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.

Figure F

exceldatatablef.jpg

Reference the input cell that changes--in this case, it's the interest rate in B7.

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.

Figure G

exceldatatableg.jpg

The data table shows payments and totals for many interest rates.

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 susansalesharkins@gmail.com.

Also see