Close
Time left to register for Excel Made Easy class

Always Include Extra Rows in Ranges

Extra rows in your ranges are important to prevent extra work and bad calculations.

When you insert rows to a spreadsheet, Excel can sometimes behave in an unexpected way.

To make things worse, Excel doesn’t make it obvious that it’s doing something weird.

If you aren’t paying close attention, you could easily end up with bad data without knowing it.

This is because of how Excel handles ranges. A range is the term for the cells that a formula looks at.

The problem with adding rows to the end of a range

Instead of trying to explain, it will be much clearer to use an example.

Let’s say you’re adding up the sales of your worst employee, Chad.

Figure 1 - Simple sum without extra row in the range

So far so good. You should probably fire Chad and stock some better items, but at least Excel’s working fine.

Where it says =SUM(B3:B6), the range is B3:B6. Or cells B3, B4, B5, and B6.

Then Chad says, “Oh, I forgot. I also sold a Stealth Bomber for two billion dollars.

Stupid Chad. Even when he succeeds he screws up.

You update your spreadsheet to insert some rows, and check Chad’s new total.

Figure 2 - Total hasn't increased even though it should have

Wait. How is the total still $0.35 after we added two billion dollars?

Let’s take a closer look at the formula.

Figure 3 - The range in the formula didn't update when we added extra rows.

There’s the problem. When you inserted the rows, the =SUM() formula didn’t update.

If you have a cell that’s adding up the numbers above it, you would intuitively think that adding new rows means it will add up the additional numbers above it.

But that’s thinking like a human and not like Excel.

Excel doesn’t care where the formula is on the spreadsheet. The word “above” has no meaning to Excel.

Excel only cares about the range – the cells listed in the parentheses of the formula.

Let’s go back and look at when you added the rows. You highlighted rows 7-9, and either pressed the Insert button on the Home ribbon, or right-clicked inside the selected rows and selected Insert from the context menu that popped up.

Figure 4 - Inserting rows

But notice that the three rows highlighted – 7 through 9 – are outside of the formula range of B3:B6.

Since the selected rows aren’t in the formula’s range, Excel doesn’t include the new rows in the formula’s range either. Even though those new rows are above the cell that’s summing up the numbers, where a human would think they should be included.

Wonky Cell Borders

The same problem happens with cell borders.

Let’s add a double-line at the bottom of Chad’s sales.

Figure 5 - Borders not updating part 1

Then add extra rows.

Figure 6 - Borders not moving when adding rows

The sensible thing would be for that double-line to be right above the total. But instead, it stayed below row 6.

In some ways, that’s a blessing. It’s much easier to notice, so it may alert you to the problem with the formula.

Usually when you encounter the problem with formulas not updating the way you’d expect, it’s very hard to spot. In the real world you won’t have a difference as obvious as 35 cents and two billion dollars. So you likely won’t notice it and end up with bad calculations.

Borders showing up in the wrong place are at least visually obvious. When you see something like that, it’s a red flag that you should double-check any nearby formulas.

Borders showing up in the wrong place are a red flag that you should double-check any nearby formulas.

While borders in the wrong place are a useful warning, they’re also an annoying hassle to fix.

Fortunately, there’s a very easy way to avoid both problems entirely.

Include Extra Rows in Ranges

Always include extra rows in ranges.

Instead of doing this:

Figure 7 - Example without extra rows in range

Do this:

Figure 8 - Extra row in range

Now there’s a blank row between the last line of data and the cell that’s totaling them up. The range in the formula includes that blank row.

That’s all you have to do. Just include an extra row between your data and the cell that’s totaling them. (Or using any other formula.) It completely solves the problem.

Plus, it looks cleaner.

Insert any rows from the last row of data. That way, you’ll be inserting them inside your range, and the formula will automatically adjust.

If I highlight rows 7-9 and insert:

Extra rows in ranges figure 9 - inserting rows with an extra row

Then add that two billion dollar sale, everything is automatically updated and correct.

Extra rows to ranges Figure 10 - Now it works right

Conclusion

So there you have it. Simply including an extra row between the bottom of your data and your formula will save you a lot of hassle, and prevent miscalculations.

Please let me know if you found this helpful.

If you have any follow-up questions, comment below, or use the question submission form.

Leave a Reply