Excel Tip of the Week #22 - Data tables

This week we have a Developer post about a powerful feature, which allows you to see what your workbook would look like if you changed some inputs without actually having to change anything.

This is a little longer (and a little more complex) than usual so settle in for a look into the world of What-If analysis.

What a data table does

The point of a data table is to show how the result of a particular formula would change under different sets of inputs.  It allows you to easily compare and contrast different scenarios against one another without having to actually change inputs.

For a simple formula like =A1+B1, you could do this just by replicating the formula over and over.  Data tables are best used in large, complex calculations that include a lot of steps and which may even spread onto more than one worksheet.  Common examples would be cash flow forecasts, models, net present value calculations, and so on.  In these cases, replicating the entire workbook for each trial set of inputs is laborious and impractical, so we use a data table instead.

For this Tip, we will be using the attached workbook (at the bottom of the page), which has a (relatively simple) NPV calculation in it.  The actual details of the calculation aren't that important but feel free to look over them to get a feel for what's being done.

How it works with one variable

Data tables need to be laid out in a very specific way to work.  Look at the example on the first tab of the attached workbook.  The layout is like this:

The different trial inputs are shown in a column at the left.  Above that and to the right is the formula that will be used.  The cells in blue are left blank - those are the ones that will be filled in by the data table feature.

If we want to amend the trial inputs later, that's fine.  However adding more inputs is difficult and requires recreating the data table from scratch, so make sure you have enough spaces for all the different trial scenarios you want to use.

Once the table is laid out, select the whole thing (including the formula, the trial values, and the empty cells).  Then go to Data => What-If Analysis, and select "Data Table".

In this example, we are using only set of variable inputs, which are in a column, so we need to complete the field "Column input cell", in this case specifying that the column of inputs should be trialled instead of C4.

Press OK and the blue area will be filled out with all the outputs that correspond to the scenario inputs.  Check some by hand - they will all match exactly.

How it works with two inputs

The layout for two variables is just as precise.  It needs to look like this:

Here, we have two sets of inputs and one formula.  One set of inputs is in a column (green) and one is in a row (red).  The pink cell has the formula to experiment with and just like before the blue area is where the output will appear.

We complete the data table just like before - select the whole boxed region, then access the menu option.

Here, we have two sets of inputs, so we need to complete both boxes in the resulting menu.  Make sure you get the inputs the right way around!

Press OK and your data table is complete.

Some notes of caution

If you happen to look in the formula bar on a Data table output, you will see something like {=TABLE(,C4)}.  This isn't a true formula and moreover will complain if you try and overwrite it, edit it, or use it elsewhere in the workbook - so don't!  The only way to access this feature safely and correctly is as described above.

Secondly, a data table is a very, very heavy drain on your computer's processor.  Adding one to a workbook like the example isn't noticeable, but put a larger data table into a large, multi-sheet modelling workbook, and you will slow down the calculation speed considerably.  It's often better to either turn off calculations or paste values the results to avoid this slow down.

Have a try at the two examples and the practise task in the attachment - you could learn something very valuable indeed.

Previous post - LEFT, RIGHT, MID 
TOTW index
Next post - Evaluate Formula and Formula Auditing

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 Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.