[Fixed!] IF Function Is Not Working in Excel (4 Quick Solutions)

Get FREE Advanced Excel Exercises with Solutions!

The IF function is one of the most used functions in Excel. We use it in a single form or a nested formula for different kinds of logical tests. But sometimes you may face some issues while using the IF function. If you are already suffering from one of them and finding solutions for that then you have come to the right place. Here, we’ll show 4 major reasons and solutions when the IF function is not working in Excel.


When IF Function Is Not Working in Excel: 4 Possible Fixes

Here’s our sample dataset that we’ll use to explore the reasons and solutions. It represents some person’s name and gender.

If Function Is Not Working in Excel


1. Remove Leading Space

Leading space is the most common reason for which some formula doesn’t work properly. It can affect the IF function too. Have a look at the dataset, we applied the IF function to return Yes for Males and No for Females. But the second output is returning the wrong result.

Remove Leading Space When If Function Is Not Working in Excel

Here’s the formula and there’s nothing wrong with the formula.

When If Function Is Not Working in Excel

The reason is there is remaining a leading space in the value of Cell C6. that’s why the IF function couldn’t recognize the value and returned the wrong result.

Solution:

  • Simply delete the space or use the TRIM function before the cell reference like the image below.


2. Change Wrong Delimiter

Another common issue is using the wrong delimiter or character. While using a function in Excel we had to use commas between the arguments. So mistakenly if you use a semicolon or any other delimiter in the IF function then Excel will show an error message like the image below.

When If Function Is Not Working in Excel

Solution:

  • Delete the wrong delimiters and use commas between the arguments.

Read More: How to Use Excel IF Function with Range of Values


3. Change Cell Format

If we store numbers in Text format then it creates a lot of problems in many formulas. Consequently, it affects the IF function too. Look, at the image below. We modified the dataset by inserting the ages of the persons.

When If Function Is Not Working in Excel

And when we used the IF function to return Yes for ages greater than 18 then it’s returning the wrong result. There are no mistakes in the formula. Then what can be the reason?

When If Function Is Not Working in Excel

The reason is we stored the numbers in a Text format instead of a Number format. So the IF function was considering it as text values and returning the wrong result.

Solution:

  • Store the numbers in Number format or convert them. To convert them, select the cells and click on the error icon, then select the Convert to Number option.

Now see, it’s giving the accurate result after the conversion.


4. Remove Unusual Characters from Argument

While inserting text in a formula we had to use double quotation marks (“”). But some values don’t need it. Here’s another modified dataset that is showing TRUE for married persons and FALSE for unmarried persons. Now we’ll apply the IF function to return Allowed for married persons and Not Allowed for unmarried persons.

When If Function Is Not Working in Excel

Here’s the formula, there’s nothing wrong with the formula but look, it’s showing Not Allowed for every value.

When If Function Is Not Working in Excel

The reason is, we used double quotation marks in the logical part C5=”TRUE” but Excel considers the value TRUE as the number 1 and FALSE as the number 0. As we know numbers don’t need double quotation marks in the logical test, So it doesn’t need the double quotation marks additionally.

Solution:

  • Just delete the double quotation marks from TRUE or FALSE in the formula.

Now see, the formula is giving the right result.

Read More: How to Make Yes 1 and No 0 in Excel


Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


Conclusion

That’s all for the article. I hope the procedures described above will be good enough to fix the problem when the IF function is not working in Excel. Feel free to ask any question in the comment section, and please give me feedback.


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo