A guide to Excel IF statements (formulas, uses and tips)

By Indeed Editorial Team

Published 21 April 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Excel is a powerful and widely available spreadsheet software that businesses from most industries use to analyse and organise their data. A popular spreadsheet function is the IF statement, which you can use to compare values in cells. Reviewing the formula and common uses of the IF statement can help you learn to improve your IT skills and become a more proficient Excel user. In this article, we discuss Excel IF statements, list common variations of the IF formula and explore popular uses of this function.

What are Excel IF statements?

Excel IF statements are useful functions you can use to make logical comparisons between values in cells and what you expect them to be. The function allows you to test a specific condition and assign a TRUE result to one value and a FALSE result to another value. It's a highly universal spreadsheet function which means you can apply it to many conditions and situations, depending on the conditions and requirements you set.

For example, if you're creating a sales report, you may use it to determine which sales representative has exceeded their monthly sales target of £10,000. If they sold products worth more than £10,000, then Excel would assign a TRUE result to them and change their background to green. Those who failed to reach the sales target would get a FALSE result and their cell would turn red.

Related: How to highlight Excel skills on your CV

IF statement formula

The Excel IF function is a multipurpose tool. Regardless of the values you assign using this function, the statement uses these three arguments to assign TRUE and FALSE results:

  • Logical_test: This is a necessary element to include in an IF statement. Thanks to this condition, the software can test, evaluate and assign TRUE or FALSE results.

  • Value_if_true: This is the value that Excel assigns to an element if the logical_test comes back positive or as TRUE. It's an optional element.

  • Value_if_false: This is the value that Excel assigns to an element if the logical_test comes back negative or as FALSE. It's an optional element.

Here's how these three elements look in the IF statement formula:

=IF(logical_test, value_if_true, value_if_false)

In addition to the three main elements of the formula, there are various indicators you can use to personalise your statement. These are some common logical operators you can use:

  • equal to: =

  • greater than: >

  • greater than or equal to: >=

  • less than: <

  • less than or equal to: <=

  • not equal to: <>

Related: 15 basic Excel formulas to learn

How to use the IF formula

To better understand what the IF statement is capable of, it's helpful to review a simple example of how to use it. For example, if you want to determine if the value in one cell is bigger than the value in another cell, follow these simple steps:

1. Choose cells

In this example, the first step to applying the IF statement is to choose two cells whose values you want to compare. It's important to make sure the contents of each cell are in the same format. For example, if you're analysing sales data, it may be helpful to make sure that both cells have values related to currencies. For this example, let's say that the cells you choose are B4 and C4.

2. Determine what you want to test

As a part of this step, you can determine what you want to Excel to assess using the IF statement. For example, you can determine if the value in one cell is greater than the value in the other cell. In this scenario, it's necessary to use the '>' logical operator to compare values in B4 and C4.

3. Enter the formula

Once you know which cells you want to compare and the results you're expecting, you can enter the IF formula in an empty cell. After personalising it to your needs, it would look like this:

=IF(B4>C4,"TRUE","FALSE")

If you're using the statement in a spreadsheet that you share with others on your team, it's helpful to make the results easier to understand for others. To do that, you can include any other statement instead of TRUE and FALSE. For example, your formula could look like this:

=IF(B4>C4,"B4 is greater than C4","B4 is not greater than C4")

Related: How to create an Excel graph in 5 simple steps (with tips)

Common uses of IF statements in Excel

Applying the IF formula to your spreadsheets allows you to compare numerical data using different logical operators regardless of their field, as companies from most industries use some form of data analysis to create reports or measure their progress. Here are some other common uses of the IF statement:

Determining if a cell is blank

One of the most common ways to use the IF statement is to determine if a cell is blank or not. To do this, it's necessary to design a formula that only takes action when it encounters a cell containing some value. In other words, the formula reacts only to cells that aren't empty. This is a straightforward formula that looks like this:

=IF(A1 <> "", result, "")

Once you input your IF statement into the first cell, you can simply drag it down like with any other formula to analyse the rest of your range or the spreadsheet. For example, if you're working with a mailing list and want to determine who has received the company's newsletter, you can change 'result' to 'sent'. Your formula would look like this:

=IF(A1 <> "", Sent, "")

IF with TEXTJOIN to list events between two dates

The IF statement can help you create a list of all events taking place between two dates you specify. For example, you can do this when you want to determine holidays occurring in a month or year. To do that, it's necessary to create a formula based on the IF statement and the TEXTJOIN function. Here's the basic formula to use for it:

{=TEXTJOIN(",

",TRUE, IF(dates >= start, IF(dates <= end, holidays, ""), "")) }

IF with AND to assess several statements

In some instances, for example, when you want to assess several statements using the IF formula, you can use the nested IFs technique. This technique combines several IF functions and allows you to show all possible options in a form of statement results. To create a nested IF statement, you can personalise this generic IF with AND formula:

=
IF(AND(C1 = "x", D1 >= 100), 2.5,
IF(AND(C1 = "y", D1 < 100), 2.4,
IF(AND(C1 = "x", D1 >= 100), 2.3,
IF(AND(C1 = "y", D1 < 100), 2.2,
2.1))))

Related: 6 Microsoft Office skills to include on a CV (with tips)

Additional tips for improving your Excel skills

Expanding your Excel skills can help you improve your qualifications and impress your employer. It's also a great way to prepare for changing jobs as these are transferable skills that many recruiters consider valuable. Here are some additional tips to use when you want to improve your knowledge of Excel:

  • See all formulas: If you're working on a spreadsheet collaboratively, you may want to see all formulas that your team's members used in it. To do that, simply navigate to the 'Formulas' tab and click the 'Show formulas' button.

  • Learn to hide rows and columns: When working with large sets of data, it may make sense to hide information you're not currently using. To do this, simply select the header of the rows or columns you want to hide, right click and select 'Hide'.

  • Consider Pivot tables: Pivot tables are a more complex function you can use which help you view and parse small sections of huge data sets. What's great about it is that Excel has a built-in pivot table tool which you can find in the 'Insert' tab.

  • Include screenshots: To make your spreadsheets more functional and easier to understand, you can add screenshots to them. This way, you can illustrate examples or share suggestions for your team in a visual form.

  • Learn the shortcuts: Although it's possible to navigate through Excel using just your mouse, learning shortcuts can help you complete tasks in less time. Knowing how to use shortcuts for things that you regularly do in Excel improves your productivity and gives you more time to focus on more complex, time-consuming things.

Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.

Explore more articles