Three Ways to Reduce Errors in Your Excel SUM Formulas
Simple spreadsheet edits can introduce costly errors. Here are three techniques that can help you to reduce such problems.
If you aren't
careful, simple edits to your reports and analyses can cause significant
Years ago, for example, I heard about an expensive error when I was
writing for LOTUS magazine, before Excel was invented.
A contractor was preparing a bid using Lotus 1-2-3. At the last
minute, he realized that he had overlooked a $100,000 expense. So he
inserted a row in his list of costs, entered the expense, recalculated,
and submitted his bid.
Unfortunately, he made the mistake shown above. When he inserted the
new row he forgot to modify the SUM function at the bottom of the
column. Here, the 100 looks like it's included in the total, but
Because the contractor won the bid but lost his shirt, he tried to
sue Lotus. But the judge tossed the case out of court. Lotus couldn't be
responsible for their users' stupid mistakes, the judge ruled.
This article describes three ways to protect yourself against errors
like these. One uses an automatic setting in Excel; another uses a
range-name trick that you've probably never seen before; and one uses
very convenient spreadsheet formatting.
Using Excel's Automatic Solution
have a spreadsheet with this display. The formula in cell B8 is:
You could have arrived at this point by inserting row 7, as shown. Or
you could have entered the numbers and formula shown in the range B4:B8.
When you enter a value cell B7, Excel can automatically modify the
SUM formula to include that new value. To set up Excel to do this,
choose Tools, Options. In the Edit tab, make sure there's a check in the
checkbox titled "Extend data range formats and formulas."
This behavior has many quirks. Here are several:
- The original SUM function
must include at least three cells in its range. That is, if the formula
in cell B8 were =SUM(B5:B6), Excel would not modify the SUM
formula when you enter a value in cell B7.
- This behavior works if the SUM formula is below a column or to the
right of a row of data. It does not work if it's above the column or to
the left of a row.
- Excel modifies the SUM formula if it is within 20 cells of the new
data. In the figure, for example, Excel will update a SUM formula in
cell B27, but not in cell B28.
These aren't all the quirks, but they're enough to suggest that
alternate approaches would be useful.
Using the 'NextUp' Relative Reference
Let's take a
look at this figure again. The original formula in cell B7 is
=SUM(B4:B6). We can change that formula so that the entire problem goes
To see how this works, I need to explain two aspects of Excel range
First, Excel names can use absolute or relative references.
Typically, we use absolute references. That is, we could define MyCell
But we also could define a name using relative references. To
illustrate, assume that cell B5 is the active cell. We could define the
name NextUp as =Sheet1!B4. That is, cell B4 is the next cell above cell
B5, the active cell.
Because NextUp uses a relative reference, the name is defined with
reference to the active cell, and that relative reference applies to any
cell in which the name is used. For example, if NextUp is used in cell
M50 the name would refer to cell M49.
There's one significant problem with this definition of NextUp,
however: It's defined in terms of Sheet1. Therefore if cell D5 of Sheet3
is active, NextUp will reference cell D4 of Sheet 1.
That won't do at all.
To get around this problem, we must change the way that NextUp is
defined. In addition to using a relative cell reference, we also must use a
relative sheet reference. Again, assuming that cell B5 is the active
cell, we define NextUp as:
(Because relative sheet references are uncommon, you should know one
unique characteristic of them. A name using a relative sheet reference
and an absolute cell reference, like...
...would refer to the specified cell in every active worksheet.
Unlike normal cell references, this reference will not change if you
insert rows above cell A3 or columns to its left, or if you cut and
paste the referenced cell to a new location. That is, a name defined as shown
reference cell A3 until you manually change its definition.)
With NextUp properly defined, we can use the name in our SUM
formulas. In the figure, that is, we can change the SUM formula to
In fact, we should go one step further. We should anchor the SUM
formula in the cell with the text above the column of numbers. That way, we could
insert rows for new data above or below the column of numbers and ensure
that the new numbers will be included in our SUM. That is, the SUM
formula in cell B7 should be:
If there's a chance that a date or some other numeric title would
appear in cell B3, you could use the formula:
Here, the N() function returns the numeric value of cell B3. That is,
it returns zero if the cell is a label, otherwise it returns the value.
Honestly, I seldom use this second approach, because I seldom include
dates without a border row, as shown below. However, it's useful to know
that a solution exists if you ever need it.
After you enter one of the two formulas above, it's always a good idea to check that
the NextUp reference is working correctly. To do so, select the cell that contains the
formula; copy the text of the reference (here, "B3:NextUp"); press the
F5 function key to launch the Go To dialog box; paste the reference text
into the Reference edit box; then press OK. After you do so, Excel
should select the expected range.
Using Border Rows
illustrates a simple approach that's extremely useful when your display
doesn't need to look like it was printed in a magazine.
Here, all summary formulas are anchored in the gray cells. For
example, the formula in cell B8 is:
One useful feature of this approach is that it leaves no doubt about
where to insert new data. You know that any data inserted between the
gray borders always will be included in your summary formulas.
If you prefer to use range names in your formulas, rather than cell
addresses, assign the names in row 2 to the areas bounded by the gray
borders. That is, select the range B2:C7, choose Insert, Name, Create;
ensure that Top Row is checked; then choose OK.
To assign the range names to formulas, choose Insert, Name, Apply;
make sure that the names you want to apply are selected; then choose OK.
This approach would change =SUM(B3:B7) to =SUM(Sales).
If the contractor who got into trouble using a Lotus spreadsheet had
used any of these techniques with Excel, he probably would have saved
himself some expense and embarrassment.
Perhaps they'll save you some problems as well.