Study smart | Excel superstars
How to use IF formulas
In Excel, the IF function is one of the most powerful tools for decision-making in spreadsheets, yet it’s also one of the most misunderstood
Words Traci Williams, excelace.co.uk
Whether you are flagging overdue invoices, grading scores or categorising sales, the IF function allows Excel to evaluate conditions and return results based on your criteria.
In this article, we will explore IF from the ground up: starting with simple formulas and moving into nested IF statements, before finally looking at even smarter combinations with AND and OR that can save you time and frustration.
The basics of the IF function
The IF formula is made up of three elements:
- logical_test – The condition you want Excel to check (e.g. A2 > 1,000).
- value_if_true – The result if the condition is TRUE.
- value_if_false – The result if the condition is FALSE.
In this example, cell A2 contains a sales figure. The formula in B2 checks whether the amount exceeds 1,000:
If A2 is 2,000, Excel returns ‘High’; if it is less than 1,000, Excel returns ‘Low’.
You can use all the usual logical operators in your test:
The elements of the IF function
The IF formula is made up of three elements:
- logical_test – The condition you want Excel to check (e.g. A2 > 1,000).
- value_if_true – The result if the condition is TRUE.
- value_if_false – The result if the condition is FALSE.
In this example, cell A2 contains a sales figure. The formula in B2 checks whether the amount exceeds 1,000:
IF examples
Here are a few examples where an IF formula can add instant value:
Pass or fail:
IIn this instance, there is a list of sales values in column F and the target is £5,000. You want to identify any sales that beat the target:
If the score in A2 is 50 or higher, Excel displays ‘Pass’; if it is less than 50, it shows ‘Fail’.
Overdue invoices:
This compares the due date in column B with today’s date. If the due date is earlier than today, it will return ‘Overdue’; otherwise, it will show ‘OK’.
Note: this image was created on 17/10/2025
Department bonus:
If the Department column contains ‘Sales’, 10% is applied in the Bonus column; otherwise, the amount is zero.
Nested IF
A nested IF is when you place multiple IF formulas inside another. This allows you to evaluate multiple conditions in sequence. For example, grading scores:
This is how Excel processes this:
- If column A is greater than (or equal to) 90 – return ‘A’.
- If not, check column A is greater than (or equal to) 80 – return ‘B’.
- If not, check column A is greater than (or equal to) 70 – return ‘C’.
- If none of the above – return ‘Fail’.
Nested IFs are brilliant for tiered logic: think pricing bands, age groups, service levels or classifications. But, as the number of conditions grows, the formulas can become long and difficult to read.
Nested IF examples
Let’s say we’re calculating sales discounts based on order value:
Order Value Discount
< £1,000 0%
£1,000–£4,999 5%
£5,000–£9,999 10%
£10,000+ 15%
This is how Excel processes this:
- If the value is less than 1,000, discount is 0%.
- If not, check if it’s less than 5,000 and the discount is 5%.
- If not, check if it’s less than 10,000 and the discount is 10%.
- Otherwise, apply 15% discount.
Top Tip: Use Alt + Enter inside the formula bar to insert line breaks within the cell. This makes long nested IFs far more readable.
Using IF with AND and OR
Once you have mastered basic IF and nested IF statements, the next step is to combine them with AND and OR functions. This allows you to evaluate multiple conditions at once, making your formulas more flexible and powerful.
The AND function returns true only if all conditions are met.
For example, suppose you want to check whether a sales rep has achieved both a minimum number of units and a minimum revenue target:
This is how Excel processes this:
- If the value in column B is greater than (or equal to) 100 AND the value in column C is greater than (or equal to) 5,000, ‘Bonus’ will be returned.
- If one or more of these conditions is NOT met, ‘No Bonus’ will be returned.
The OR function returns TRUE if any of the conditions are met. For example, imagine a shipping scenario where an order qualifies for priority handling if it’s either urgent or over a certain value:
This is how Excel processes this:
- If the Order Type in column B is ‘Urgent’, ‘Priority’ will be returned in column D, regardless of the Order Value.
- If the Order Value in column C is greater than 10,000, ‘Priority’ will be returned in column D, regardless of the Order Type.
- If none of these conditions are met, ‘Standard’ will be returned.
Common mistakes and troubleshooting
IF formulas can trip up even the most experienced users, so here are a few pitfalls to be wary of:
- Missing brackets – Each IF needs a closing parenthesis. Count carefully or use line breaks for clarity.
- Forgetting quotes around text – "Sales" not Sales.
- Swapping true/false values – Double-check your logic reads clearly.
- Text mismatches – Extra spaces or case differences can cause unexpected results.
- Multiple Categories – Lookup formula (like XLOOKUP or VLOOKUP) can make the logic easier to manage and update than long nested IF statements.
Top Tip: Use Excel’s Evaluate Formula tool (Formulas tab, Evaluate Formula) to watch Excel process each step. This is perfect for highlighting a bracket in the wrong place.
Conclusion
CThe IF function is at the heart of Excel’s decision-making power. Start simple and get comfortable with single IF statements and logical tests, then layer up with nested IFs for more complex logic.
As your formulas grow, the IF formula can become a little unwieldy, so consider Lookup formulas to make your work cleaner, faster and easier to maintain.
The best way to learn is by experimenting with real data: try categorising, flagging or calculating based on the rules your business already uses. Once you master IF, you will unlock a huge amount of Excel’s potential.
QUIZ TIME
Test your knowledge
Roll over the buttons to find the green correct answer or click on the buttons to find the correct answer
1
1. Which of the following is the correct syntax for the IF function in Excel?
2
2. You need to classify sales values into bands:
. Under 1,000 = “Low”
. 1,000-4,999 = “Medium”
. 5,000+ = “High”
Which of the following nested IF formulas will correctly return the classification?
3
3. What will the following formula return if cell B2 contains the number 75?
=IF(B2>=80,"Pass","Fail")
The Association of Accounting Technicians. 30 Churchill Place, London E14 5RE. Registered charity no.1050724. A company limited by guarantee (No. 1518983).