An awesome powertool buried in Excel

I build complex spreadsheets, and I often have trouble fitting everything into Excel tables - I'd really like a way of simplifying things. At other times, I want to run a set of different scenarios through a spreadsheet, and I'd like to automate them to be self-updating.

There is a code-free solution to both these problems which has been there all along, but I didn't see it until a few years ago. It exploits data tables, an almost forgotten feature of Excel.

If you have the same problems as me, or if you simply want to learn new techniques, please read on. This has become one of my most important tools, so I'm going to take a little time and space to explain, and of course I have a demo for you.

Data tables - in brief

Data tables offer basic automation. Suppose we have a simple calculator, and we want to test the effect of different interest rates. We set up a data table, giving it our list of different rates, the cell we want Excel to put them into, and the results we want to store. Excel puts the rates into the interest rate cell one at a time, calculates the results, and copies them into a table with one row for each rate. Importantly, data tables recalculate every time Excel calculates, so you don't need to do this manually.

Automating

Data tables could be great for automating scenarios, except they may have 10-20 variables, not just one. If we could get the data table to update more than one variable, it could be very useful.

And there is a simple way.

We put all our scenario variables in a table, with one row per scenario, and one column per variable, like so.

The scenario selector cell in our calculation section tells us which row to use in the scenario table, ie it can be 1, 2, 3,  up to the number of scenarios. We can use the OFFSET command to read in each variable from the correct row in its column - you can see the formulae in blue next to the green cells above.

What this means is that we can change all the scenario variables - as many as we like - by changing just one (scenario selector) cell - so now we can use a data table to run through all the scenarios by putting 1, 2, 3... in the scenario cell, calculating, and pulling out the chosen results.

If this explanation isn't clear, see the demo workbook below.

But there's an even better use for data tables.

Simplifying complex spreadsheets using by calculating 'one at a time'

A big limitation of Excel is that it only offers 2-way row/column tables. I might have 1,000 product records, and for each product, I need to forecast 10 years of revenue, and each year's figures require 15 different calculations. What I really need is a 1000 x 10 x 15 table, ie a 3-way or even 4-way table.

Usually, people try to cram everything into the same rows as the data records, eg a table of 1,000 rows x many columns, which can get very messy.

However, data tables give us a neat way to add a third dimension. I'll illustrate with my example above. Suppose I set up a sheet which does the calculations for just one product. I can quite easily lay out my 10 years of 15 calculations each, in a table.

I put a record selector cell at the top. I use this cell to read in all the data for the selected product, using the OFFSET command as already illustrated above. So now I can calculate any product simply by putting its row number in the selector cell.

Then I use a data table to automatically run all the row numbers one by one through this cell, in a similar way to the scenarios above, storing the results, one row per product, on another sheet. (I call this - not very imaginatively - calculating 'one at a time'). Not only is it much, much simpler to understand and check, but it is also safer, in that

• I have a whole sheet to do the calculations for a data record, rather than one row 
• the same single set of calculation cells is used for every data record - there is no need to copy formulae down all the data rows and maybe get them wrong
• the data, calculations and results can all be on different sheets, avoiding the problems that happen when they are mingled together

Effectively we've added an extra dimension to Excel by giving ourselves a whole sheet to do each product calculation, rather than a single row.

If you've read this far..

..try the demos here (Data Tables.xls). You may think it's only a toy, so I built a full size example here (Data Tables In Use.xls), which has a couple of useful tips.

Is this worth exploring? All I can say is that I'm an actuary who does financial projections and who lives in Excel all day long. The technique above has made a huge difference to my work, making it simpler, safer and allowing me to build much more powerful models. I thoroughly recommend it as an essential tool for professional modelling in Excel.

Finally, VBA code can do the same as data tables, but more efficiently and with extra features, so you can build even more amazing models, but that's for another post.