Data tables are part of a suite of commands that are sometimes called what-if analysis tools. A data table is a range of cells that shows how changing certain values in your formulas affects the results of the formulas. Data tables provide a shortcut for calculating multiple versions in one operation and a way to view and compare the results of all of the different variations together on your worksheet.In this article

Overview

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

One-variable data tables Use a one-variable data table if you want to see how different interest rates affect a monthly mortgage payment. In the following example, 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 A two-variable data table can show how different interest rates and loan terms will affect the mortgage payment. In the following example, 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 .

Create a one-variable data table

You must design one-variable data tables so that input values 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 an 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.
  2. Do one of the following:
    • If the data table is column-oriented, type the formula in the row above the first value and one cell to the right of the column of values. Type any additional formulas to the right of the first formula.
    • If the data table is row-oriented, type the formula in the column to the left of the first value and one cell below the row of values. Type any additional formulas below the first formula.
  3. Select the range of cells that contains the formulas and values that you want to substitute.
  4. On the Data tab, in the Data Tools group, 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.

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

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, type the new formula in a blank cell to the right of an existing formula in the top row of the table.

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

  2. Select the data table, including the column or row that contains the new formula.
  3. On the Data tab, in the Data Tools group, 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

Two-variable data tables use only one formula with 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, where the formula's starting values are entered in cells B3, B4, and B5, you would type the formula =PMT(B3/12,B4,-B5) into cell C2.

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

    In the example below, you would type the different interest rates into cells C3, C4, and C5.

  3. Type the second list in the same row, to the right of the formula.

    In the following example, you would type the loan terms (in months) into cells D2 and E2.

  4. Select the range of cells that contains the formula and both the row and column of values.

    In the following example, you would select the range C2:E5.

  5. On the Data tab, in the Data Tools group, 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.

    In the following example, you would 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.

    In the following example, you would type B3 in the Column input cell box.

  8. Click OK.

Example A two-variable data table can show how different interest rates and loan terms will affect the mortgage payment. In the following example, 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 with data tables

  1. Click the Microsoft Office Button button image, click Excel Options, and then click the Formulas category.
  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 tables, select the formula and press F9.



See also: