How to Use Conditional Formatting in Excel (Master Every Option)

Conditional formatting in Excel lets you visually highlight data based on rules you set — like coloring top performers, flagging overdue dates, or tracking progress. It’s a must-know feature for turning data into insights fast.

In this tutorial, you’ll learn how to use every key part of Conditional Formatting — from basic highlights to custom formulas and dynamic visuals. By the end, you’ll be able to analyze and present your data like a pro.

Step-by-Step: Apply Conditional Formatting in Excel

1. Select the Range You Want to Format

Click and drag to highlight the cells (e.g., A2:A20 or a full table).
You can format rows, columns, or even entire sheets.

2. Go to the Conditional Formatting Menu

On the Home tab, click Conditional Formatting in the Styles group.

3. Choose a Formatting Rule Type

You’ll see several options:
- Highlight Cell Rules
- Top/Bottom Rules
- Data Bars
- Color Scales
- Icon Sets
- New Rule…

4. Apply a Highlight Cell Rule

Example: Highlight values greater than 100
- Choose Highlight Cell Rules > Greater Than
- Enter 100, pick a style (or create a custom one), then click OK.

5. Use Top/Bottom Rules for Quick Ranking

Example: Top 10 values
- Top/Bottom Rules > Top 10 Items
- Adjust the number or percent, and apply.

6. Add Visual Impact with Data Bars

Choose Data Bars to show progress-like visuals inside cells.
You can pick gradient or solid fills.

7. Use Color Scales for Ranges

Example: Heatmap of temperatures
- Color Scales > Green-Yellow-Red
- Excel shades cells based on value from low to high.

8. Add Icons for Instant Recognition

Choose Icon Sets (arrows, symbols, flags) to mark data status.
Great for KPIs, performance tracking, etc.

9. Create a Custom Rule with a Formula

Conditional Formatting > New Rule > Use a formula to determine which cells to format
Example: Format rows where the deadline is past: =A2<TODAY()
- Then apply red fill or bold text.

10. Manage and Edit Existing Rules

Go to Conditional Formatting > Manage Rules
You can edit, delete, or reorder rules here.
Set the range, stop rule evaluation, or change the priority.

Pro Tips & Workflow Improvements

✅ Use absolute and relative references correctly in formulas ($A$1 vs A1) to control how rules apply across ranges.
✅ Apply rules to entire rows by using formulas like =$B2="Completed" and selecting full rows before applying.
✅ Combine multiple rules for layered formatting (e.g., color scale + icon set).
✅ Use custom number formats in combination with formatting to hide zeroes or create visual patterns.
✅ Copy and paste formatting with Format Painter to quickly replicate your rules across sheets.

Advanced Use Case: Conditional Formatting Based on Another Sheet

Want to highlight items in Sheet1 that are also in Sheet2?

Select your range in Sheet1
Go to New Rule > Use a formula
Use a formula like: =COUNTIF(Sheet2!$A$1:$A$100, A1)>0
Apply formatting — Excel will highlight matching values across sheets.

Troubleshooting & Common Mistakes

❌ Rule not working? Check your formula references — they may need absolute $ signs or correct range logic.
❌ Rules overlap strangely? Use the Manage Rules menu to reorder or add “Stop If True” conditions.
❌ Performance issues on large sheets? Reduce the number of rules or use dynamic named ranges.
❌ Wrong format showing? Double-check custom formatting styles or try clearing all rules and reapplying.
❌ Formulas not updating? Hit F9 to refresh calculations if the formatting isn’t reacting.

Conclusion

Conditional Formatting in Excel turns raw data into instantly readable insights. You can highlight key trends, flag issues, and create interactive dashboards with just a few clicks or formulas.

Now that you’ve mastered the basics and beyond, try combining formatting with Excel Tables or PivotTables for even more powerful analysis.

Next up? Learn [How to Use Excel’s IF Statement with Conditional Formatting] for deeper logic-based formatting.