Excel Data Tables are one of the What-if Analysis tools that we have available to aid our decision making. They don't require knowledge of any new fancy formulas and are super quick to build.
With Excel Data Tables we can perform what-if analysis with one or two variables, which makes it quick and easy to experiment and understand the outcome of different options.
Watch the Video
Download the Workbook
Enter your email address below to download the sample workbook.
One Variable Data Tables
In cell B12 (image below) I’ve used the FV function (future value) to calculate the amount we’ll have after 12 months of saving $100 at the beginning of each month at a 5% interest rate p.a., which results in $1233.
Note: The FV function syntax is:
=FV(rate, nper, [pmt], [pv], [type])
I’ll be adjusting the pmt argument in this one variable data table what-if analysis.
What if we increased our monthly savings amount by $10 or $20 or more? It’s easy to see the effect of the change in the savings amount [pmt] with a Data Table:
Creating Excel Data Tables
Set up a table that contains your variables going down the rows or across the columns. In this example my monthly payments are in column A.
Place your formula or a link to your formula in the cell above where your data table will place the values. In my case this is cell B12.
Next, select the cells containing the variables and the cells your results will be entered in. In this example it’s cells A12:B23. Go to the Data tab > What-if Analysis > Data Table, as shown below:
In the Data Table dialog box, shown below, select the cell that contains the variable referenced in the formula in cell B12, which is B8:
Note: As this is a single variable data table, I can leave the row input cell empty.
That’s it. Easy peasy!
Notice the formula bar in the image below contains the TABLE function. The only way the TABLE function can be entered in the worksheet is via the Data tab > What-if Analysis > Data Table. You won’t even find it in the Function Wizard.
Tip: Because the data table contains a formula, you can make changes to your variables and it will automatically update.
Two Variable Data Tables
We can compare changes in the saving amount [pmt] and the interest rate [rate] using a two variable data table. In the image below I have the interest rates across row 12 and the savings amounts in column A:
Note: your formula or a link to your formula must be in the top left cell of the table. In my case, cell A12.
The process is the same, except this time I need to select cells A12:H23 and in the Data Table dialog box I choose cell B6 as my row input cell and B8 as my column input cell:
Notice the TABLE formula in the image below contains two cell references, one for the row variable and one for the column variable:
Breakeven Data Tables
There are many uses for Data Tables. Another is to calculate and visualise breakeven. With the help of Conditional Formatting heat maps we can see the breakeven curve for profit per month (in the image below) runs from 1500 pizzas at $4.00 profit per pizza through to 1200 pizzas at $5 profit per pizza:
Spoiler: Of course, you could achieve the same results as a data table by inserting your formula in cells B37:F45 and simply reference the cells that contain the variables in row 36 and column A. But at least now when you stumble upon a workbook containing this mysterious TABLE function you’ll know what it is and how it got there 😉