Excel IF Formulas – An Introduction

Understanding Excel’s IF() function is essential to using Excel for anything more than the most rudimentary spreadsheets.

This article demonstrates how to use the IF function to create smart Excel Formulas that can handle real-world requirements.

You can download the example discussed in the article here.

The expense of guests

Our example revolves around the weekly expense report of one of the sales representatives working at the (fictional) XYZ Widget Company:

The XYZ accounting department will reimburse 50$ for any day a salesperson had a visitor. This means that for some of the days of the week in question, Ryan should be reimbursed, and for some days he shouldn’t.

IF to the rescue

As we don’t want to check each day’s record by hand, we’ll use the IF function to test the ‘Entertaining Guest’ column.

We do this by:

• Selecting cell C4
• Typing in =IF(B4=”yes”,50,0)
• Pressing Enter

Now, cell C4 will contain 50$, as a guest was entertained on the 22^nd of November:

What just happened?

Before filling in the rest of the column, let’s break down the formula to understand how it works.

The IF formula has three parts: the logical expression, the TRUE output, and the FALSE output; each part is separated from the next by a comma. Excel evaluates the logical expression: if it’s true, the if function will output the TRUE output expression; if it’s false, the if function will output the FALSE output expression.

In our case, the logical expression is B4=”yes”. In the sample, cell B4 does indeed contain the string “Yes” (for the purists among us, we’ll note that the equal sign in Excel will evaluate strings with difference capitalization or case anywhere as the same string, thus “Yes” is equal to “yes”; for an exact comparison, use the EXACT() function).

The TRUE output expression in our sample is 50, so if the logical expression evaluates to true (as happens in row 4), the output of the IF function is 50 (as is seen in the sample). This represents the 50$ which will be reimbursed when the salesperson entertains a visitor.

The FALSE output expression is 0, which is the amount of money reimbursed when there’s no visitor.

To sum up our whole IF expression, it amounts to “IF there was a guest – Yes in column B – Output 50, otherwise, output 0”.

Expanding to the whole

Now we need to use the same formula for the rest of the week. To do that, we’ll use the Cell fill handle to copy the formula to the other cells in the column. This is done in to following manner:

• We’ll grab the cell’s handle using the mouse
• Then, we’ll drag the rectangle to cover cells C4 to C10
• And finally, we’ll let go of the mouse button

Excel will copy the IF function from cell C4 to cells C5 through C10, changing B4 to B5 through B10 in the process:

The result is: Wherever there’s a “Yes” in column B, column C will contain 50$, and where there’s “No”, it will contain 0: Just what we wanted.

Summary

The IF function is THE most important component of Excel formulas, It has the power to transform your Excel sheets from simple calculators to smart tools that react intelligently to data you enter into them.