TKB Regularly

Lunchtime learning - Excel

Return to lunchtime menu

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


Search other Excel resources (AccountingWeb, Beancounters Guide, IT Counts):


Lists and tables: Excel's most underrated feature?


There's more than enough stress around at this time of year, so we've kept the December 2009 lunchtime learning feature simple, but hopefully very useful. Before we start, the bad news: if you are using a version of Excel prior to Excel 2003 this will not work. Lists were introduced in 2003 and 'upgraded' to tables in 2007.

What they do

Often an Excel list, chart or formula will refer to a range of cells. Inserting or deleting cells within the range generally results in the linked formulae or chart adjusting to the change, but adding data to the edges does not. In this example we have set a data validation list, a SUMIF() formula and a chart to refer to part of the block of data in A1 to C12:

If we now add data to row 13, none of our linked results adjust:

Now, we'll clear the data in row 13, right click in our range and choose 'Create List' It should automatically choose the data area A1:C12 and ask for confirmation that the list has headers.

Now, we'll add our row 13 data again:

The equivalent feature in Excel 2007 is a table and works in a similar way, but rather more colourfully.

Do be careful not to rely on the technique working without checking the results.

Get lots more Microsoft Office hints and tips



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