Calculate multiple results by using a data table

A data table is a range of cells in which you can change values in some in some of the cells and come up with different answers to a problem. A great example is using the PMT function with different loan amounts and interest rates to figure out how much of a loan you can afford for a home or a car. Experimenting with changeable values to produce different results is part of a discipline known as data analysis.

Data tables are part of a suite of commands that are called what-if analysis tools. When you use data tables, you are doing what-if analysis.

What-if analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet. For example, you can use a data table to vary the interest rate and term length that are used in a loan to determine possible monthly payment amounts.

Kinds of what-if analysis    There are three kinds of what-if analysis tools in Excel: scenarios, data tables, and goal seek. Scenarios and data tables take sets of input values and determine possible results. Goal Seek works differently from scenarios and data tables in that it takes a result and determines possible input values that produce that result.

Like scenarios, data tables help you explore a set of possible outcomes. Unlike scenarios, data tables show you all the outcomes in one table on one worksheet. Using data tables makes it easy to examine a range of possibilities at a glance. Because you focus on only one or two variables, results are easy to read and share in tabular form.

A data table cannot accommodate more than two variables. If you want to analyze more than two variables, you should instead use scenarios. Although it is limited to only one or two variables (one for the row input cell and one for the column input cell), a data table can include as many different variable values as you want. A scenario can have a maximum of 32 different values, but you can create as many scenarios as you want.

For information about what-if analysis, see Introduction to What-If Analysis.

Data table basics

You can create one-variable or two-variable data tables, depending on the number of variables and formulas that you want to test.

One-variable data tables    Use a one-variable data table if you want to see how different values of one variable in one or more formulas will change the results of those formulas. For example, you can use a one-variable data table to see how different interest rates affect a monthly mortgage payment by using the PMT function. You enter the variable values in one column or row, and the outcomes are displayed in an adjacent column or row.

In the following illustration, cell D2 contains the payment formula, =PMT(B3/12,B4,-B5), which refers to the input cell B3.

Data table with one variable

Two-variable data tables    Use a two-variable data table to see how different values of two variables in one formula will change the results of that formula. For example, you can use a two-variable data table to see how different combinations of interest rates and loan terms will affect a monthly mortgage payment.

In the following illustration, cell C2 contains the payment formula, =PMT(B3/12,B4,-B5), which uses two input cells, B3 and B4.

Data table with two variables

Data table calculations    Data tables are recalculated whenever a worksheet is recalculated, even if they have not changed. To speed up calculation of a worksheet that contains a data table, you can change the Calculation options to automatically recalculate the worksheet but not the data tables. See the section Speed up calculation in a worksheet that contains data tables.

Create a one-variable data table

A one-variable data table has input values that are listed either down a column (column-oriented) or across a row (row-oriented). Formulas that are used in a one-variable data table must refer to only one input cell.

  1. Type the list of values that you want to substitute in the input cell either down one column or across one row. Leave a few empty rows and columns on either side of the values.

  2. Do one of the following:

    • If the data table is column-oriented (your variable values are in a column), type the formula in the cell one row above and one cell to the right of the column of values. The one-variable data table illustration shown in the Overview section is column-oriented, and the formula is contained in cell D2.
      If you want to examine the effects of various values on other formulas, type the additional formulas in cells to the right of the first formula.

    • If the data table is row-oriented (your variable values are in a row), type the formula in the cell one column to the left of the first value and one cell below the row of values.
      If you want to examine the effects of various values on other formulas, type the additional formulas in cells below the first formula.

  3. Select the range of cells that contains the formulas and values that you want to substitute. Based on the first illustration in the preceding Overview section, this range is C2:D5.

  4. On the Data tab, in the Data Tools group or Forecast group (in Excel 2016), click What-If Analysis, and then click Data Table.

  5. Do one of the following:

    • If the data table is column-oriented, type the cell reference for the input cell in the Column input cell box. Using the example shown in the first illustration, the input cell is B3.

    • If the data table is row-oriented, type the cell reference for the input cell in the Row input cell box.

      Note: After you create your data table, you might want to change the format of the result cells. In the illustration, the result cells are formatted as currency.

Add a formula to a one-variable data table

Formulas that are used in a one-variable data table must refer to the same input cell.

  1. Do one of the following:

    • If the data table is column-oriented (your variable values are in a column), type the new formula in a blank cell to the right of an existing formula in the top row of the data table.

    • If the data table is row-oriented (your variable values are in a row), type the new formula in a blank cell below an existing formula in the first column of the data table.

  2. Select the range of cells that contains the data table and the new formula.

  3. On the Data tab, in the Data Tools group or Forecast group (in Excel 2016), click What-If Analysis, and then click Data Table.

  4. Do one of the following:

    • If the data table is column-oriented, type the cell reference for the input cell in the Column input cell box.

    • If the data table is row-oriented, type the cell reference for the input cell in the Row input cell box.

Create a two-variable data table

A two-variable data table uses a formula that contains two lists of input values. The formula must refer to two different input cells.

  1. In a cell on the worksheet, enter the formula that refers to the two input cells.

    In the following example, in which the formula's starting values are entered in cells B3, B4, and B5, you type the formula =PMT(B3/12,B4,-B5) in cell C2.

  2. Type one list of input values in the same column, below the formula.

    In this case, type the different interest rates in cells C3, C4, and C5.

  3. Enter the second list in the same row as the formula, to its right.

    Type the loan terms (in months) in cells D2 and E2.

  4. Select the range of cells that contains the formula (C2), both the row and column of values (C3:C5 and D2:E2), and the cells in which you want the calculated values (D3:E5).

    In this case, select the range C2:E5.

  5. On the Data tab, in the Data Tools group or Forecast group (in Excel 2016), click What-If Analysis, and then click Data Table.

  6. In the Row input cell box, enter the reference to the input cell for the input values in the row.
    Type cell B4 in the Row input cell box.

  7. In the Column input cell box, enter the reference to the input cell for the input values in the column.
    Type B3 in the Column input cell box.

  8. Click OK.

Example    A two-variable data table can show how different combinations of interest rates and loan terms will affect a monthly mortgage payment. In the following illustration, cell C2 contains the payment formula, =PMT(B3/12,B4,-B5), which uses two input cells, B3 and B4.

Data table with two variables

Speed up calculation on a worksheet that contains data tables

  1. Do one of the following:

    • In Excel 2007, click the Microsoft Office Button Office button image , click Excel Options, and then click the Formulas category.

    • In all other versions, click File > Options > Formulas.

  2. In the Calculation options section, under Calculate, click Automatic except for data tables.

    Tip: Alternatively, on the Formulas tab, in the Calculation group, click the arrow on Calculation Options, and then click Automatic Except Data Tables.

Note: When you select this calculation option, data tables are skipped when the rest of the workbook is recalculated. To manually recalculate your data table, select its formulas and then press F9.

What next?

You can use a few other Excel tools to perform what-if analysis if you have specific goals or larger sets of variable data.

Goal Seek

If you know the result that you want from a formula, but are not sure what input value the formula needs to get that result, use the Goal Seek feature. See the article Use Goal Seek to find the result you want by adjusting an input value.

Excel Solver

You can use the Excel Solver add-in to find the optimal value based on a number of variables. Solver works with a group of cells (called decision variables or simply variable cells) that are used in computing the formulas in the objective and constraint cells. Solver adjusts the values in the decision variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell. See the article Define and solve a problem by using Solver.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×