TRUE and FALSE in Excel: how it works
A comparison such as =B2>100 gives TRUE or FALSE. You use that result in IF, in COUNTIF and when filtering.
Start practising hereIn the lesson Comparing: TRUE or FALSE, free
What are TRUE and FALSE?
Every comparison in Excel has only two possible outcomes: TRUE or FALSE. =B2>100 asks whether B2 is greater than 100. If it is, you get TRUE, and if not, FALSE.
A comparison like that is the building block of many functions. IF chooses an outcome based on TRUE or FALSE, COUNTIF counts the cells for which the comparison is true, and AND and OR combine several comparisons.
When do you use a comparison?
- Checking whether an amount is over a limit: =B2>100.
- Comparing two cells, such as what was ordered and what was delivered: =B2=C2.
- Checking whether a cell has been filled in: =A2<>"".
- Putting a comparison in IF to show your own text: =IF(B2>100,"Expensive","Cheap").
Step by step
D1 contains =B2>100. 149 is more than 100, so TRUE. Change the numbers or the formula and see what happens.
This is how to go about it:
- Click D1 and type an equals sign: that tells Excel a formula is coming.
= - Click the cell you want to check, B2.
=B2 - Type the greater-than sign, followed by the limit.
=B2>100 - Press Enter: 149 is greater than 100, so you see TRUE.
More examples
Comparing two cells
D1 contains =A2=B2. 24 isn't equal to 20, so FALSE: some units are missing.
Calculating with TRUE
C1 contains =(A2>100)*50. The comparison gives TRUE, and in a calculation TRUE counts as 1: 1 × 50 = 50. Type 80 in A2, and you get 0.
Comparing text
C1 contains =A2="Open". Capital letters make no difference: open and Open are the same to Excel.
The signs
= is equal to, <> is not equal to, > is greater than, < is less than, >= is greater than or equal to, and <= is less than or equal to.
Calculating with TRUE and FALSE
In a calculation TRUE counts as 1 and FALSE as 0. So =(B2>100)*50 gives 50 if the price is above 100, and 0 otherwise.
Where it often goes wrong
- You compare text between quotation marks: =A2="Leeds". Upper and lower case make no difference.
- A number between quotation marks is text: =B2>"100" doesn't compare what you expect.
Practise this in the lesson Comparing: TRUE or FALSE. It's free.
Frequently asked questions
Why do I see TRUE instead of my own text?
A comparison always gives TRUE or FALSE. If you want your own text, put the comparison inside IF: =IF(B2>100,"Expensive","Cheap").