When a Formula Breaks (It Will) and How to Fix It
Okay. Last lesson we got lookups pulling prices for us instead of retyping them a hundred times. Handy, right up until the day it isn't. And that day is coming.
I'm not being dramatic. Formulas break. Yours will break. Mine still break, and I've been doing this three years. So this lesson isn't about avoiding it, it's about not panicking when it happens.
What a broken formula actually looks like
Usually one of these:
- #N/A — Sheets is telling you "I looked for this and couldn't find it."
- #REF! — something the formula was pointing to got deleted or moved.
- #DIV/0! — you're dividing by zero somewhere, which math does not allow, sorry.
- A number that's just... wrong. No error, no red flag, just quietly incorrect. This one's the scary one.
That last one is the one that gets people. An error at least waves its hand and says "hey, something's off." A confidently wrong number just sits there looking normal.
The three things to check first
Before you start Googling or panicking, check these in order:
1. Did a column move? If you inserted a column, deleted one, or moved your product list around, any lookup formula pointing at a specific column number is now pointing at the wrong thing. This is probably 60% of my broken formulas, honestly.
2. Is there a typo in the thing you're looking up? Spelling has to match exactly. "T-Shirt" and "Tshirt" are not the same to a formula. Neither are "Widget " with a trailing space and "Widget" without one. Sheets doesn't know you meant the same product. It just knows they don't match.
3. Is the cell actually empty, or does it look empty? Sometimes there's a stray space in there. Looks blank, isn't blank. Formulas notice the difference even when your eyeballs don't.
An actual story about this
Okay so, back when I was rebuilding the tracker at the warehouse, one of my first versions had a formula that overcounted a whole product line by about 300 units. Three hundred. My boss caught it before it went anywhere, thank goodness, but that afternoon was not fun. I had genuinely convinced myself the numbers were fine because the formula gave me an answer and it looked official.
That's the thing nobody tells you about spreadsheets. They will hand you a confident, tidy, wrong answer and never once apologize for it. A formula doesn't know if it's right. It just knows if it ran.
So here's my opinion, and I say this one a lot: check your numbers against reality. Count the actual boxes. Look at the actual bank total. If your sheet says you have 40 units of something and you can walk over and count them, do it once in a while. Especially right after you build or change a formula. Trust the sheet, but verify it, because the sheet has no idea if it's lying to you.
How to actually fix it once you've found the problem
- Click the cell with the error. Look at the formula bar up top, don't just look at the cell.
- Read the formula out loud, slowly, like you're explaining it to someone. This sounds silly. It works.
- Check each range it's referencing, one at a time. Click into the formula and Sheets will actually highlight the ranges in color for you, which is one of the more genuinely useful things it does.
- Fix the one broken piece. Don't rewrite the whole formula from scratch out of frustration. Usually it's one column reference or one typo, not the whole structure.
- Copy the fixed formula down to the other rows if it was a "this exact mistake in every row" situation.
And if you truly cannot find it, that's fine. Delete the formula and rebuild it fresh in a blank cell nearby. Sometimes that's genuinely faster than hunting for the one broken piece, and there's no prize for finding it the hard way.
A quick aside about teaching this live
The first time I demoed conditional formatting in front of a room, I typed the rule backwards and the entire sheet turned red. Just, all of it. I laughed and said "sooo that's what NOT to do" and fixed it right there in front of everyone. That mess-up got a bigger laugh than anything I'd actually planned.
I bring that up because watching me fix something broken is honestly more useful than watching something that never breaks. You need to see what the fix looks like, not just the shiny finished version. So when your formula breaks at home, don't hide it from yourself, don't undo it and pretend it didn't happen. Sit with the broken version for a second and actually look at it.
Before next time
Break one of your own formulas on purpose. Rename a column, or delete a row your lookup depends on, and watch what error shows up. Getting comfortable seeing the error is half the battle here. ❤️