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.

## 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

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

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

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

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

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

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.