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.
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.
Wait. How is the total still $0.35 after we added two billion dollars?
Let’s take a closer look at the formula.
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.
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.
Then add extra 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.
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:
Do this:
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:
Then add that two billion dollar sale, everything is automatically updated and correct.
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.