Hello and a very happy new year from the Excel Tip of the Week! This week, we have a Developer post in which we are taking a definitive re-look over the powerful and intricate data analysis tool, data tables. This topic was most recently covered in TOTW #119.
What is a data table?
A data table lets you quickly populate a table of outputs for a range of different what-if scenarios. In other words, you can see how varying an input (or two inputs) would alter a given outcome.
While formulas might be suitable for populating such a table for a simple calculation, a more complex calculation process - such as a model - can't really do this and you might be stuck with changing the inputs one at a time to see what happens. But fear not! A data table can automate the process of altering inputs and measuring outputs for a given complex calculation.
How do you make a data table?
These tables require a very specific layout. There is one version for if you are just altering one variable, and one if you are altering two.
This is the layout for if we have one variable - the colours and border have been added to aid in clarity. The sections are:
Pink - A cell linked to the output we want to monitorGreen - A range of possible inputs we want to try outBlue - The space that will be populated by the data table(the white cell is not used)
This is the layout for if we have two different variables that we want to vary in our sensitivity analysis. The sections are:
Pink - A cell linked to the output we want to monitor. Note that this is in a different position to in the first example.Green - The range of possible inputs we want to try for our first input variableRed - The range of possible inputs we want to try for our second input variableBlue - The space that will be populated by the data table
In both cases, once we have set up the range, we must select the entire thing - including all the sections shown in the pictures here - and then go to Data => What-If Analysis => Data Tables. We will get this box:
We need to program Excel with the right inputs. The ROW INPUT CELL will be where the red inputs (in a row) are used; this box is left blank if you have only one variable. The COLUMN INPUT CELL is where the green inputs (in a column) will go.
When you are all done, you can hit OK and your table will be populated with values. There is a file attached at the foot of this post for you to try this out for yourself.
Working with data tables
Data tables require a lot of power to recompute - Excel has to recalc the entire calculation tree for each and every scenario - and in larger models can be a deadly drag. Under Formulas => Calculation Options, there is a button to set calculation to "Automatic Except for Data Tables", which you should enable.
You can't delete or edit individual data table results - if you want to make any changes, you need to delete all the results and start over. You can, however, change the stuff around the outside - so for example edit the trial values in the green and red areas, or change which output is referred to by the pink formula.
Using data tables to generate scenarios
If you are generating random data of some kind - with some kind of RAND function - you can use data tables to automate the process of generating lots of different sets of random data. To do this, you just need to create a data table which uses any blank cell as the "input cell" and which just uses numbers like 1,2,3 as the "input variables" in the green range - the point is, simply slotting any number into a blank cell triggers a recalculation of all the RAND variables in your random data and generates a new scenario. This can be used to analyse sensitivity with a kind of Monte Carlo analysis. If you are an Excel Community member, you can see me using a version of this to solve a problem from the ModelOff Excel competition in this webinar. The data tables part starts at about 37:00.
Previous post - Games compendiumTOTW index Next post - Nested dropdowns
This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.