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 😉
Excel CONCAT Function
Abbott Katz
For an Excel 365 non-data table alternative, enter in B13:
=FV(B6/12,B7,-A13:A23,0,1)
Tatiana Petkova
Hi, Mynda
I saw that if our variables are formulas (referred to the cell B8) – in column Saving Amount – the calculations aren’t correct. Why? Does it work only with values, not formulas?
Mynda Treacy
Hi Tatiana,
I can’t reproduce that error. Please post your question and sample Excel file on our forum where we can take a look.
Mynda
Steve Olson
What a great reminder. There was an old Journal of Accountancy article on this very thing by Stephanie Bryant years ago. An extra tip is that in a 2 variable input table the results cell must be referenced in the top left corner of the table. That confuses people, so the suggestion is to format that cell (A12) using Format Cells, Custom and typing in “Savings Amt” with the quotes in the Type: box. The formula is still the same, but the viewer will see it as a Savings Amt header instead of $1,671.
Mynda Treacy
Great tip, Steve! Thanks for sharing.
Colin
Hi Mynda,,
The last part of the tutorial regarding the heat map. I deleted from B37:F45 and attempted to replicate. If I select the entire table go to what-of analysis and choose cell Row input cell B31 and Column input cell B32, I seem to populate the table with “Profit/Mth” in every cell. Where am I going wrong?
Thanks,
Colin
Mynda Treacy
Hi Colin,
After deleting the data in cells B37:F45 you need to select cells A36:F45. The result should be profit per month adjusted for the different profit per pizza and pizzas per month figures.
Mynda
Danny Eisenbacher
I enjoyed your explanation. I was confused at first that the results of the two variable data table were different than the one variable data table until I realized the formula was different between the two of them. Once the formula was changed then the result is the same for the two examples.
Mynda Treacy
Thanks, Danny. Yes, two variable data tables have two references in the TABLE formula, as shown in the image just above the Breakeven Analysis heading. Glad you had a chance to play around with them.
Ron S
One point you haven’t mentioned is OneDrive.
.
I’ve seen many comments from people complaining about files stored in OneDrive. The (relatively) new Autosave on OneDrive is on by default. That means all changes are saved almost in real time. People are used to working on files saved to the local drive. You can open a file, do some “what-if” changes, then close WITHOUT saving so that file is “clean” the next time you open it. That is NOT possible on OneDrive unless you remember to turn off AutoSave.
.
It’s a small thing, but can be annoying if you are not aware of it
Mynda Treacy
Yes Ron, AutoSave is very annoying at first. We MVPs kicked up a big stink about it when it was first released, but I confess that I am a convert. If you don’t like AutoSave you can disable it upon opening the file by clicking the toggle in the top left or you can turn it off in the File tab > Options > Save > Save Workbooks group.
Mynda
Don
Mynda,
This type of Data Table is completely new to me. Can you explain how this type of a Data Table is different than the tables I’m used to using extensively with Insert > Table? The first thing I notice is that is I select a cell within the Data Table in your example is that I don’t see the ‘usual’ Table Tools > Design menu highlighted.
Thank you for highlighting this use for Data Tables with What-If’s.
Mynda Treacy
Hi Don,
These Data Tables have no relationship to the Excel Tables on the Insert tab which are designed to organise and help you reference and work with your data. Whereas Data Tables are an automatic what-if analysis tool.
Mynda