Early in my career I built an Excel model I was genuinely proud of. It handled a dozen edge cases, pulled from multiple sheets, and always gave the right answer. The problem was that nobody else could touch it. When I moved to a different role, someone had to rebuild the whole thing from scratch because my formulas were impossible to follow.
The problem wasn't complexity. The business logic genuinely required nested conditions. The problem was that I wrote formulas the way Excel encourages you to write them: as a single horizontal line.
Here's a real formula I pulled from a production workbook:
=IF(MID([@Blend],6,2)="DB","",IF(MID([@Blend],6,2)="RH",MID([@Blend],3,3)&"PR",IF(OR([@Site]="ALWC",[@Site]="WWC"),[@Var]&"WA",MID([@Blend],3,5))))Now the same formula, formatted with line breaks and indentation:
=IF(
MID([@Blend],6,2)="DB",
"",
IF(
MID([@Blend],6,2)="RH",
MID([@Blend],3,3)&"PR",
IF(
OR([@Site]="ALWC",[@Site]="WWC"),
[@Var]&"WA",
MID([@Blend],3,5)
)
)
)The logic is identical. But you can see the nesting, trace the conditions, and find a bug without squinting at the formula bar.
How to Do It
Excel supports line breaks inside formulas. Press Alt+Enter on Windows or Option+Enter on Mac to start a new line, then use spaces to indent. That's it.
A few conventions that work well:
- One argument per line for each IF or nested function
- Indent each nesting level by 4-5 spaces
- Align closing parentheses with their opening function
This is borrowed directly from how programmers format code. If you've ever looked at a properly indented block of Python or JavaScript, the principle is the same: visual structure should mirror logical structure.
When It Matters
Simple formulas don't need this treatment. A VLOOKUP with three arguments is fine on one line. But once you're nesting IFs, combining AND/OR conditions, or chaining INDEX/MATCH, formatting becomes the difference between a formula that can be maintained and one that can only be replaced.
The extra minute spent formatting pays off the first time someone else needs to edit your workbook. Or the first time you reopen it after three months and can actually follow your own logic.
— Lo