Working with ranges that grow or shrink

Always insert new rows (or columns) in the middle of a range, then re-sort or re-arrange if necessary. Instead of adding a row/column at the end of a range, which can 'blow up' your math because functions to not 'adjust' to include the new row/column, add the row/column in the middle of the range. The function will expand to include the new row/column. Then rearrange (easiest by re-sorting) the range. This will typically be faster and definitely safer than re-doing math on a sheet.

Use a 'bookend' row or column between the end of the data and the formula, for example:

1 July2 123 104 85 166 7 =SUM(A2:A6)

The 'dummy' row is the last row referenced by the function. Any 'real' data row inserted at the end of the data is, therefore, technically in the middle of the range and will therefore be included.

Tips for "bookends":

Leave bookend cell content empty -- do not use a 'filler' character (e.g. '------'). Filler characters can 'throw off' more functions than an empty cell.

You can even hide the bookend column or row so it doesn't appear on the sheet.

Note this method, while it seems to be the simplest, is fraught with danger. This is a 'rigged' workaround, and it works in many situations. But be careful of how you write your functions--some functions will not produce the correct results when there is a bookend. These functions are few and far between, so as long as you are aware of the possibility that problems may appear; and as long as you check your results to ensure there is no problem, you can use a bookend as a shortcut.

Use a dynamic named range to define a range that will grow and shrink according to the amount of data in the range. This is the best solution. See the content on names for details.