Data Tables in Excel
What are Data Tables for? I’m glad you asked. Data Tables are a subset of “What if” analysis. You can try different data inputs and see the results in a Data Table. Data Tables in Excel are on the Data ribbon, in the Forecast group, in the What-if Analysis dropdown list. Goal Seek and Scenario Manager are there too.
Goal Seek and Scenario Manager focus on getting a single answer. Unlike them, a Data Table can take one or two inputs and show the results of one calculation, or several, for each of the different values. That makes it easier to present the results as a graph.
Data Tables come in two forms: One-input and two-input. The one-input format can be vertical (more common) or horizontal
In all cases, you set up a formula, feed it the one or two inputs, and get a list of the corresponding results of the formula. The formula can be as complicated as you like, as long as it produces a single answer.
A One-input Data Table (column)
The general layout of a column-wise one-input Data Table is:
The first column always has a blank first cell, then the sequence of inputs you want Excel to use in the formula. The formula itself must be the top cell in the second column. There can be more columns, each with its own formula in the top cell. Those formulae must all use an input cell outside the data table (“Column Input Cell” in the diagram above).
To run the data table, start by selecting the whole Data Table range. If you don’t you will get a misleading message later. Example: Select the range D2:E5:
The Payment formula in E2 =PMT(B3/12,B4,-B5) =PMT(Interest Rate/12, Term,-Loan Amount)
Click on the Data ribbon. In the Forecast group, click “What if Analysis”. In the dropdown list, choose Data Table… The Data Table dialog will open.
Because this is a column-wise Data Table, specify only the Column input cell:
When you click OK, Excel will take the rates in column D (9.00%, 9.25%, 9.50%) and successively put them into cell B3 (the Column Input Cell). Then it will take the result in the orange Formula cell E2, and write it into column E next to the corresponding rate.
Another One-input Data Table (row version)
It is not as popular as the column-wise version, but the general layout of a row-wise one-input Data Table is:
The first row always has a blank first cell, then the sequence of inputs you want Excel to use in the formula. The formula itself must be the leftmost cell in the second row. There can be more rows, each with its own formula in the leftmost cell. Those formulae must all use an input cell (the same cell) somewhere outside the data table. That is the “Row Input Cell” in the diagram above.
Example:
The Effective Rate formula in E3 =EFFECT(B3,12) =EFFECT(Interest Rate, Periods)
The Payment formula in E4 =PMT($B$3/12,$B$4,-B5) =PMT(Interest Rate/12, Term,-Loan Amount)
To run the data table, start by selecting the whole Data Table range. If you don’t you will get a misleading message later. Select the range E2:H4.
Click on the Data ribbon. In the Forecast group, click “What if Analysis”. In the dropdown list, choose Data Table… The Data Table dialog will open:
Because this is a row-wise Data Table, specify only the Row input cell:
When you click OK, Excel will take the rates in row 2 (9.00%, 9.25%, 9.50%) and successively put them into cell B3 (the Column Input Cell). Then it will take the results in the orange Formula cells E3:E4, and write them into rows 3 and 4 under the corresponding rate:
A Two-input Data Table
The general layout of a two-input Data Table is:
The top-left cell in the data table always contains the formula. Unlike with a one-input data table, only one formula is possible in a two-input data table. The formulae must use two input cells outside the data table (the “Column Input Cell” and the “Row Input Cell” in the diagram above).
The rest of the top row contains the sequence of row inputs you want Excel to use in the formula. The rest of the left column contains the sequence of column inputs you want Excel to use in the formula.
For example, for our mortgage loan analysis, we might like to vary two variables in the formula, the interest rate and the term:
The Payment formula in D2 =PMT(B3/12,B4,-B5) =PMT(Interest Rate/12, Term,-Loan Amount)
To run the data table, start by selecting the whole Data Table range. If you don’t you will get a misleading message later. In our Example above, select the range D2:F6.
Click on the Data ribbon. In the Forecast group, click “What if Analysis”. In the dropdown list, choose Data Table… The Data Table dialog will open.
Because this is a two-input Data Table, specify both the Row and Column input cells:
When you click OK, Excel will take the rates in column D (9.00%, 9.25%, 9.50%, 9.75%) and put them in turn into cell B3 (the Column Input Cell). Excel will also take the terms in row 2 (180 and 360) and put them into cell B4 (the Row Input Cell). For each case, it will take the result in the orange Formula cell D2, and write it into the corresponding row and column in the range E3:F6.
Notice that if we halve the term, the monthly payment is much less than double. Such is the effect of compound interest.