Software optimize

Create an Excel data table to compare multiple results

Suppose you want to view multiple possibilities within an Excel calculation. The answer is to create 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 does changing an input value change the results? You can examine the possibilities with a quick glance. (A data table isn't the same thing as the new table feature on the Insert tab.)

To illustrate their use, we'll add one to the simple mortgage calculator shown below. As is, you can change any of the input values to change the results. (It's generally a good practice to separate input and result cells.) 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 if you plan to distribute the workbook to others. This function is new to Excel 2007; Excel 2003 users can use ISERROR() to inhibit errors.

Table A: Calculator formulas

Cell

Formula

Format

B4:B5

Currency

B6

General

B7

Percentage

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 changing interest rates - that's where a data table comes in. First, enter the data table's labels. They should be easy to glean from the result cells in your original sheet. In addition, the data table's first column should contain your variable. In this case, that's the changing interest rate. I used AutoFill to create this list. If you use formulas in the data table's first column, be sure to paste with values - data tables can't use formulas in the first column.

Now you're ready 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.

You're ready to create the data table, but there's one last step. 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. Do not skip this step.

To generate the data table, do the following:

  1. Select the data table range. In this case, that's A11:D30.
  2. Click the Data tab. In Excel 2003, choose Table from the Data menu and skip to step 4.
  3. Choose Data table from the What-If Analysis option in the Data Tools group.
  4. In the resulting dialog box, enter the input cell, B7, in the Column Input Cell (because the interest rate values are in a column, versus a row). This is the input value Excel will change for each row in the data table.
  5. Click OK and format the rest of the table as necessary.

The resulting data table shows how the monthly payment and other values change as the interest rate changes. By combining the original calculator 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 12%) for each mortgage scenario.

Once the data table is in place, you can't change any of the referencing cells (columns B, C, and D in this data table). 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.

About

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.

4 comments
tufte
tufte

What exactly is the advantage of doing this? It doesn't seem to me that it would be too hard to put the formulas into a range that match this.

KJSanders
KJSanders

Excellent article, but I need to study in depth. Where do we download the demo workbook?

SianteJ
SianteJ

This really opens up a new view for my use of and for Excel.