5 Cases to Use Microsoft Excel IF Function in Practice

IF function, also known as conditional function, enables you to get corresponding results in Microsoft Excel spreadsheets when meeting the specific conditions.

How Does IF Function Work

The function syntax of IF function is: IF(logical_test,[value_if_true],[value_if_false]).

If the specified conditions evaluate to true, this function will return a value; If the specified conditions evaluate to false, another value will be returned.

For example, if the value in A1 is larger than 10, the formula: =IF(A1>10, “More”, “Less or equal”) will return “More” as the result; if the value in A1 is not larger than 10, the formula will return “Less or equal”.

When Should You Use IF Function

IF function is one of the most commonly used function in Microsoft Excel, it’s very practical and also easy to grasp. Here are some pratical situations that you can make use of it to significantly save your time and effort. Let’s have a look.

• Compare 2 Columns of Data in Excel
• Determine Whether Someone is Qualified (Pass or Fail)
• Evaluate the Level According to Specific Criteria
• Find Duplicates in an Excel Column
• Plus or Minus a Number if Specific Conditions are Met

How to Use IF Function in Pratical

1. Compare 2 Columns of Data in Excel

When there’s a large amount of data in the table, it’s difficult to judge whether the data in 2 columns are the same. On this occasion, IF function can do the job for you quickly and accurately.

Steps:

Enter the formula next to the columns that you want to compare.

Here I want to compare the data in A1 and B1 at first, so the formula can be: =IF(A1=B1,”Same”,”Different”).

Press [Enter] or click anywhere outside the cell to see the result.

You can then drag your mouse to apply this formula to all cells in column C using fill handle.

2. Determine Whether Someone is Qualified (Pass or Fail)

Similarly, if you want to mark the students whose grades are above a specific score or below it, you don’t need to judge their grades one by one which will doubtlessly take a lot of time.

Steps:

Assuming the students can pass with a score above 300, the formula can be: =IF(C2>300,”Pass”,”Fail”). Enter it beside the grade column.

Use fill handle to apply the formula to multiple cells.

3. Evaluate the Level According to Specific Criteria

There are many fields that need to be graded, like the quality of products and the scores of students. With IF function, you can evaluate the level very conveniently.

Steps:

Confirm several scores as the points to divide different grades. In my case, I want to evaluate the grades of students in the table below, so the formula can be: =IF(C2<250,”D”,IF(C2<350,”C”,IF(C2<450,”B”,”A”))). It’s the combination of several conditions, and the grades will be divided into 4 levels: A(>450), B(350~450), C(250~350), D(<250).

Enter the formula beside the existing column of scores, then use fill handle to apply it to all the selected cells. In this way the scores of students will graded according to the conditions you set within seconds.

4. Find Duplicates in an Excel Column

With IF function, you don’t need to find the duplicates manually as well. The formula can do better and quicker than your eyes.

Steps:

If the list of data is in column A, create a new column to return the result and enter the formula: =IF(COUNTIF(A:A,A2)>1,”Duplicates”,””).

Thus only the repeated data will be marked with “Duplicates”.

5. Plus or Minus a Number if Specific Conditions are Met

If you’ve ever been in charge of the payroll in a company, you must be familiar with the situation that among all these employees, some people from specific departments have higher bonuses and some people with larger working years have higher salaries. At this point, how to calculate the wage for all the people without spending too much time?

You may already know the answer – IF function. If you are in a similar situation and need to process a huge amount of data, let Excel run it automatically.

Steps:

The process is all the same. Just enter the proper formula in a new formula and press enter to return the corresponding value. What you need to pay attention to is entering a right formula according to the function syntax and also your actual situation.

For example, if you want to add 500 to all the employees whose working years are more than 3, the formula should be like: =IF(D2>3,E2+500,E2).

Then use fill handle to apply it to other cells.

Data processing is not always so difficult as long as you use functions smartly and enter the right formula. IF function is one of the most powerful functions you can use a lot in work as well as daily life. Have a try, you’ll love the feeling of solving practical problems so quickly and easily.