Do you ever use the Data Table feature in Excel? It’s one of the “What-if Analysis” tools, found on the Ribbon’s Data tab, along with Scenario Manager, and Goal Seek.
A data table lets you experiment with one or two variables in a formula, and see the results, in a compact layout.
Get Ready to Build a Data Table
Before you can build a data table, you need a formula on your worksheet. It should use cell references in its arguments, like this PMT function, in cell C7.
The variables are in cells C2, C3, and C4, and any one or two of those could be used in a data table.
One Variable Data Table
For example, you can use a one-variable data table to show the monthly payments for the loan, using the number of months, in cell C3, as the variable. The options for the months variable are entered in a column – B9:B13.
To create the table, select cells B7:C13, and use the Data table command on the Ribbon’s Data tab, with $C$3 as the Column Input Cell.
Not too surprisingly, the data table below shows that the payment is lower, if the number of months is higher.
Two Variable Data Table
In a two-variable data table, you could keep the number of months as the first variable, and add the interest rate as the second variable. In this example, the PMT function is in cell B7 – the top left corner of the data table.
- The interest rates are in row 7, across the top of the data table, and the row input cell is C2, where the annual interest rate is entered.
- The number of months are in column B, and the column input cell is C3, where the number of months is entered.
When you create the two-variable data table, it shows the monthly payments required for each combination of the two variables – from a 12 month loan at 2%, to a 72 month loan at 6% annual interest.
Download the Sample File
To download the sample file, and see the detailed instructions, please visit the Data Table page on my Contextures website.
Take the Data Tables Poll
Going back to the question that I asked at the start of this article – Do you use Data Tables? Please take this short poll, and let me know.