Nested IF Statements in Excel: Examples, Limits, and Tips

Nested IF statements in Excel put one IF function inside another, letting a formula choose among several outcomes. For example, a score can receive an A, B, C, D, or F by testing score thresholds in sequence; the examples below show how to write that formula and when a different approach may be easier.

What a Nested IF Statement Does

Excel’s IF function checks a condition and returns one value when the condition is true and another when it is false. Its basic pattern is =IF(logical_test, value_if_true, value_if_false). In a nested formula, the value_if_false argument of one IF is another IF. Excel checks that next condition only when the previous one is false, continuing until a condition is true or the formula reaches its final fallback value.

This makes nested IF useful when a cell must be assigned one result from a short list of mutually exclusive categories. The order of the tests matters: if a condition is already true, Excel does not move on to later alternatives. For ranges, test the highest threshold first or use ascending thresholds with the appropriate comparison operators. That prevents a broad condition from capturing a value that should have received a more specific result.

Nested IF formulas are different from several separate IF formulas placed in different cells. A nested formula returns a single result in its own cell, although that result can be text, a number, a date, a reference, or another formula result.

Example: Assign Letter Grades

Suppose a worksheet has test scores in B2:B20 and you want a letter grade in column C. With the score in B2, enter this formula in C2, then fill it down. It checks the score from the highest cutoff to the lowest.

With a numeric score in B2, where 90 and above is A, 80–89 is B, 70–79 is C, 60–69 is D, and below 60 is F, use =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F")))). For B2 equal to 84, the result is B.

The final F is the fallback: it applies only when every earlier comparison is false. The thresholds are inclusive at 90, 80, 70, and 60 because the formula uses >=. If the grading policy uses different boundaries, change the numbers and comparison operators to match it. Decide what to do with blank cells too: as written, a blank B2 is treated like zero and returns F. If blanks should remain blank, add a blank check before the grade tests.

With a score in B2 and a blank result desired when B2 is empty, use =IF(B2="","",IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))). For an empty B2 the result is an empty-looking cell; for a score of 84 the result remains B. The formula tests blankness first so it does not classify a missing score as a failing score.

Example: Set Shipping Charges

Nested IF can also apply a priority rule. Imagine B2 contains an order total and C2 indicates whether the customer is a member. Orders of $100 or more receive free shipping; otherwise, members pay $4.99 and nonmembers pay $8.99. The order-total test comes first because it overrides membership status.

With the order total in B2 and the text Yes or No in C2, use =IF(B2>=100,0,IF(C2="Yes",4.99,8.99)). If B2 is 65 and C2 is Yes, the result is 4.99; if B2 is 120, the result is 0 regardless of C2. Apply a currency number format to the result cells if you want Excel to display the amounts with a dollar sign.

This example also shows why a formula should reflect the actual rule order. Reversing the tests would make the formula return the member charge for a qualifying member even when the order total reached $100. Before writing a nested formula, state which rule takes precedence, especially when more than one condition could be true.

Example: Calculate a Commission Rate

A nested IF can assign a rate based on sales bands. The formula below applies one rate to the entire revenue amount in each band. It is not a progressive calculation in which different portions of revenue receive different rates, so confirm which type of policy is intended before relying on the result.

With total revenue in B2, use 0% below $10,000, 5% from $10,000 to under $25,000, 8% from $25,000 to under $50,000, and 10% at $50,000 or above. Use =IF(B2<10000,0,IF(B2<25000,B2*5%,IF(B2<50000,B2*8%,B2*10%))). If B2 is 30000, the result is 2400 because the whole amount is multiplied by 8%.

The formula tests lower bounds indirectly by eliminating the lower bands first. For instance, once a value is not below 10,000 and is below 25,000, it must be in the second band. That structure is compact, but it makes the boundary logic worth checking carefully. At exactly 25,000 this formula returns 2,000, or 8% of 25,000; at exactly 50,000 it returns 5,000, or 10% of 50,000.

Example: Label an Invoice Status

Nested IF can combine a missing-value check with a status check and a date comparison. For example, leave a row empty if there is no invoice ID, label it Paid if the status says Paid, and otherwise mark it Overdue when the due date has passed. Any remaining invoice is labeled Open.

With the invoice ID in B2, payment status in C2, and due date in D2, use =IF(B2="","",IF(C2="Paid","Paid",IF(D2<TODAY(),"Overdue","Open"))). If B2 contains INV-104, C2 contains Open, and D2 contains October 1, 2026, the result on November 2, 2026 is Overdue. If C2 says Paid, the result is Paid even if the due date is in the past.

TODAY() returns the current date and updates when Excel recalculates the workbook, so the label can change as time passes. If a due date that is today should also count as overdue, change the comparison from < to <=. This formula also assumes that C2 contains the exact text Paid; extra spaces or a different spelling will not match.

How to Build and Check a Nested IF

Writing the rules in plain language first makes the formula easier to construct and review. Use this sequence:

Identify the input cell or cells and the result you want for each possible case.
Write the conditions in the order they should be tested, putting priority exceptions first.
Choose the value to return when each condition is true, then decide the final fallback for when all tests are false.
Start with an IF for the first condition and put the next IF in its false-result position.
Check that every opening parenthesis has a closing parenthesis, and test values at each cutoff as well as values between cutoffs.

Excel’s formula bar can help when a formula becomes difficult to read. Click the formula bar or press F2 to edit the active cell, then use the insertion point to inspect each condition and its corresponding result. On Windows, Ctrl+Z undoes an ordinary cell edit; on Mac, use Command+Z. Test a formula with a few known inputs before filling it down a large table, and keep a copy of the original data if the results will drive a consequential workflow.

When to Use IFS or Another Approach

For several straightforward conditions, IFS may be easier to read because each condition is paired with a result rather than inserting one IF inside another. IFS is available in Excel 2019 and later, including Microsoft 365. It returns the result associated with the first condition that evaluates to TRUE, so order still matters. Unlike a nested IF with a final fallback, IFS can return an error if none of its conditions is true; include a final TRUE condition when you need a default result.

With a score in B2 and the same grade cutoffs used above, use =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F"). For B2 equal to 84, the result is B. IFS can make a long list of alternatives more readable, but it does not automatically make the decision rules simpler or remove the need to test boundaries.

For a small set of exact matches, SWITCH may be a better fit; for complex conditions, a lookup table can be easier to maintain than a formula with many branches. A table lets you store categories and thresholds as worksheet data, which can be useful when the rules change often. The right choice depends on whether the logic is based on ranges, exact values, or multiple conditions, and on which Excel versions need to open the workbook.

Common Errors and Practical Limits

Wrong test order: A broad condition early in the formula can prevent later conditions from being reached. Put priority cases first and check each threshold from both sides.
Missing or extra parentheses: Each nested IF adds another pair. Use Excel’s formula editing view to trace which false result belongs to which condition.
Unexpected blank results: A formula that uses an empty string as a result displays nothing, but the cell still contains a formula. That differs from a truly empty cell in some counting and testing situations.
Text that does not match: A test such as C2=”Yes” expects the specified text. Look for spelling differences, unintended spaces, or values stored in a different format.
Numbers stored as text: A cell that looks numeric may not behave like a number in comparisons or calculations. Check the cell’s underlying value and data format if results seem inconsistent.

Excel supports up to 64 levels of nested functions in current versions, but that is a technical ceiling, not a recommendation. A formula with many branches is harder to audit, edit, and explain. If the rules grow, consider IFS, a lookup table, or splitting the logic into clearly labeled helper columns. Excel for Microsoft 365 on Windows uses the same IF syntax shown here as Excel for Mac and Excel for the web. Function argument separators can differ with regional settings: some installations require semicolons instead of commas, so use the separator accepted by your Excel setup.

Frequently Asked Questions

How many IF statements can I nest in Excel?
Current Excel versions allow up to 64 nested function levels. In practice, a formula with many branches can become difficult to maintain, so consider IFS or a lookup table before approaching that limit.

What happens if none of the nested IF conditions is true?
Excel returns the final value_if_false argument. Make that last result an intentional fallback, such as F, Other, or 0, rather than leaving the final case unclear.

Can I use AND or OR inside a nested IF?
Yes. AND and OR can combine multiple tests inside an IF condition, which is useful when a result depends on more than one fact. Keep the combined condition readable and test cases where only one part is true.

Why does my nested IF return the wrong category?
Check the order of conditions, the comparison operators at the boundaries, and the actual contents of the referenced cells. A value exactly on a cutoff can change category depending on whether the formula uses >, >=, <, or <=.

Is IFS better than nested IF?
IFS is often easier to scan when several conditions are tested in order, but both approaches depend on correct condition order. A lookup table may be clearer when thresholds or categories change regularly.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.