How to indent nested IF formulas in Excel and stop guessing

On this page
- Put one condition on each line
- Make the layers easier to see
- Use indentation to catch order mistakes
- Where indentation stops helping
- Action steps
- Frequently asked questions
- Does indenting a nested IF change the result?
- What is the shortcut for a line break in the Excel formula bar?
- Can I indent formulas the same way in Google Sheets?
- How many IF functions can I nest in Excel?
Learning how to indent nested IF formulas in Excel pays off every time you reopen a long formula. A nested IF crammed onto one line hides its logic behind a wall of commas and brackets. Break it into lines, indent each level, and the conditions line up like an outline. You can see which test fires first, which result belongs to it, and where the formula ends.
Excel calculates the same result with or without indentation, but you will read it very differently. The method below uses only the formula bar, so you need no add-ins. The examples use the grade formula from Microsoft's support page on nested IF formulas, which gives you a known answer to check against.
Put one condition on each line
The fix is simple: give each IF its own line and push it right by two spaces per level. Click into the formula bar, place your cursor right after a comma that follows a condition, and press ALT+ENTER. Microsoft's documentation gives that shortcut for wrapping long formulas. Repeat for each nested IF, then add spaces at the start of each new line.
Here is Microsoft's grade formula, =IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F")))), after indenting. Each line holds one test and its result. The last line closes every bracket at once, so you can count them against the levels above it.
| Line | Formula bar text |
|---|---|
| 1 | =IF(D2>89,"A", |
| 2 | IF(D2>79,"B", |
| 3 | IF(D2>69,"C", |
| 4 | IF(D2>59,"D","F") |
| 5 | ))) |
Excel ignores spaces and line breaks outside quotation marks, so the result matches the one-line version. Inside quotation marks the rule flips. A space in "A " becomes part of the text, so never indent inside a quoted result. Check that mistake first when an indented formula returns something odd.

Make the layers easier to see
Excel helps with colour while you edit. Microsoft's tip says that as you move the cursor past a closing bracket, its matching opening bracket turns the same colour. Combine that with indentation and bracket counting gets easy. If your last line holds three brackets, you should see three indented levels above it.
Spacing also reads better in a fixed-width font. Microsoft's Tech Community post on the formula bar's new look covers the addition of monospaced formulas, where every character takes the same width. That means two spaces of indent line up from one row to the next. Ragged indentation is easier to spot, and so are stray characters.
Ben Collins describes the onion framework for complex formulas. You peel back the layers until you reach the innermost function, splitting the formula over lines and indenting as you go. Once you know how to indent nested IF formulas in Excel, the same habit works for =IF(AND(B2>=100,C2="Yes"),"Bonus","None"). The IF is the outer layer and AND is the second. When an AND or OR holds three or more tests, give each test its own line so you can switch one off while you debug.
Use indentation to catch order mistakes
Indented thresholds should move in one direction down the page. If they don't, you have found a bug. Microsoft's commission example shows why. When the revenue tests run from $5,000 up to $15,000, a value of $12,500 returns 10%. It passes the first test, so Excel stops there and never reaches the better rates.
The safe pattern lists the highest threshold first: =IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0))))). Indent it and the numbers read 15000, 12500, 10000, 7500, 5000 in a clean falling column. A number out of sequence jumps out immediately.
Boundaries need a second look too. With D2>89, a score of exactly 89 falls through to the B test, and a score of 90 earns the A. Whether the cutoff should be "greater than" or "greater than or equal to" depends on the rule you are modelling, so confirm it with whoever owns that rule. Then test values on each side of every threshold.
This also helps when you inherit a workbook. Click into an unfamiliar formula, add line breaks from the outside in, and read it top to bottom before you change anything. You will usually spot the repeated test, the dead branch or the missing final result quickly.
Where indentation stops helping
Knowing how to indent nested IF formulas in Excel solves readability, not maintenance. Microsoft notes that Excel allows up to 64 nested IF functions but calls it inadvisable. A formula can work most of the time and still return wrong results on some inputs, and those are hard to catch. Thresholds typed inside the formula also mean every rate change is a formula edit. Moving them into cells fixes that, as the post on changing one cell to update all formulas explains.
Two replacements are worth trying. Microsoft shows =IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F"), which removes the extra IF layers and brackets. The other is a lookup table with an approximate match, such as =VLOOKUP(D2,$F$2:$G$6,2,TRUE), where the table sorts from smallest to largest. For other layouts, INDEX and MATCH can do the same job. Exceljet also has short videos on making a nested IF formula easier to read and replacing nested IFs with VLOOKUP.
The manual method has a cost. Every edit means re-adding line breaks and indents by hand, and you repeat the work for each formula in a shared workbook. A multi-line editor with syntax highlighting, like the one in Formula Foundry, applies the layout while you type. If you would rather assemble a formula from parts, the Excel formula builder is another route.
One naming trap is worth a line. Excel's Subtotal command also talks about nesting, but that is a different feature. Microsoft's guide to inserting nested subtotals covers it, and it has nothing to do with IF formulas.
Action steps
- Pick a long formula: Choose one nested IF in your workbook that you find hard to read, and click into the formula bar.
- Add line breaks: Place the cursor after the comma that follows each condition and press ALT+ENTER.
- Indent each level: Add two spaces at the start of every new line for each level of nesting, and keep spaces out of quoted text.
- Check order and brackets: Confirm the thresholds run in one direction and that the closing line holds as many brackets as there are levels.
- Test the edges: Try values just below, at and just above each threshold, then decide whether IFS or a lookup table would be easier to maintain.
Frequently asked questions
Does indenting a nested IF change the result?
No. Excel ignores spaces and line breaks outside quotation marks. Spaces inside quoted text do become part of the result, so keep indentation outside the quotes.
What is the shortcut for a line break in the Excel formula bar?
Press ALT+ENTER before the text you want to move to a new line, as Microsoft's documentation describes. Then add spaces to indent that line.
Can I indent formulas the same way in Google Sheets?
Google Sheets supports multi-line formulas too, but the key combination for a line break differs from Excel's ALT+ENTER. Check the keyboard shortcuts list in Sheets for your system.
How many IF functions can I nest in Excel?
Microsoft says Excel allows up to 64 nested IF functions but advises against going that far. Past a few levels, IFS or a lookup table is easier to maintain.