IF in Excel: how to use it
With IF you let Excel choose: if the test is true, you get one result, otherwise the other. For example =IF(C2>500,"High","Low").
Try the first exercises for freeIn the lesson The IF function
What does IF do?
IF lets Excel make a choice. You give it a test, such as C2>500, and two outcomes: what Excel should show if the test is true, and what it should show if it isn't.
The outcome can be text, a number or even a calculation. That's why you use IF everywhere: for labels such as Pass and Fail, for a bonus that only applies above a limit, or to deal with an empty cell.
When do you use IF?
- A label based on a limit: =IF(B2>=10,"Pass","Fail").
- A bonus that only applies above a certain revenue: =IF(C2>=500,C2*5%,0).
- A message for an empty cell: =IF(B2="","To fill in","OK").
- Free shipping from a certain amount: =IF(B2>=50,0,4.95).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =IF(C2>500,"High","Low")
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
IF | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | IF |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
logical_test | What Excel should check, such as C2>500. | C2>500 |
value_if_true | What you want to see if it's true. | "High" |
[value_if_false] | Optional. What you want to see otherwise. | "Low" |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. |
IF step by step
E1 contains =IF(C2>500,"High","Low"). 1200 is more than 500. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click E1 and type IF with a bracket.
=IF( - Write the test: is the revenue in C2 greater than 500? Then type a comma.
=IF(C2>500, - Type what you want to see if the test is true. Text goes in quotation marks: "High". Type a comma.
=IF(C2>500,"High", - Type what you want to see if the test isn't true: "Low".
=IF(C2>500,"High","Low" - Close the bracket and press Enter: 1200 is more than 500, so you see High.
=IF(C2>500,"High","Low")
More examples
A calculation as the outcome
C2 contains =IF(B2>=500,B2*5%,0). Sophie reaches the limit of 500, so she gets 5% of her revenue: 31. Type 450 in B2, and the bonus becomes 0.
Dealing with an empty cell
D2 contains =IF(B2="","",B2*C2). As long as the quantity is empty, the total stays empty instead of showing 0. Type a quantity in B2, and the total appears.
Shipping costs
B2 contains =IF(A2>=50,0,4.95). Under 50, you pay 4.95 for shipping. Type 60 in A2, and the shipping becomes 0.
More than two outcomes
You can put an IF inside an IF, but that soon becomes unreadable. Use IFS instead.
Two conditions at once
If two things must both be true, put AND in the test: =IF(AND(B2="Leeds",C2>=500),"Yes","No"). If one of the two is enough, use OR: =IF(OR(B2="Leeds",B2="York"),"Yes","No").
Checking whether a cell is empty
Two quotation marks in a row stand for an empty cell. With =IF(B2="","Still to fill in",B2*1.2) you don't see a misleading 0 while there's no amount yet.
Where it often goes wrong
- Text goes in quotation marks, numbers don't.
- An amount written as "500" in quotation marks becomes text.
Practise this in the lesson The IF function. The first exercises are free.
Frequently asked questions
Can IF return a calculation?
Yes. =IF(C2>=500,C2*0.05,0) gives 5% of the revenue once it reaches 500, and 0 otherwise.