IF Statements in Excel

Spreadsheets are one of the best and simplest ways to analyze data. Often, this will include determining whether values meet certain conditions. One of the best ways to do that is with an IF statement. Let’s learn how to use IF statements in Microsoft Excel.

How to Use IF Statements in Microsoft Excel

IF statements work with all kinds of data. In essence, they’re a formula-driven logic test that Excel performs. IF statements are used when you need to establish whether a given data point meets a certain specified condition.

For example, suppose that you’re a sales manager with several employees working for you. If they make a sale over $5,000, each salesperson qualifies for a bonus. You want to quickly determine which sales meet the +$5000 condition.

Of course, you could manually review the data and physically determine which sales are above $5,000. For small datasets, that’s a perfectly acceptable option. But imagine if you have a list of hundreds or thousands of sales. Manual analysis would quickly become difficult and tedious. Fortunately, with IF statements in Excel, there’s no reason to perform the analysis manually.

To get started, set up a column in Excel adjacent to your data. You can use this to place IF statements in, and return the results of your analysis. For this example, click into cell C2 to select it.

Now, it’s time to begin constructing your very first IF statement. Once you’ve finished, you’ll be able to quickly copy the formula downward. Excel will automatically adjust the values, so you only have to input one formula for each set of data.

Get started inside cell C2 by typing an = sign. The = sign tells Excel that you’re inserting a formula into the given cell. This is standard practice for any Excel formula.

Then, type IF(. This inserts the IF function into your formula.

Excel now asks for multiple inputs and conditions. Let’s work through them in order to build the IF statement.

The first input is the Logical Test. This, in essence, is your decision point, or the factor that you want to perform an analysis around. Remember, in this example, you want to find values that are greater than $5,000.

With that in mind, click into the uppermost of the cells containing a price. In this example, that’s cell B2. Then, type >, the greater-than sign, followed by 5000. So far, your IF formula reads:

=IF(B2>5000

if-statement-in-excel

Type a comma to conclude this portion of the formula. Now, Excel asks for two conditions:

• Value_If_True
• Value_If_False

Again, these inputs will vary based on the nature of your IF statement. The Value_If_True is asking the following: if a sale is above $5,000, what is your desired outcome? In this case, the outcome is paying a bonus, and you want to display this in column C where you’re constructing your IF statements.

This pair of values can be fully customized. In this example, a simple “Yes” or “No” response easily captures the spirit of the question. If a sale is above $5,000, yes, a bonus will be paid.

Continue with your formula by typing “Yes”. Always ensure that you include the quotation marks, or your IF statement formula won’t work correctly.

Now, you’ve satisfied the Value_If_True condition. To complete the formula, you’ll also need to add the Value_If_False. This, in a way, is much simpler. It’s simply the opposite condition. The opposite of “Yes” is, of course, “No.”

Continue your formula by adding another comma, and then typing “No”)

Your complete IF statement formula now reads:

=IF(B2>5000,”Yes”,”No”)

Hit Enter on your keyboard, and Excel will instantly perform a quick analysis. If the price in cell B2 is over $5,000, you’ll see the word Yes appear in your formula. Conversely, if it’s below $5,000, you’ll see the word No. In this example, Excel returns No.

From here, you can click and drag on the corner of cell C2 to copy the formula down, spanning the other transactions in your list. Excel will automatically update the formula, so that it will analyze each individual price and perform the logical test.

IF statements also work with additional conditions. Let’s say, for example, you want to know which sales were above $5,000, but less than $7,500. It requires a modified formula, but this is still an easy calculation to perform with a Microsoft Excel IF statement. All you need to do is incorporate Excel’s AND function.

To perform the above analysis, find an empty cell and begin by inserting an IF statement:

=IF(

Now, you’ll need to continue by adding AND(, which is the Excel AND function:

=IF(AND(

This tells Excel to perform the IF analysis with multiple conditions. From here, you’re inputting two logical tests – the two conditions. Continue by typing B2>5000,B2<7500). Your formula is now:

=IF(AND(B2>5000,B2<7500)

Complete the formula by adding the Value_If_True and Value_If False:

=IF(AND(B2>5000,B2<7500),”In Range”,”Not in Range”)

Again, the values can be customized.

Press Enter on your keyboard again, and Excel will run the multi-step IF/AND analysis to determine which sales meet the specified conditions.

As you can see, by using IF statements, you can streamline your data analysis work in Microsoft Excel. These instantaneously filter data through one or more logical tests. They help you quickly derive meaning from any data point. And thanks to the ability to copy formulas downward, you can run analysis steps on thousands of data points in a flash.