Stop using IF/IFS in Excel if you expect your spreadsheet to scale (here’s why it keeps breaking)
Most people are using Excel in a way that guarantees their file will eventually break.
The biggest mistake I keep seeing is people stuffing all their logic into IF/IFS formulas like Excel is a programming language.
It works at first… until the file grows. Then suddenly you have 10+ nested conditions, hidden assumptions everywhere, and one new “job type” breaks half the sheet.
There’s a much more scalable way to do this that almost nobody bothers with.
Instead of writing logic inside formulas, you move the logic OUT of the formula completely.
You create a simple table that defines the rules.
For example, instead of:
“If Job = A and Title = X and Type = Y then calculate rate like this…”
You store everything in a structured table:
Job | Title | Type | Rate | Multiplier | Adjustment
Then your main sheet does something much simpler:
It just pulls the correct row using a lookup (multi-criteria key or FILTER), and calculates:
Net Hours × Rate × Multiplier + Adjustment
That’s it.
No IF chains. No nested logic. No rewriting formulas every time the business changes.
The painful part most people don’t realize is this: every time you hardcode logic into a formula, you’re actually moving business rules into the most fragile place possible.
So when the business changes (and it always does), your spreadsheet becomes technical debt instead of a tool.
Once you switch to a proper lookup-table structure, Excel stops breaking every time something new is added. You don’t “edit formulas” anymore—you just add rows.
It’s a small shift in thinking, but it’s usually the difference between “why is this broken again?” and “this just works even after changes.”