by Svetlana Cheusheva, updated on
The tutorial shows how to use data tables for What-If analysis in Excel. Learn how to create a one-variable and two-variable table to see the effects of one or two input values on your formula, and how to set up a data table to evaluate multiple formulas at once.
You have built a complex formula dependent on multiple variables and want to know how changing those inputs changes the results. Instead of testing each variable individually, make a What-if analysis data table and observe all possible outcomes with a quick glance!
In Microsoft Excel, a data table is one of the What-If Analysis tools that allows you to try out different input values for formulas and see how changes in those values affect the formulas output.
Data tables are especially useful when a formula depends on several values, and you'd like to experiment with different combinations of inputs and compare the results.
Currently, there exist one variable data table and two variable data table. Although limited to a maximum of two different input cells, a data table enables you to test as many variable values as you want.
Note. A data table isn't the same thing as an Excel table, which is purposed for managing a group of related data. If you are looking to learn about many possible ways to create, clear and format a regular Excel table, not data table, please check out this tutorial: How to make and use a table in Excel.
One variable data table in Excel allows testing a series of values for a single input cell and shows how those values influence the result of a related formula.
To help you better understand this feature, we are going to follow a specific example rather than describing generic steps.
Suppose you are considering depositing your savings in a bank, which pays 5% interest that compounds monthly. To check different options, you've built the following compound interest calculator where:
And now, let's do a simple What-If analysis to see what your savings will be in 5 years depending on the amount of your initial investment, ranging from $1,000 to $6,000.
Here are the steps to make a one-variable data table:
=B8
Tip. If you want to examine the impact of the variable values on other formulas that refer to the same input cell, enter the additional formula(s) to the right of the first formula, as shown in this example.
Now, you can take a quick look at your one-variable data table, examine the possible balances and choose the optimal deposit size:
The above example shows how to set up a vertical, or column-oriented, data table in Excel. If you prefer a horizontal layout, here's what you need to do:
A two-variable data table shows how various combinations of 2 sets of variable values affect the formula result. In other words, it shows how changing two input values of the same formula changes the output.
The steps to create a two-variable data table in Excel are basically the same as in the above example, except that you enter two ranges of possible input values, one in a row and another in a column.
To see how it works, let's use the same compound interest calculator and examine the effects of the size of the initial investment and the number of years on the balance. To have it done, set up your data table in this way:
=B8
At this point, your two variable data table should look similar to this:
If you wish to evaluate more than one formula at the same time, build your data table as shown in the previous examples, and enter the additional formula(s):
For the "multi-formula" data table to work correctly, all the formulas should refer to the same input cell.
As an example, let's add one more formula to our one-variable data table to calculate the interest and see how it is affected by the size of the initial investment. Here's what we do:
=B8-B3
Voilà, you can now observe the effects of your variable values on both formulas:
To effectively use data tables in Excel, please keep in mind these 3 simple facts:
=TABLE(, B3)
where B3 is the column input cell.=TABLE(B6, B3)
where B6 is the row input cell and B3 is the column input cell.The TABLE function is entered as an array formula. To make sure of this, select any cell with the calculated value, look at the formula bar, and note the {curly brackets} around the formula. However, it is not a normal array formula - you can't type it in the formula bar nor can you edit an existing one. It is just "for show".
As mentioned above, Excel does not allow deleting values in individual cells containing the results. Whenever you try to do this, an error message "Cannot change part of a data table" will show up.
However, you can easily clear the entire array of the resulting values. Here's how:
Done! :)
Since it is not possible to change part of an array in Excel, you cannot edit individual cells with calculated values. You can only replace all those values with your own one by performing these steps:
This will insert the same value in all the selected cells:
Once the TABLE formula is gone, the former data table becomes a usual range, and you are free to edit any individual cell normally.
If a large data table with multiple variable values and formulas slows down your Excel, you can disable automatic recalculations in that and all other data tables.
For this, go to the Formulas tab > Calculation group, click the Calculation Options button, and then click Automatic Except Data Tables.
This will turn off automatic data table calculations and speed up recalculations of the entire workbook.
To manually recalculate your data table, select its resulting cells, i.e. the cells with TABLE() formulas, and press F9.
This is how you create and use a data table in Excel. To have a closer look at the examples discussed this this tutorial, you are welcome to download our sample Excel Data Tables workbook. I thank you for reading and would be happy to see you again next week!
Table of contents