How to Use Nested If Statements in Excel

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Whenever you need to test for more than one condition and take action depending on the outcome, nesting multiple ‘If’ statements is one of the things you can do.

Learning to use nested ‘If’ statements signal a user’s transition from a basic Excel user to an advanced user.

At first, “nesting multiple if statements” may sound a bit complicated. But as you learn more about it, you’ll see how simple it really is.

In this article, we’ll show you how to use nested ‘If’ statements in a way you’ll never have to search about it again. 😊

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get started!

*This tutorial was written in Microsoft Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file

What’s a nested ‘If’ statement?

To put it plainly, a nested ‘If’ statement happens when you use Excel’s ‘If’ function on top of another, multiple times.

That’s why it’s called “nested” because you are nesting or placing the function inside the other.

If you’re not familiar with the ‘If’ function, it’s an Excel function that performs a logical test on a specified value or reference. What it returns is a specified value depending on the result, whether it’s TRUE or FALSE.

Kasper Langmann, Co-founder of Spreadsheeto

Before we discuss how to use a nested ‘If’ statement, it’s important that you know what it’s syntax is since it’s going to be the basis of the statement.

The syntax of the ‘If’ function is as follows:

=IF (logical_test, [value_if_true], [value_if_false])

Parameters:

  • ‘logical_test’ – value, cell reference, or logical expression to be evaluated as TRUE or FALSE
  • ‘value_if_true’ – the value to return if the ‘logical_test’ returns TRUE
  • ‘value_if_false’ – the value to return if the ‘logical_test’ returns FALSE

Basically, you nest the ‘If’ function by adding another ‘If’ function as the value of the ‘value_if_false’ parameter on an existing ‘If’ statement.

The formula then becomes like this:

=IF (logical_test, [value_if_true], IF (logical_test, [value_if_true], [value_if_false]))

If you have more than 2 conditions, just add ‘If’ statements as necessary.

Points to remember when nesting ‘If’ statements

Here are important notes to remember:

  • Check your formula: That includes counting your parentheses. 
  • Cell references: Using cell references minimizes error when using ‘If’ statements. However, you have to check whether you have the right reference for the right parameter.
  • Absolute reference cells: Once you refer to the cells that contain the categories and their max & min value, use absolute references.
  • Left to right: The way Excel reads the statement is from left to right. If you have 3 conditions and the formula returned TRUE on the second one, the formula will not proceed into evaluating the third condition.

Nested ‘If’ statements are extremely sensitive. One missing parenthesis and you’ll get an error.

Kasper Langmann, Co-founder of Spreadsheeto

How to use nested ‘If’ statements

The most common use of nested ‘If’ statements is categorization.

That’s why in this exercise, we’ll be using nested ‘If’ statements to categorize grades into fail, average, good, and excellent.

Here’s our data set as well as the minimum and maximum values of categories:

dataset for nested if statement exercise

As mentioned earlier, Excel reads the statement from left to right. Because of that, we can arrange our conditions either from the highest category or the lowest.

Kasper Langmann, Co-founder of Spreadsheeto

Intuitively, we start with the lowest.

So for the first ‘If’, let’s check if the grade belongs to the ‘Fail’ category.

The best way to do this is by evaluating whether the grade is lower than the maximum value of ‘Fail’.

In English:

Is the grade lower than the ‘Max’ of ‘Fail’? If yes, then the grade belongs to the ‘Fail’ category. If not…

Formula:

=IF(grade<74,’Fail’,…) or

=IF(B3<G5,E5…)

To proceed, all we have to do is start another ‘If’ if the grade doesn’t belong to the ‘Fail’ category.

In English:

…If yes, then the grade belongs to the ‘Fail’ category. If not, is the grade below the ‘Max’ of ‘Average’? If yes, then it belongs to the ‘Average’ category. If not…

Formula:

=IF(grade<74,’Fail’, IF(grade<82,’Average’,..)) or

=IF(B3<G5,E5, IF(B3<G6,E6,…))

The next part is to evaluate whether the grade belongs to the ‘Good’ category.

If not, then the grade is obviously on the last category, ‘Excellent’.

Usually, you don’t have to open another ‘If’ when you’re on the second to the last condition. Why? Since if that condition is false, then the value obviously falls on the last category.

However, there are cases when you need to open another ‘If’. For example, if the value exceeds the max value of the last category, it belongs to a general category like ‘null’ or ‘impossible’.

But for this exercise, there’s no need to add that.

Kasper Langmann, Co-founder of Spreadsheeto

In English:

…If yes, then it belongs to the ‘Average’ category. If not, is the grade below the ‘Max’ of ‘Good’? If yes, then the grade belongs to the ‘Good’ category. If not, it belongs to the last category which is ‘Excellent’.

Formula:

=IF(grade<74,’Fail’,IF(grade<82,’Average’,IF(grade<89,’Good’,’Excellent’))) or

=IF(B3<G5,E5,IF(B3<G6,E6,IF(B3<G7,E7,E8)))

how to use nested if

Let’s breakdown the process above:

  • If 71 is less than 74, then it’s a ‘Fail’ grade
  • If not and it’s less than 82, then it’s an ‘Average’ grade (implying the value is more than 74 but less than 82)
  • If not and it’s less than 89, it’s a ‘Good’ grade (implying the value is more than 82 but less than 89)
  • If not, it’s an ‘Excellent’ grade

To apply the formula to the rest of the grades, all you need to do is drag the cell handle down.

However, you need to apply absolute cell reference first on the parameters that refer to the categories and their max & min values. 

If not, you’ll have the wrong results since Excel will also adjust the cell references in the formula as you copy or drag the cell handle down.

For example, here’s what would happen if you don’t use absolute reference:

what happens if you use relative reference in nested if

To remedy that, apply absolute reference on the appropriate cells by pressing ‘F4’ on your keyboard.

using absolute reference in nested if

After you drag the cell handle down, you’ll immediately see the results:

results for nested if exercise

Problems with nesting ‘If’ statements

After you write your first nested ‘If’ statement, you’ll realize how cumbersome and tricky the process is.

The disadvantages of using nested ‘If’ statements include:

  • Prone to error: Nested ‘If’ statements may be easy to understand and use but they’re difficult to write. One tiny mistake and you’ll get an error.
  • Error-checking is time-consuming: Depending on how many variables you have, then back-tracking for an error would take time.
  • Unscalable: Every time you add another category, you have to manually add it on the formula. Imagine if you have more than 20 categories!

Though Excel simplified nesting ‘If’ statements with the new ‘Ifs’ function, the same disadvantages exist.

If you like better approaches, try using the ‘MATCH’ and ‘VLOOKUP’ functions.

Wrapping things up…

Nesting ‘If’ statements is easy. However, you need to do it carefully.

If you’re having difficulty, try using our free exercise file and write the formulas mentioned above. Then, simply reverse engineer the process and you’ll surely get it. 😊

Kasper Langmann, Co-founder of Spreadsheeto