Excel Article

How to get formulas to update automatically when new data is added to a plain table

Excel has a sophisticated Table feature which I refer to as the “Excel table” and there are also Pivot tables beloved by many. However in some cases a “plain” table is just easier or avoids certain pitfalls with the Excel table – a topic for a future article.

By a plain table I simply mean data that is entered into cells in adjacent rows for a row based table where data changes in the vertical direction and different data types in the horizontal direction. In some cases a column based table is visually easier to understand, particularly when the horizontal direction represents regular time such as weeks or months but for now we will stick to row based.

The following screenshot is a simple example, where a fictional runner Joe is recording his training distances.

Table 1

Now if Joe wants to know the total distance he has run, he might add a stats area alongside the plain table.

Table 2

He has achieved this by adding a sum formula in cell F2.

Table 3

Now the problem occurs when he extends his table, by adding some more distances.

Table 4

You will notice that the total distance has not changed. The reason is although the data has been extended, the formula in F2 has not changed. There is the error indicator in the top left of cell F2 indicating a potential problem – which is “Formula omits adjacent cells” and it is possible to get Excel to resolve the problem if you click on the “Update formula to include adjacent cells” in the error options. However if your stats include more items, such as average distance run, maximum distance, minimum distance, it can get tedious changing the formulas every time you add some new distances in to your table.

The key suggestion is as follows, when you enter the formula, extend the range of the formula beyond the bottom of the data by one row. The sub-row of cells underneath the data should be empty. So in this case the sum formula is over the range C3-C11 and not C3-C10. And as an additional tip – extend the fill colour of the table to include the empty row, which will give a good visual clue.

Table 5

Now when you want to extend the table, select the bottom cells of the table - the empty cells - and continue your selection down for the number of new enteries you want to add. In the example below the selected range is 2 columns by 3 rows.

Table 6

Once selected right click, select insert and choose shift cells down. Alternatively you can select the required number of rows, starting with the bottom (empty) row of the table. This approach is easier providing you do not have data alongside the table, which will be messed up by the new rows being added.

This is the result, showing how the formula has been automatically extended.

Table 7

Now we have the correct total distance

Table 8

It is also possible to do this, but putting the results at the bottom of the table instead of along the side, again leaving the one row gap.

Table 9

However Joe is sticking to a having the stats table on the side, and had now added some more stats. Of course, he should really be recording his running time!

Table 10

The next article extends this idea to graphs.

Screen shots created in Excel 2007 on Windows 7

Back to the top