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
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.
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
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
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.
Jan 22 2008, 11:19 AM