What-If Analysis – Data Table

What-If Analysis

On the Data ribbon in Microsoft Excel is the What-If Analysis collection of tools, which include Scenario Manager, Goal Seek, and Data Table. Each of these tools offers a unique way to approach data analysis that includes unknown variables or where you want to see a number of different outcomes.

Data Table

The final tool under Scenario Manager is the Data Table tool which allows you to see the resulting values from numerous combinations of two variables. To start a Data Table, enter two values in any cells and then enter your equation in a third cell. In the example below, cells B3 and B4 are the input variables and the equation in cell D5 is =2*B3 +3*B4^2, which with the input values of 1 and 2, results in a value of 14.

What-If Analysis - Data Table

Now, if you’d like to see what the result of the equation is for a range of each input variable, you can enter those values in the row and column adjacent to the cell where your formula is. Enter 5, 10, 15, 20, and 25 in cells E5:I5, and 2, 4, 6, 8, 10 in cells D6:D10. Once you have these ranges entered, it is usually helpful to change the initial equation and input values to have white font so that they can’t be seen. Next, highlight a range of cells including the cell with the formula and both variable ranges. In this case, that would be cells D5:I10.

What-If Analysis - Data Table

Then you can go to Scenario Manager > Data Table to fill in the values in your data table. For the Row input cell, select the variable which you want to substitute 5, 10, 15, 20, and 25 in for. In this case, it’s the first variable, cell B3. Next, for the Column input cell, which is the variable which will have 2, 4, 6, 8, and 10 plugged in for it, select B4.

What-If Analysis - Data Table

When you click OK, all of the cells in the range will be populated with the results of the equation that you get from pairing each value on the row with the value in the column. This can be helpful in situations where you know that variables can have a range of values and you want to see the sensitivity of the result from the formula. In this case, the values range from 22 for the combination of 5 and 2 all the way up to 350 when 25 is paired with 10.

What-If Analysis - Data Table

Was this article helpful?

Related Articles

Leave a Reply