Improving your Spreadsheets

A simple way to improve your spreadsheets:

By including a blank row/column in SUM()s (or any Range, e.g. E3:E6) you can avoid one of the most common errors in spreadsheets.

GrabShot_1a

This spreadsheet has a “Guard Row” and a “Guard Column” (shown in grey), both are included in the SUM()s shown. You can insert rows or columns at the bottom or to the right and the SUM()s will adjust to include the additional columns (see below).

GrabShot_2a

Without the Guard Row/Column, if you add additional rows and columns the SUM()s won’t include them and the new data is missing from the totals.

GrabShot_3a

Excel sometimes adjusts the SUM()s automatically when data is entered from the keyboard, but not if it is pasted in.