ย 

This super basic Excel trick has saved me from so much pain.

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

Recent Posts

See All

Ever googled directions to the airport? How many routes do you get? Two? Three? Is one the clear choice? Usually. But โ€” do they all get you there within 5 minutes? โ€”โ€”โ€” Try to keep this in mind when bu

=SUM(range)/2 This formula could have saved me from a $1,000,000 mistake... Ever have multiple sums & subtotals in your model? Like this: cost 1 cost 2 cost 3 ------------ subtotal 1 cost 4 cost 5 cos

ย