Show All

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 example below, 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 example below, 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 example below, you would select the range C2:E5.

5. On the Data menu, click Table.
6. In the Row input cell box, enter the reference to the input cell for the input values in the row.

   In the example below, 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 example below, 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.