TKB Regularly

Lunchtime learning

Use with care

Do make sure that any tips and suggestions work as you expect them to in your own particular circumstances.



Automate Excel with tables

TKB book on Excel essentials

Johnny Depp explains Excel OFFSET()

Excel 2010 - Do Slicers cut it?

Excel Camera - choosing multiple areas

Add emphasis to your reports - 2010 update

Google Docs spreadsheet - animated chart example

Excel - PowerPivot's Pivot Power

Excel - taking comments to heart

Lists and tables 2: data entry

Convert your overdraft to cash-in-hand

Excel countdown to Christmas - the pie chart as clock

Lists and tables: Excel's most underrated feature?

Using the Excel camera to assemble a report

Adding emphasis to Excel reports

Dealing with and avoiding circular references

Text functions - 2

Text functions - 1

Indirect function

Data tables

Interactive charts

Range names and implicit intersection

Two dimensional lookups


Portrait and landscape in one document 2 - the page numbers strike back

Portrait and landscape in one document

Automatic continued...


Data tables

This is a relatively unknown and little used feature in Excel, but can be very useful where you need to compare a range of possible inputs into a calculation. There are two types of data table: a 'one-way' table that allows several alternatives for a single input to be compared, and a 'two-way' table that caters for two changing inputs.

Let's look at a simple one-way table first. We'll imagine that we want to look at projected sales figures from a range of possible starting sales values. To start with we'll enter our three data values and in cell B4 the formula that calculates the Final sales value:

Our formula is:


Now let's assume that we want to know what the final sales would be for different initial sales figures. We can type in a list of different starting values, and immediately to the right of the first value in our table we type a reference to our formula cell: B4:

To create our data table, select the range of cells including our list of starting values and the cells immediately to the right. Then select the option Data, Table (Excel 2007: Data ribbon, Data Tools group, What-If Analysis, Data Table):

For a one-way data table where we have the list of values arranged vertically, we only need to enter the 'Column input cell' - this should be the cell that contains the value that is to be replaced in the formula with each one of the list of values. So, in our case, it is cell B1 that contains the 'Initial sales' value, and we have created a list of alternative values for B1. When we click the OK button, our table will be created:

The formulae in cells C8 to C17 are created as the array formula:


We can now change our original data values to see the results in our table. For example we can change the growth rate and number of years as follows:

As we said earlier, we can have one-way or two-way data tables. Let's assume that in our case we want to create a table that not only shows the final sales value for different initial sales values, but also shows the different values for different numbers of years. To do this we can start with our one-way data table but instead of entering the reference to the formula to the right of the first value in our table, we enter it in the cell immediately above the first value (B6). We then enter our different numbers of years to the right of the formula cell (say C6:G6). Then select the entire table from the formula cell at the top left hand corner. Again we use the Data Table option, but this time we need to enter a 'Row input cell' as well as a 'Column input cell'.

The 'Row input cell' will refer to B3, the cell that contains our Number of years, and the 'Column input cell will continue to refer to B1:

Get lots more Microsoft Office hints and tips

© 1998 The Knowledge Base. Disclaimer   Contact
Design by Reading Room Ltd 1998