How to Use IF Function in Excel
Formulas are the heart of Excel. With a huge list of built-in formula and functions, Excel stands out from other spreadsheet software. Formulas are used for calculating and analyzing data based on values in designated cells. Excel supports trigonometric, statistical and other functions. You can also create a new rule, or constraint, to apply over your datasheet.
This article covers how to use IF functions in excel and conditional formatting, which are essential concepts every spreadsheet user should grasp at least on a basic level. Don’t worry, we’ll make this easy to understand, even if it’s your first time hearing about functions or formulas.
IF function syntax
Before we get started, it’s crucial to understand how to structure IF functions. All formulas you’ll use IF functions in have to comply with the same structure/syntax. Here’s what that looks like:
It’s totally fine if that doesn’t make sense yet. We get into this a lot more below.
Another important element is understanding the concept behind IF functions. If you’re new to this function, it’s a good idea to say aloud exactly what you wish to get out of it. In other words, clearly define the data you want to analyze and the result you’re after.
For example, in the table below, which we’ll use throughout the rest of this article, you can see it lists students and their respective grades. However, we only have the number grade, and we want the letter grade (A, B, C, etc.) without having to do the math ourselves.
For example, instead of typing A next to each student who got a 90 or above, we’ll use the IF function to do it all for us. You can see how beneficial this function can be with a class of hundreds of students, or a spreadsheet with thousands of rows of data.
Now that we know the results we want to create, we can rewrite the IF statement in English to better get an idea of what we’re doing:
Unfortunately, Excel doesn’t understand plain English, so we have to rewrite it using the language Excel does understand.
Tip: Learn how to spell check in Excel if your spreadsheet contains more than just numbers.
How to write basic IF statements in Excel
We want our letter grade to go in the Grade column, starting with E2 for the first student. We can see that they received a 60 in the class. Let’s say we want to mark that grade level as a D. We could type this in that cell:
This formula will write D if cell D2 is greater than 59. Great! But there’s still a problem…we need the formula to apply to every grade, no matter what the number is. If we apply this same formula to the other students, Excel won’t know the right letter grade to write because it’s not in our formula.
Instead, we can merge multiple IF statement into the same formula as a sort of catch-all for all the possible grades.
Tip: The greater-than sign is similar to other math operations. For example, you can use addition in Excel to do math right there on your spreadsheet.
How to write multiple IF statements in the same cell
Combining several IF statements into one formula looks messy, as you’ll soon see, but it’s actually very helpful. It’s the best way to apply numerous logical tests with different value outputs, which is exactly what we need in a case like this where our table has different values.
I recommend that you first write out each logical statement. For our example, we already have the formula for calculating the D letter grade, so we just need to come up with the logic for the other ones. Here’s what we’ll use:
- IF(D2>89,”A”
- IF(D2>79, “B”
- IF(D2>69,”C”
- IF(D2>59,”D”
- “F” will be the leftover value that will apply if none of the other statements are true. In essence, anything that’s 59 and lower will be marked as an F.
The trick now is putting all of that together. You’ll need to watch for commas and parentheses. Here’s what that looks like:
You’ll notice our Status column is blank. Here, we want to apply similar logic, but a value of Pass or Fail instead of a letter. To do this, first decide which letter grades you want to define as a passing grade and which ones mean that the person failed.
Here’s what we’ve come up for our example:
- IF(E2=”A”,”Pass”
- IF(E2=”B”,”Pass”
- IF(E2=”C”,”Pass”
- IF(E2=”D”,”Fail”
- IF(E2=”F”,”Fail”
Putting it together looks like this:
Use conditional formatting for visual results
Conditional formatting is similar to using the IF function in Excel. It lets you define one thing based on another piece of data. It’s most helpful if you’re a visual person. For instance, for our example, we could use conditional formatting to mark all passing students with a green color to make them really stand out.
Here’s how we can do this (your selections will vary depending on your specific data):
- Highlight the cells you want the formatting to apply to. We’re selecting the whole F column.
- Open the Conditional Formatting menu from the Home tab.
- Go to Highlight Cell Rules > Equal To.
- Choose Specific text, Containing, and Pass from the menus.
- Select a color from the Format with drop-down menu.
- Press Done.
<file:excel-conditional-formatting;alt:Red and green conditional formatting rules in Excel>
Immediately, you can see that all the students who have passing grades are highlighted in green. You can then repeat the steps and pick a red color for the Fail values.
How Can I do this? If Column B is Highlighted Yellow place an “x” in column A.
I am having trouble with an excel formula. If(j24=X,”Y”,””) and it keeps giving me “N”. In a previous row I used IF(J21=x,”N”,””)
thank u for helping….wonderful explanation
PLEASE HELP: If cell G7 is 3000643 or 3001283 then auto populate cell D7 with Lamb01
Hi there Carol,
In cell D7 input the following formula:
=IF(G7=3000643,IF(G7=3001283,”Lamb01″,0))
No way, Jose?
IF(or(AND(AND(C3>=35,D3>=35,E3>=35,F3>=35,G3>=35,H3>=35),I3>=210,I3=35,D3>=35,E3>=35,F3>=35,G3>=35,H3>=35),I3>=301,i=35,D3>=35,E3>=35,F3>=35,G3>=35,H3>=35),I3>=360,I3<=600),"first","fail")) WWhat is error in this formula please rectify or suggestion
Conditional Formatting issue: I am using a formula that references a cell in another sheet in the same workbook (example: =B23other sheet!H4). When I apply it, it works fine and does what it is supposed to do. After saving and reopening the file the formatting is GONE. Cant I reference a cell from another sheet? If so, why is the conditional formatting disappearing? I tested it with a formula comparing cells on the same sheet – no issue after saving. The issue only appears when I reference the other sheet.
I wish to shade or format a cell (alignment, strikethrough) based on the value of another cell. The cell I need to format already contains a lookup value from another sheet.
I have a column that shows whether a person in a male or female and another column which displays the age. So I woul like to test whether a peson’ age >= 65 AND if this person is male or >= 60 AND female (reason females retire at age 60 and males retire at age 65) than the result if true should be Pensioner if fales Not Pensioner.
Hi,
for a table where the column heads (A1, B1, C1, D1) are:
NAME SEX AGE STATUS
in column under the STATUS (D2)
the formula would be
=IF(AND(B2=”male”;C2>=65)=TRUE;”Pensioner”;IF(AND(B2=”female”;C2>=605)=TRUE;”Pensioner”;”not pensioner”))
cell E6 turns into a C+ nmark with this formula ,, not a B+.
what if i want to use if and and function how does it work ?? for example, based on Man, teenager and senior, if he’s a senior and he has more than $1000 in his bank account, “rich” should be the outcome in the designated cell, and if he’s a teenager and he has an amount between $200 and $500 in his bank account “Moderate” should be the outcome….and so on like multiple IF AND formula!! how does that work
I need to show different cell shading according to the figure in the cell. However, I want to show the same cell shading for a positive rating as for a negative rating e.g. greater than +50 might be shaded red and greater than -50 (less than really) should also be shaded red. I don’t seem to be able to do this with conditional formatting alone. Any thoughts?
hay
i need samall format for codinational formt for equil Serial Number in cell colour change
just like A1= 25125 F, A85 =25125 F after Colur Change
Does anyone knows a way to format a cell depending of the outcome of an if function?
and i don’t mean conditional formatting. the issue i have is my IF&VLOOKUP function combination evaluates whether a cell in a source sheet is empty or not and then based on that it calculates a value or copies the value from the source sheet. As this is an automatic update i would like to show to the viewer if the value in the cell has been calculated or extracted from the source sheet by formatting it differently.
Did anyone else notice the error in the formula for “B” grades? The way it’s written, the student with a 70 gets a “B+” and the student with 78 gets a “B”. It should be changed to:
=IF(D2>=80,”A”, IF(D2>=75, “B+”, IF(D2>=70,”B”, IF(D2>=65,”C+”, IF(D2>=60, “C”,”D”)))))
that is due to the order of the conditional settings. easily fixed
Hi there,
Could you help me on the following:
If i have two columns A and B. In the first A lets say that we have the currency for example EUR, USD, CHF, in the other column B I have the Values in Original Currency. I want to add another column C that gives me the Equivalent in EUR.
=IF(A2=”EUR”,b2*rate!$B$1,IF(A2=”USD”,-B2*rate!$B$2,IF(A2=”CHF”,-B2*rate!$B$3,-K2))). The sheet rate! has the the daily exchange currency ratio.
Best regards,
Rubin
Thanks for this very helpful tip. I was looking for something to change colour of text for values below certain number.
There is a Quick Reference Card available for VLOOKUP here
http://officeimg.vo.msecnd.net/en-au/files/818/530/AF101984660.pdf
Although the purpose of the page is conditional formatting, it would benefit from using a less clumsy formula.
For something like student grades, it is important that your grade lookup is in order (ie: lowest grade to highest grade) and you set the “range lookup” field to true (or 1)
Suppose you want same color for the full row after conditional formatting, what do i do???
Pls help…
For something like this, you really should be using VLOOKUP instead of IF
The example above is promoting bad practice
Mine worked fine..
great
Hey,
If you are going to post something like this on the web, at least try them out, I just tried the very same freaking formula on the same freaking format you are showing it and the motherfucking thing does not work!
Grow up and learn how to express yourself like a man.