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

Roundel, reading "test your knowledge with our quiz - click here"

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:

Excel screenshot showing a list of sales reps, their regions, products, dates, quantities and value of sales.

1) Select column F.

2) Go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than.

3) Enter 5000 and choose a Light Red Fill:

Excel screenshot showing conditional formatting for greater than 5,000 with light red fill

Now, every sales figure above £5,000 turns red. Immediately, you can see who is smashing their targets:

Excel screenshot showing a list of sales reps, their regions, products, dates, quantities and value of sales. Values above £5,000 are in light red

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:

Excel screenshot referencing a cell (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:

Excel screenshot showing how to highlight upcoming deadlines. Those past due are in red.

5. Add another rule for the next 7 days: =AND(B3>=TODAY(),B3<=TODAY()+7)

6. Format it amber.

Excel screenshot showing how to highlight upcoming deadlines. Those late are in red, while those in under a week are in yellow.

7. Add a third rule for later deadlines: =B3>TODAY()+7

8. Format it green:

Excel screenshot showing how to highlight upcoming deadlines. Those late are in red, while those in under a week are in yellow. Those over a week away are in 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:

Excel screenshot showing all three formats for deadlines.

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.

Excel screenshot showing visualised trends. 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:

  1. Select the range of data.
  2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
  3. Pick a colour.

Now, all duplicate entries are flagged and can save hours of error-checking:

Excel screenshot showing duplicate entries highlighted in red.

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.

Excel screenshot showing icons communicating improvements, declines and who/what has stayed the same.

Use the Rules Manager to tweak the rules as you need them:

Excel screenshot showing the rules manager, where you can manage icon sets.

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:

  1. Select ALL of the data (except headers)
  2. Go to Home > Conditional Formatting > New Rule > Use a Formula.
  3. Enter this formula for deadlines that have passed: =AND($F2<8000,$A2="Scotland")
  4. Format in required colour:
Excel screenshot showing how to use a formula to determine formatting rules

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?

A Highlight Cells Rules
B Data Bars
C Chart Overlays
D Icon Sets

2

If you want to highlight cells where sales are greater than a target in cell D1, which formula should you use?

A =B2>5000
B =B2>D1
C =B2>$D$1
D =B2=D1

3

Conditional Formatting can slow Excel down if:

B You apply it to very large ranges with many rules
A You only use it with duplicates
C You use it with TODAY
D You save your file in OneDrive
Back to the top
Back to contents
Back to section

The Association of Accounting Technicians. 30 Churchill Place, London E14 5RE. Registered charity no.1050724. A company limited by guarantee (No. 1518983).