This super basic Excel trick has saved me from so much pain.
Take a look at the picture below ↓
I'm adding up 4 numbers.
However, BEFORE I sum them up...
I insert a 𝐁𝐋𝐀𝐍𝐊 𝐑𝐎𝐖.
𝘛𝘏𝘌𝘕, I put in my =SUM() formula.
Why?
Because my SUM formula ALREADY CAPTURES the blank row.
So, anytime I add more rows (and I will)...
My SUM formula "comes with me" and automatically grabs the new data.
**********************
When we add new data to a list,
We have a natural tendency to add it to the bottom.
So if you don't have this blank "placeholder row" and add new data (to the bottom),
You 𝘈𝘓𝘚𝘖 need to update your SUM formula.
In other words: ONE change to your model requires TWO updates.
Easy enough to remember for my lame example, sure.
But, a more complicated model?
Forget it — you're setting yourself up for error.
**********************
I'll even do this when I only have one line item, for example:
Total Revenue
[~blank row~]
-------------------------
Total Revenue
Why duplicate?
At some point I promise you, you will have more rows.
(It will become Revenue Category 1, 2, 3, etc..., then Total Revenue)
And when you add these rows...
...your Total Revenue Subtotal will already be linked to the rest of your model.
If you only have the one "Total Revenue" line, it's easy to forget:
▪️ "wait which one links to Gross Profit?"
▪️ "am I calculating Net Margin off this Revenue or that one?"
▪️ "which one pulls to my summary tab?"
That's thinking you don't need to be doing when you're just trying to add a line.
**********************
For the advanced users, yes, I know about using OFFSET here.
But — how many people understand the OFFSET function?
I've been doing Excel for ~15 years and that function still confuses me.
The SUM formula?
Pretty sure it's the first thing they teach in Excel 101 after "this is a spreadsheet."
So, IMO there's no need to overcomplicate something with a more "Excely" approach.
**********************
This "trick" might seem obvious, but let me tell you...
I see the SUM formula missing data 𝐀𝐋𝐋 𝐓𝐇𝐄 𝐓𝐈𝐌𝐄 b/c of this exact issue.
Imagine a budget built by customer...
We add one customer at the last minute, forget to update our SUM formula.
Whoops! None of that gets carried through our file.
How about missing a row in your cash forecast?
Nothing like an unexpected Revolver draw b/c your model missed a payment.
**********************
So, next time you're building a schedule...
Think about adding that extra row.
It saves me from pain all the time (learned the hard way, I promise you).
And I hope it helps you too.
—Chris