What-If Analysis: Data Tables
What-if analysis feature of MS Excel
The What-if analysis feature of MS Excel helps in swapping multiple sets of data and comparing them side by side. The feature is very beneficial as it saves your time and makes your work easier.
Alt A W is the Excel shortcut that helps to get to What-If analysis quickly. Then you can push S / G / T for Scenario Manager / Goal Seek / Data Table respectively.
Scenario Manager is one of the tools of What-if feature and it helps you in comparing multiple sets of data. If you want to change two variables in your data then you can use this tool to do so. By using this tool, if you change the value of two variables it will automatically make the rest of the changes. In that case, you don’t have to do it manually.
Goal Seek is another tool of What-if analysis feature, which helps to adjust a value used in a formula to achieve a specific goal. It is very beneficial because with this tool one can easily adjust values in the formula.
A Data table is a range of cells in which you can change values in some of the cells and come up with different answers to a problem. Instead of creating different scenarios, you can create a data table to quickly try out different values for formulas.
Two Way Data Table
A two-variable data table is used when you want to see how the final result changes when two of the input variables change simultaneously.
In the example below, we want to find out how the Total Sales change if price and/or quantity of the product sold changes.
Total sales are the output cell in the data table. It’s calculated by multiplying the price with the quantity of product sold. Price and Quantity are input ranges in the data table in our example.
To create a Data Table for the given example, highlight the whole table including the output cell and input ranges(C6 to F11). Then click Alt to access the Ribbon Navigation, A for Data, W for what-if analysis, T for data table: Alt A W T.
In the “Data Table” window, put the price($C$2) for the row input cell and quantity ($C$3) for the column input cell. After that, click “Ok”.
Sometimes you might need to push F9 to recalculate the data table if it’s not automatic in your settings. You can see from the data table now, that if the price is $70 and the quantity is 20 –total sales are $1,400. If the price is $30 and the quantity is 10, the total sales are$300. And so on. You have the whole range of answers to analyze.
One Way Data Table – Horizontal or Vertical
- Horizontal / Vertical Data Tables HaveThe Same Logic As Two-Way Data Table, But Only For One Input Range.
In our example, we want to find the TotalSales, if only the price changes, but the quantity of sold products stays the same (C3=15).
As we have learnt already, we need to highlight the table together with the output cell and input range (C6-F7), and then use the shortcut Alt A W T (clicking one after another) to open the “Data Table” window.
Put the price ($C$2) for the row input cell, and leave the column input cell blank as it is a one-way horizontal data table. You would do the reverse for the one-way vertical data table, leaving the row input blank and filling the column input.
Great! Now you have variables for total sales depending on the price. The same logic would apply for vertical data tables.
Tips and tricks for making data tables in Excel
Here are a few tips and tricks from MagooshExcel Blog:
1. You can change the format of the table by going to the ‘format as table’ button in the home ribbon if you are bored with the old format of the table.
2. You can use zebra lines when you create data table as it will save your time because then you don’t have to manually format alternative rows in a different colour.
3. You can use structured references instead of cell references because structured references will automatically get updated when you add or remove rows.
4. You can use calculated columns as they will save your time. When you use calculated columns and then when you write a formula in one cell, it automatically gets filled in the rest of the cells.
5. If you want to convert the table back to named ranges, then you can use the ‘convert to range’ tool, as it will again save your time.
6. If you want to analyze your table then you can convert your export table into pivot table by choosing to summarize with the ‘pivot table’ tool.
7. You can use an export table to SharePoint list tool if you want to publish your data table.
8. If you want to print your data table then instead of adjusting print settings, you can select the table and then hit Ctrl+P. This way of printing table is much easier and it also saves your time.
Using data tables can save you tons of time. You can build it in less than a minute and see multiple scenarios all at one glance. Learn more Excel tips and tricks in our Excel Game, where you can learn and try at the same time.