How to Use Conditional Formatting in Excel

Microsoft Excel is more than just numbers and formulas — it’s also a powerful tool for spotting trends and patterns in your data. One of the best features for this is Conditional Formatting.

Conditional Formatting allows you to automatically apply colours, icons, or styles to cells based on the values they contain. For example, you can highlight sales above target in green, flag overdue dates in red, or create heatmaps that show trends at a glance.

In this guide, we’ll walk through the basics of Conditional Formatting, show examples, and highlight advanced options.

👉 Conditional Formatting is introduced in our Excel Introduction Course, expanded in the Excel Intermediate Course, and taken further in the Excel Advanced Course and Excel Power User Course.

Why Use Conditional Formatting?

• Highlight important values – e.g., overdue tasks, low stock levels.
• Make patterns visible – e.g., growth trends in sales.
• Improve decision-making – see at a glance what needs attention.
• Save time – Excel applies formatting automatically based on your rules.

Getting Started with Conditional Formatting

You’ll find the Conditional Formatting menu on the Home tab in the ribbon.

Basic Options Include:

• Highlight Cell Rules → Greater Than, Less Than, Between, Equal To.
• Top/Bottom Rules → Top 10 items, Top 10%, Below Average.
• Data Bars → Horizontal bars to show relative values.
• Colour Scales → Heatmaps that apply colours based on value.
• Icon Sets → Traffic lights, arrows, stars, and more.

Example 1: Highlighting Sales Above Target

Imagine you have monthly sales figures in a column. To highlight all values above 10,000:

1. Select your sales column.
2. Go to Home > Conditional Formatting > Highlight Cell Rules > Greater Than.
3. Enter 10000.
4. Choose a formatting style (e.g., Green Fill).

Now, all sales above target stand out.

Example 2: Colour Scales for Heatmaps

Colour Scales apply a gradient, e.g.:

• Low values in red.
• Medium values in yellow.
• High values in green.

This is perfect for financial data or KPIs.

Example 3: Using Icon Sets

Icon Sets display symbols like arrows, flags, or traffic lights.

• Green arrow up → increasing value.
• Yellow sideways arrow → stable value.
• Red arrow down → decreasing value.

Creating Custom Rules

Beyond the built-in options, you can create your own rules:

1. Select your range.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter a formula, e.g.:

= $B2 > 10000


This highlights rows where column B is greater than 10,000.

Managing Rules

When you apply multiple rules, they may overlap.

• Go to Home > Conditional Formatting > Manage Rules.
• Change priority by moving rules up or down.
• Edit or delete old rules.

Advanced Conditional Formatting

Conditional Formatting can become very powerful when combined with formulas and functions.

Examples:

• Highlight duplicate values → Formula: =COUNTIF($A:$A,A2)>1
• Highlight weekends in a date list → Formula: =WEEKDAY(A2,2)>5
• Highlight overdue tasks → Formula: =AND($B2<TODAY(),$C2="Incomplete")

Common Problems and Fixes

• Rules not applying → Make sure the correct range is selected.
• Conflicting rules → Adjust order in “Manage Rules.”
• Too many formats → Limit complexity for large datasets to avoid slowing Excel down.

Best Practices

• Keep formatting simple and clear.
• Use consistent colours across sheets.
• Don’t overdo it — too many rules make sheets confusing.
• Document your rules if others will use your file.

Final Thoughts

Conditional Formatting is one of the most useful tools in Excel. It helps you instantly spot trends, outliers, and important information without reading every number.

👉 If you’re a beginner, learn the basics of Conditional Formatting in our Excel Introduction Course.
👉 If you want to customise and manage rules, explore the Excel Intermediate Course.
👉 For advanced formula-driven formatting, join the Excel Advanced Course.
👉 To take Excel even further with complex functions and data models, the Excel Power User Course is ideal.

With Conditional Formatting in your toolkit, you’ll be able to turn raw data into actionable insights.

‍

‍