Study smart | Excel superstars
Conditional Formatting
Interpreting data and understanding it are basic necessities with any dataset. Conditional Formatting goes a long way toward making sense of the numbers you’re working with
Words Traci Williams, excelace.co.uk
The main challenge with spreadsheets is to make sense of them. Raw data in a worksheet can very quickly become overwhelming and meaningless, so this is where Conditional Formatting helps. It is one of Excel’s most powerful features, transforming bland data into visual insights at a glance and making data instantly interpretable.
Key stats about Conditional Formatting
Type of data (e.g. number, date, text)
Length of entries
- There are eight built-in rule categories for Excel’s Conditional Formatting options: Highlight Cells Rules, Top/Bottom Rules, Data Bars, Colour Scales, Icon Sets, New Rule (custom formulas), Clear Rules and Manage Rules.
- Twelve distinct Colour Scale presets are available out of the box – from two-colour gradients to vibrant three-colour transitions – to help visually map data values.
- From Excel 2007, Microsoft removed the old limit of just three Conditional Formatting rules per cell (as per Excel 2003).
This article will walk you through what Conditional Formatting is, how it works and, most importantly, show you practical examples you can start working with today.
What is Conditional Formatting?
Conditional Formatting allows you to apply dynamic formatting rules to cells based on the values they contain. This means that instead of manually colouring cells, Excel will do the work for you. For example:
- If a sales figure is above target → colour it green.
- If a deadline has passed → highlight it red.
- If a duplicate entry is found → flag it automatically.
The formatting updates instantly as the data changes, so your spreadsheet always tells the right story without you needing to do anything, making it super robust.
Plus, you only need to set up your Conditional Formatting rules once and they will continue to work forever.
Example 1: Highlighting sales above target
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:
1) Select column F.
2) Go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
3) Enter 5000 and choose a Light Red Fill:
Now, every sales figure above £5,000 turns red. Immediately, you can see who is smashing their targets:
Pro tip: Instead of typing 5000 into the Conditional Formatting rule, reference a cell (say, G1) containing the target. When the target changes (in cell G1), your formatting updates automatically:
This tiny tweak can save hours in monthly reporting and provide vital flexibility.
Example 2: Highlighting upcoming deadlines
Let’s say you’re managing project deadlines in column B. You want to highlight:
- Red if the deadline has passed.
- Amber if it’s due within the next 7 days.
- Green if it’s more than a week away.
Here’s how:
1. Select the date column.
2. Go to Home > Conditional Formatting > New Rule > Use a Formula.
3. Enter this formula for deadlines that have passed: =B3<TODAY()
4. Format it red:
5. Add another rule for the next 7 days: =AND(B3>=TODAY(),B3<=TODAY()+7)
6. Format it amber.
7. Add a third rule for later deadlines: =B3>TODAY()+7
8. Format it green:
Now your project plan instantly flags what needs attention and will automatically update every day, as we’ve used TODAY() in the Conditional Formatting rule. No missed deadlines, no surprises.
Pro tip: All three of these rules can be viewed in the Rules Manager: Conditional Formatting > Manage Rules:
The Rules Manager can also be used to amend the ranges the rules are applied to, edit the rules, add/delete rules or change the order that the rules are applied in.
Example 3: Visualising trends with colour scales
Sometimes we’re not looking for yes/no conditions or values, so we need to identify patterns instead. That’s where Colour Scales are the best tool.
Take a column of monthly sales figures. Instead of manually scanning through the numbers, apply:
- Home > Conditional Formatting > Colour Scales > Green-Yellow-Red.
Now, the highest numbers are shaded green, the lowest red and everything else is graded in between.
This is especially useful when dealing with large datasets. For example, a retail chain with 1,000 products can quickly identify its best and worst performers. In seconds, what could have taken hours of analysis becomes obvious.
Pro tip: You can also apply Filter to these colours, to focus on a specific colour.
Example 4: Highlighting duplicates
Duplicate values can cause headaches in lists such as customer IDs, invoice numbers or product SKUs. Spotting them manually? Almost impossible, not to mention time-consuming and extremely boring.
Excel does it in seconds:
- Select the range of data.
- Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Pick a colour.
Now, all duplicate entries are flagged and can save hours of error-checking:
Example 5: Using Icon Sets for quick indictors
Sometimes a simple icon communicates more clearly than colour (these are my personal favourite as they really help to bring a spreadsheet to life).
For example, in a performance dashboard:
- A green arrow = improvement.
- A yellow arrow = no change.
- A red arrow = decline.
To apply, select your data, then go to Home > Conditional Formatting > Icon Sets.
Use the Rules Manager to tweak the rules as you need them:
This option reduces complex reports into a simple “up, down, steady” summary, so requires very little skill or time to interpret the data.
Example 6: Formula-driven formatting
Conditional Formatting can also be based on formulas and this is where they become even more powerful, as the rule can be based on multiple criteria such as:
- Sales are below £8,000 and
- The region is Scotland
To apply this:
- Select ALL of the data (except headers)
- Go to Home > Conditional Formatting > New Rule > Use a Formula.
- Enter this formula for deadlines that have passed: =AND($F2<8000,$A2="Scotland")
- Format in required colour:
Tip: Notice the formula is similar to an IF/AND formula we could enter into a cell, but Conditional Formatting does not require the inclusion of IF.
Keeping Conditional Formatting under control
A quick word of caution: Conditional Formatting is powerful, but it can get messy very quickly. Using too many rules will slow down your workbook and confuse users.
Tips to stay in control:
- Keep it simple – highlight only what matters.
- Use consistent colours across your files.
- Check rules via Manage Rules to avoid duplication.
- Copy and pasting Conditional Formatting ranges will duplicate the rules (use Paste Special, Values where possible to avoid the duplication).
Think of Conditional Formatting like highlighter pens: if you highlight everything, nothing stands out.
Conclusion
Conditional Formatting might look simple, but simple is by no means shallow. Used properly, it is one of the fastest ways to add clarity, intelligence and insight to your data.
In fact, studies show that humans process visual data 60,000 times faster than text. That’s why Conditional Formatting is such a game changer – it turns raw data into something our brains can interpret almost instantly.
So next time you are faced with a daunting spreadsheet, ask yourself: what do I really need to see here? Then let Conditional Formatting do the heavy lifting.
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
Which of the following is NOT a built-in Conditional Formatting option?
2
If you want to highlight cells where sales are greater than a target in cell D1, which formula should you use?
3
Conditional Formatting can slow Excel down if:
The Association of Accounting Technicians. 30 Churchill Place, London E14 5RE. Registered charity no.1050724. A company limited by guarantee (No. 1518983).