How to Use Excel IF-THEN Formula: 5 Best Cases

When working on Excel with advanced dashboards for budgets, expenses, data entry, etc., you might need to automate several calculations using logical tests. Here, the Excel IF-THEN formula comes to your rescue.

IF-THEN statements have long been used in programming as a logical test. Many advanced and intellectual computer programs contain thousands of IF-THEN statements.

Microsoft offers the same conditional programming of calculations via the IF-THEN formula in Excel. Using this IF-THEN Excel formula, you can automate your Excel spreadsheets where the data entry operator will simply enter a value or text, and the Excel worksheet will itself fill in the necessary details, perform calculations in seconds, and give the final output you need.

Join me as I explain below the definition of the Excel IF-THEN formula, use cases of the Excel formula IF-THEN, and how you write an IF-THEN formula in Excel yourself without any external help. In the end, you become an expert Excel user for IF-THEN logical testing.

Contents

1 What Is the Excel IF-THEN Formula?
2 How Does the Excel IF-THEN Formula Work?
3 When to Use IF-THEN Formula in Excel
4 Conclusion

What Is the Excel IF-THEN Formula?

IF-THEN formula in Excel is nothing but the widely used IF function of the Excel formulas library. Since it tests a given logic and performs actions based on the logic, it’s widely known as the IF-THEN Excel formula.

Using this formula, you can compare values in multiple cells of Excel logically. If the formula is true for logic, Excel will enter a text, calculate a value, format cells, and so on. The THEN actions depend on your creativity in Excel worksheets. IF-THEN formula is compatible with many logical testing, and other formulas, including the following:

< (less than)
> (more than)
>= (more than or equal to)
= (equal to)
<> (not equal to)
<= (less than or equal to)
IF with AND
Nested IF
IF with OR
IF with XOR
IF with NOT
IFS for complicated logic

How Does the Excel IF-THEN Formula Work?

The functioning of the IF-THEN Excel formula is fairly simple. If you can accurately supply some data like the IF argument supported with a logical test and output instructions in the order Microsoft Excel accepts, you shouldn’t face any problems.

Hence, look at the construction of the function below and understand its various arguments:

The simple explanation of the formula is as follows:

=IF(the logical test is True, then do what asked, otherwise do something else that was asked)

Building on the above simplification of the Excel IF-THEN formula, find below its actual syntax for every Excel desktop version including the Excel web app:

=IF(your logical_test, your value_if_true, your [value_if_false])

The above function for the IF-THEN statement in Excel is fairly simple. Now, find below how do you write an IF-THEN formula in Excel in a real Excel worksheet with numbers, values, and text:

First off, you create a few column headers to mention some value, names, tasks, figures, etc.
Then, you enter the actual data from field reports or other applications.
Now, you choose a target cell where you need the outcome of a logical test performed by the Excel formula IF-THEN.

Explore How Does the Excel IF-THEN Formula Work

Enter the equals sign (=) and then type IF.
The formula suggestion tool of Excel will show all the available IF functions for the current Excel desktop or web app.
Double-click the first IF function result, and the formula editor will show up a guided path to enter one or many conditions to be met by the reference cell, text/values to be entered if the condition is true, and values/texts to be displayed if the logical test is negative.

The structure of the Excel if then formula — Discover what is the structure of the Excel if then formula

You must enter your arguments and resulting values in steps as shown above in the formula example image.
First off, you supply a logical test like Sales Value is more or equal to or less than the Target This Month column header.
If your logical condition is acceptable by Excel, you’ll be able to enter Value_if_True data.
Similarly, if this part is acceptable, you can enter the Value_if_False data.
If you’re entering any text as Value_if_False and Value_if_True, keep those in quotation marks. Alternatively, when you must enter another formula for Value_if_True and Value_if_False, don’t keep the formulas in quotation marks.
Once everything in the function looks as per the above-mentioned syntax, close the formula with a closing parenthesis.

IF-THEN Excel formula with formula and text for Then values

Hit Enter to get the results you’re expecting.

So far, you got the answer to your question, “how do you write an IF-THEN formula in Excel?” For simple Excel IF-THEN formula cases, it shouldn’t be challenging for you to enter the syntax with appropriate logical conditions, then values when the logical test is true, and then values when the logical test isn’t true.

However, the last argument of this function is optional. You can leave the target cell blank for easier identification if the logical condition isn’t met for a cell reference by typing in quotation marks only, no values are needed.

Now, you’re prepared to learn more real-world use cases of the IF-THEN formula in Excel, so you get a clear idea of where exactly the Excel formula IF-THEN fits.

When to Use IF-THEN Formula in Excel

1. Assessing Students or Job Candidates

Let’s say you’ve got a spreadsheet with thousands of students’ or job candidates’ marks obtained in a competitive examination. You’ve set a percentage for the shortlist cut-off, which is 65%.

Now, you can’t just go through each student’s, or job candidate’s percentage marks obtained one by one to create a shortlist because it would take days, and there’ll be an increased chance of errors. Instead, you use the following Excel formula IF-THEN:

=IF(B2>C2,“Shortlisted”,“Try Again”)

2. Using Mathematical Operators in an IF-THEN Excel Formula

You can use simple mathematics like multiplication, division, addition, and subtraction in Excel in the IF-THEN formula as a value for the Value_if_True and Value_if_False arguments.

For example, you need to charge interest on long-term tabs in your diner. Now, you decided to charge 5% interest to those whose tabs are older than 2 years, and the rest will be 2%. Here’s how the IF-THEN formula will look in this scenario:

=IF(C2=2,B2*0.05,B2*0.02)

3. Excel Formula IF-THEN With a Nested Formula

You can use a formula as the partial logic of the logical condition in an IF-TEHN formula. This is known as nesting a different formula in another function.

For example, you need to evaluate a group of students from different high schools. These student groups participated in a science exhibition, and your organization awarded these groups a mark depending on their project and presentation.

Discover Excel Formula IF-THEN With a Nested Formula

Let’s consider there are hundred school groups that you need to evaluate. The best option is the following IF-THEN formula nested with an AVERAGE formula:

=(IF(AVERAGE(B3:B4>90),“Gold Medal”,“Silver Medal”))

4. IF-THEN Formula in Excel for Inventory Management

If you run a small business or a home-based business and find it challenging to keep track of product inventory, this is the perfect Excel IF-THEN formula for you.

Here, you or your business operator can mark a product as sold. Instantly, an Excel worksheet will subtract the product from its primary inventory showing the current balance of the product according to your books. Here’s the formula you can use:

=IF(C2>0,A2-C2,A2)

5. Multiple IF-THEN Excel Formulas in One Cell

Excel allows you to stack up to 64 IF-THEN statements in a single function for multiple conditions-based calculations of values for specific cell ranges. For instance, look at this situation:

You pay commissions to sales agents per unit of vehicles sold in the 50 states of the US. Now, the commission rate is different from state to state.

Let’s consider there are hundreds of sales agents in these 50 states. Also, you sell thousands of units of vehicles each day.

Discover how top use Multiple IF-THEN Excel Formulas in One Cell

Then, calculating the agent commissions from these automobile sales could be a daunting task even if you use an Excel spreadsheet. However, you can nest up to 50 different IF-THEN conditions in one formula to calculate commissions earned from different states in a flash. Here’s the formula you should use:

=(IF(A3="Arizona",D3*$H$3,IF(A3="California",D3*$H$4,IF(A3="Nevada",D3*$H$5,IF(A3="Washington",D3*$H$6,IF(A3="Texas",D3*$H$7))))))

Until now, you’ve learned various real-world use cases of the Excel IF-THEN formula, you can modify the above formulas based on the exact scenario you need to resolve and use them wherever it suits you. Enjoy working on Excel worksheets and automating formulas using IF-THEN statements!

Conclusion

Now you know how to use the Excel IF-THEN formula in simple to very complex calculations in an Excel spreadsheet.

This will in turn help you to automate various manual calculations and the final analysis of your Excel worksheet and save time. Not to mention, the risk of error by human-based analysis of logical tests can be avoided by using the Excel formula IF-THEN.

Don’t forget to comment below if you know any unique use cases for the Excel IF-THEN formula.

Next up, learn to make a copy of an Excel sheet and copy-paste values without formulas in Excel.