**Please Note: **
This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), *this tip may not work for you*. For a version of this tip written specifically for earlier versions of Excel, click here: Nesting IF Worksheet Functions.

If you have done any programming, you know that you can create conditional statements known as If...Then statements. Basically, these statements give you a way to say "If this is true, then do that." Excel provides an IF worksheet function to accomplish essentially the same thing in a formula.

You already know how to use the IF function because it was covered in other *ExcelTips. *What you may not know is that you can nest IF functions. For instance, consider the situation where you want to return a value if two other conditions are true. If cell B5 contains a temperature setting and cell B6 contains a pressure setting, you may want cell B7 to contain the words "TOO HIGH" if B5 is greater than 100 and cell B6 is greater than 50. To do something like this, you could use the following formula:

=IF(B5>100,IF(B6>50,"TOO HIGH",""),"")

Notice that there are two IF functions here. The first one checks to see if the value of B5 is greater than 100. If it is, then the next IF function is invoked. This one checks to see if B6 is greater than 50. If it is, then the words "TOO HIGH" are displayed in the cell. If either of the conditional statements fail, then nothing is displayed in the cell.

*ExcelTips* is your source for cost-effective Microsoft Excel training.
This tip (12611) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: **Nesting IF Worksheet Functions**.

**Excel Smarts for Beginners!** Featuring the friendly and trusted *For Dummies* style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out *Excel 2013 For Dummies* today!

When working with finances, you often need to know the rate of return on a given investment. The most common type of ...

Discover MoreIndirect references can be very helpful in formulas, but getting your head around how they work can sometimes be confusing. ...

Discover MoreIf you need to change the case of letters in a cell, one of the functions you can use is the PROPER function. This tip ...

Discover More**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

Got a version of Excel that uses the
ribbon interface (Excel 2007 or later)?
**This site is for you!** If you
use an earlier version of Excel, visit
our *ExcelTips* site focusing on the menu interface.

**FREE SERVICE:** Get tips like this every week in *ExcelTips,* a free productivity newsletter. Enter your address and click "Subscribe."

Copyright © 2017 Sharon Parq Associates, Inc.

2015-12-08 10:25:30

Michael

When I nest IFs, I use Alt-Enter to embed carriage returns in the formula so it can be indented/outlined. This makes it more readable, especially when there are a higher number of nests.

=IF(B5>100,

IF(B6>50,

"TOO HIGH",

""),

"")

2013-09-26 12:39:27

Bryan

Carlos, it seems I didn't understand the question, but it also seems you don't need an answer.

However, I still stand by the only part of my statement you didn't quote: "You really would be better served getting comfortable with the formula bar. The formula selector is nice if you are a really basic user needing really basic functions, but it is limiting if you want to use even a slight fraction of Excel's power."

I played around with the method described in the article you linked; it's nice, but in the time it takes to make one formula I could write ten by hand. If you decide to go back and edit that formula it would be exponentially easier to type the changes than try and manipulate the formula box (depending, of course, on what kind of changes you are making).

2013-09-25 11:36:43

Carlos Mario

"Carlos, I assume by the Name box you mean the formula selector."

No: I mean the box that is to the left of the Function Wizard.

"You can't use this to nest functions, at least not the way you would want to."

Yes, I can: when you type a function name, the Name box (which normally shows the active cell address) turns into a "Function box" in which you can choose a previously used function or another function (with More Functions option)

"You can only use the formula selector to choose the outer-most function, and then you have to manually type the inner functions."

No: when I need to nest functions, I choose the first function from the wizard and the nested (inner) functions from the Name Box.

I found a very good article that describes very well what I am you talking about. Please see:

http://mauriceausum.wordpress.com/2011/10/29/excel-use-the-function-wizard-wisely/

Sincerelly yours,

Carlos Mario

2013-09-25 07:32:43

Barry Fitzpatrick

Shreepad what you say is correct but you have effectively changed the requirement.

More than a few levels of nesting become very messy. In Shreepad's example I would use the "CHOOSE" function for clarity and ease of use. But I do appreciate that you lose flexibility in the criteria that can be used.

=CHOOSE(A1,"1", "2", "3","4", "5", "6", "7", "8", "9")

values in A1 of less than 1 or more than 10 will result in the "#Value!" result. This can be trapped using an IF function to wrap around the CHOOSE function.

=IF(OR(A1<0,A1>=10),"Try something Else", CHOOSE(A1,"1", "2", "3","4", "5", "6", "7", "8", "9"))

But as ever it all depends on the Users requirement.

2013-09-24 08:31:57

Shreepad S M Gandhi

Barry friend, your alternative is good but true only for the above example. Paste the formula below in any cell other than A1. This is just another example to show how nesting can be put to use.

=IF(A1=0,"Zero",IF(A1=1,"One",IF(A1=2,"Two",IF(A1=3,"Three",IF(A1=4,"Four",IF(A1=5,"Five",IF(A1=6,"Six",IF(A1=7,"Seven",IF(A1=8,"Eight",IF(A1=9,"Nine","Try something else"))))))))))

2013-09-22 07:58:37

Bryan

Carlos, I assume by the Name box you mean the formula selector. You can't use this to nest functions, at least not the way you would want to. You can only use the formula selector to choose the outer-most function, and then you have to manually type the inner functions.

You really would be better served getting comfortable with the formula bar. The formula selector is nice if you are a really basic user needing really basic functions, but it is limiting if you want to use even a slight fraction of Excel's power.

2013-09-21 14:37:53

Carlos Mario

It will be very good if you post an example indicating how to next functions using the Name box, instead of nesting in the Formula Bar.

I think this will be more clear for everyone if you post it in a video.

Thanks,

Carlos

2013-05-04 05:11:23

Barry Fitzpatrick

Another way to achieve the same result is to use the AND function.

=IF(AND(B5>100,B6>50),"TOO HIGH","")

I appreciate the example above is to demonstrate how IF functions can be nested.