IF and Nested IF Statements in Excel


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

An IF statement might sound like a complicated thing that programmers do, but it’s actually fairly easy to do in Excel. An IF statement basically puts something into a cell depending on certain conditions – if A is true,  put X in this cell. You can even use what’s called a nested IF statement if you have multiple conditions that you want to meet.  

To start with a simple example, lets say we have a spreadsheet where we enter our expenses, but any expense over $100 needs to have an authorization number, so we want a note next to those expenses to remind us that we need to enter that number.  

The Original IF statement 

The basic format for an if statement is: 

=IF(condition-to-be-met,what-to-insert-if-true,what-to-insert-if-false) 

If that looks complicated, don’t worry, as Excel has a handy tool to fill all this in for you.  

To create an IF statement, click in the cell where you want the result of the statement to appear (in our case, in the first cell next to the amount column), and click the Insert Function button. 

if and nested if statements in excel 33243 - IF and Nested IF Statements in Excel

Of course, you can just type =IF and Excel will offer prompts and reminders 

if and nested if statements in excel 33245 - IF and Nested IF Statements in Excel

But the Insert Function dialog helps you select the right function and shows a full dialog box to type in the right parameters. 

if and nested if statements in excel 33246 - IF and Nested IF Statements in Excel

The logical test is the condition that we want to have met. We are interested in if the amount in cell B2 is greater than 100, so we write: 

B2>100 

if and nested if statements in excel 33248 - IF and Nested IF Statements in Excel

The full function arguments dialog is great for anyone new to a function or trying to fix a problem. 

  • The current result for a parameter appears to the right. 
  • The text explains what goes in the currently selected parameter. 
  • Click on the up arrow to briefly shrink the dialog and let you see or choose values from the worksheet. 
  • The current result from the function appears bottom left. 

Now we need to specify what happens if the contents of cell B2 meet this condition. If we want text in the cell, we need to put quote marks around the text. So in the Value if True field, we type: 

“Authorization required” 

if and nested if statements in excel 33250 - IF and Nested IF Statements in Excel

Now, we don’t want anything to appear in the cell next to amounts less than $100, but if we leave the Value if False field blank, Excel will enter the word FALSE in any of those cells. To have those fields remain blank, we need to enter quotation marks with nothing in between them in that field. 

if and nested if statements in excel 33251 - IF and Nested IF Statements in Excel

Click OK and we will see our function in the formula bar.  

if and nested if statements in excel 33253 - IF and Nested IF Statements in Excel

This function is still only applying to cell C2 though, so, just with any other formula we enter, we need to grab the bottom right corner of the cell and drag down to apply the formula to the whole column. 

if and nested if statements in excel 33254 - IF and Nested IF Statements in Excel

Now, as we fill our spreadsheet with expenses, anytime we enter an amount that is more than $100, the message “Authorization required” will appear next to it to remind us that we need to enter the authorization number, and make it easy for anyone auditing the expenses to check that authorization has been entered for any relevant expenses. 

if and nested if statements in excel 33255 - IF and Nested IF Statements in Excel

Nested IF statements for multiple conditions 

If you have more complex conditions to meet, you can use nested IF statements. It’s not immediately obvious how to do this using the wizard we used above, but it can be done! 

Let’s say we want to enter our students’ grades into a spreadsheet, and we have a grading system structured as follows: 

0-49     = FAIL
50-69   = PASS
70-79   = CREDIT
80-89   = DISTINCTION
90-100 = HIGH DISTINCTION 

We will start out using our highest grade. If 90-100 scores receive a High Distinction, then we need to specify what happens for a score over 89. We start out as if this is our only condition. As above, we click the Insert Function button and select IF to open the Function Arguments dialog. 

if and nested if statements in excel 33257 - IF and Nested IF Statements in Excel

 Now write our first condition (greater than 89) and the result if that is true (“HIGH DISTINCTION”), but don’t put anything in the Value if False field yet. 

if and nested if statements in excel 33259 - IF and Nested IF Statements in Excel

Now we click in the Value if False field, but instead of typing anything here, we then click on the Name box, which should still say IF.  

if and nested if statements in excel 33260 - IF and Nested IF Statements in Excel

This will open a new blank Function Arguments dialog, where we enter the next condition and result – any score over 79 should receive a Distinction. 

if and nested if statements in excel 33262 - IF and Nested IF Statements in Excel

Then repeat the process for the Credit result. 

if and nested if statements in excel 33263 - IF and Nested IF Statements in Excel

Then we do again with the Pass condition, but this time we don’t click back into the Name box. We only have one result left (Fail), and this is what happens when none of the above conditions are true, so we can put that last result into the Value if False field. If the student’s score is not over 49, they receive a fail. 

if and nested if statements in excel 33265 - IF and Nested IF Statements in Excel

As you are entering all this information, keep an eye on the formula bar to get an understanding of the structure that is being created. Each time you enter a new condition, a new set of parentheses is opened within the previous set. None of the parentheses are closed until you put in the final condition. If you decide at some time to have a go at writing your own nested IF statement by hand and receive an error – check the parentheses – there should be the same number of closing parentheses at the end as there are opening parentheses in the formula.  

if and nested if statements in excel 33266 - IF and Nested IF Statements in Excel

Once we have entered our final condition, including the Value if False, Click OK, and our selected cell should now contain a grade to match the score next to it.  

if and nested if statements in excel 33268 - IF and Nested IF Statements in Excel

Again, we grab the bottom right-hand corner and drag down to fill the rest of the column with the appropriate grades. 

if and nested if statements in excel 33270 - IF and Nested IF Statements in Excel

The whole formula is copied down with the relative lookup cell (C2) changing. 

IF with other functions 

These are just two simple examples of uses of the IF function in Excel. There is a wealth of other things you can do with it, including combining it with other functions, such the SUM, AVERAGE, MIN or MAX functions 

IFS 

A recent addition, only in Excel 365 and Excel 2019. It’s an alternative for nested IF statements. 

IFS function has matching pairs of conditions and return values. It returns a value that for the first TRUE condition.  That means the order of the conditions is important. 

=IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)  

Here’s IFS using the same example as above. It’s a lot easier to read and fix.

if and nested if statements in excel 33271 - IF and Nested IF Statements in Excel

IF Function alternatives 

There are other IF-like functions worth considering. 

SWITCH 

Another Office 365/2019 function. 

It starts with a function then matching pairs of values to test and return value. 

SWITCH(Value to switch, Value to match1…[2-126], Value to return if there’s a match1…[2-126], Value to return if there’s no match) 

CHOOSE 

long standing Excel function. 

It starts with an index value (1 to 254) then a series of values.  Choose() returns the nth value from the list. 

CHOOSE(index_num, value1, [value2], …) 

 IFError and IFNA 

Two really useful functions for catching errors and problems in cells then returning a nicer message of value than Excel’s # cell errors. 

IFERROR(checks a cell for one of these # results; #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!. 

=IFERROR (C2“ Value out of accepted range”) 

IFNA(is similar but only catches #N/A errors, not the others accepted by IFERROR(). 

subs profile e1563205311409 - IF and Nested IF Statements in Excel
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address