**How to Use** **Nested If Statements**** in Excel**

**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. 😊

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.*

**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 teston a specified value or reference. What it returns is aspecified valuedepending on the result, whether it’sTRUEorFALSE.

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.

**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:

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.

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.

**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****)))**

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:

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

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

**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. 😊