Excel > Excel 2007 Advanced > What-If Analysis

Data Tables

A data table is a range of cells that displays results from a formula using different values. For example, a data table could be created to show monthly loan payments for different interest rates and loan length.

Join Excel School

There are two types of data table:

One-input Data Table: Displays the results of a formula for multiple values using one input cell. For example, you could show the monthly loan payments for multiple interest rates.

Two-input Data Table: Displays the results of a formula for multiple values using two input cells. For example, you could show the monthly loan payments for multiple interest rates and loan length.

Download data tables.xlsx to follow the tutorial.

Create a one-input data table

  1. Set up the table area
  2. There is a particular structure to adhere to when setting up the table area. Ensure that the formula is in the top row of the table and the input values are in the left column, as illustrated below

    one-way data table layout

  3. Select the table range including the formula and input values. This includes the blank cells below the formula where the table results will appear
  4. Click the Data tab on the Ribbon
  5. Click the What-If Analysis button in the Data Tools group and select Data Table
  6. The Data Table dialogue box appears

  7. Enter the cell reference for the input cell in the Column input cell field
  8. Data table inputs

  9. Click Ok

The results of the formula using each of the input values is displayed in the data table

It is possible to set up the data table with the data in a row instead of a column, in which case the cell reference would be entered in the Row input cell field.

Create a two-input data table

  1. Set up the table area
  2. It is important once again to structure the table area correctly. Ensure that the formula is in the top left cell of the table. The first input values should be in the left column and the second input values in the top row

    Two way data table

  3. Select the table range including the formula and both sets of input values. This includes the blank cells within the table where the table results will appear
  4. Click the Data tab on the Ribbon
  5. Click the What-If Analysis button in the Data Tools group and select Data Table
  6. The Data Table dialogue box appears

  7. Enter the cell reference for the first input cell in the Column input cell field, and the cell reference for the second input cell in the Row input cell field
  8. Inputs for data table

  9. Click Ok

The results of the formula using both sets of input values is displayed in the data table

Follow us on

Facebook  Twitter  You Tube