Guides

## Related Tutorials

Excel > Excel 2003 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.

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.

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

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 Data > Table
5. The Data Table dialogue box appears

6. Enter the cell reference for the input cell in the Column input cell field
7. 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.

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 Data > Table
5. The Data Table dialogue box appears

6. 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
7. Click Ok

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