Conditional Formatting in Excel

Learn Conditional Formatting in Excel from beginner to advanced level with real-life examples. Highlight data automatically using rules, formulas, and visuals.

Conditional Formatting in Excel allows you to automatically apply formatting—such as colors, icons, or bold text—based on the values in your spreadsheet. Whether you’re tracking sales, highlighting top customers, or flagging low inventory, this tool helps visualize your data clearly.

We’ll use the following Sales Data Table as our reference for examples:

Bill No.Customer NameLocationProductQtySales Amount
INV0030Yuvraj NairKorbaSuit613991
INV0033Yuvraj NairRajnandgaonT-Shirt964800
INV0081Yuvraj NairGariabandT-Shirt328467
INV0020Vivek MalhotraBalodSuit641580
INV0058Vivaan SharmaSargujaKurta446670

Basic Conditional Formatting Examples

1. Highlight Sales Greater Than ₹40,000

Highlight Cells Greater than - Conditional Formatting in Excel
Step 1: Home > Conditional Formatting > Highlight Cells Rules > Greater Than > 40000
Highlight Cells
Step 2: Type 40000, then choose fill color, then OK

Highlight all sales where the Sales Amount > 40000.

  • Select the “Sales Amount” column.
  • Go to Home > Conditional Formatting > Highlight Cell Rules > Greater Than.
  • Enter 40000 and choose a highlight color (e.g., light green).

2. Highlight Duplicate Customer Names

Highlight Duplicate Values
Step 1: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values
Duplicate Values - Conditional Formatting
Step 2: Click OK
  • Select the “Customer Name” column.
  • Go to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values.

3. Color Scale Based on Qty

Color Scales Conditional Formatting
Home > Conditional Formatting > Color Scales > Green-Yellow-Red.

Visualize purchase volume with a color gradient.

  • Select the “Qty” column.
  • Go to Home > Conditional Formatting > Color Scales > Green-Yellow-Red.

Intermediate Examples

4. Highlight Top 3 Highest Sales

Top Bottom Rules in Conditional Formatting
  • Select the “Sales Amount” column.
  • Go to Conditional Formatting > Top/Bottom Rules > Top 10 Items.
  • Change “10” to “3” and apply bold red fill.

5. Highlight Customers with Hoodie Orders

Text That Contains in Conditional Formatting
Home > Conditional Formatting > Highlight Cell Rules > Text that Contains > Type “Hoodie”

Use a text match to highlight Hoodie product rows.

  • Select the “Product” column.
  • Go to Conditional Formatting > Highlight Cell Rules > Text that Contains > Type “Hoodie”

6. Highlight Sales from Gariaband

  • Select the “Location” column.
  • Use “Text that Contains” with value “Gariaband”

Advanced Examples

7. Highlight Rows Where Qty > 5 and Sales > ₹30000

Use a formula to apply formatting across the row.

  • Select entire data range (e.g., A2:F113)
  • Go to Conditional Formatting > New Rule > “Use a formula”

=AND($E2>5, $F2>30000)

Also Read: How to use AND & OR in Excel

1. New Rule Conditional Formatting Conditional Formatting
Step 1: Home > Conditional Formatting > Click on New Rule
2. New Formatting Rule Conditional Formatting
Step 2: Select “Use a formula to determine which cells to format” > type formula > click on “Format”
3. New Rule Formatting Output Conditional Formatting
Step 3: Click on “Fill” > select Color > click on “OK”, [Right side is your Output]

8. Color Sales Over Average Using Formula

=F2>AVERAGE($F$2:$F$20)

Highlights sales amounts above the average.

9. Highlight All Hoodie Orders with Qty < 5

=AND($D2=”Hoodie”, $E2<5)

10. Highlight Sales from Specific Customers

=OR($B2=”Yuvraj Nair”, $B2=”Tanmay Dubey”)


Real-Life Use Cases from the Table

  • Highlight all Hoodie orders with red if Qty < 5
  • Use a color scale to show which cities have highest total sales
  • Apply icon sets to the “Sales Amount” column
  • Flag duplicate orders using “Bill No.”
  • Identify VIP customers (Sales > ₹50,000)

Clear Rules

Clear Rules Conditional Formatting Conditional Formatting

To remove conditional formatting:

  1. Select the data range or sheet.
  2. Go to Home > Conditional Formatting > Clear Rules.
  3. Choose from:
    • “Clear Rules from Selected Cells”
    • “Clear Rules from Entire Sheet”

This helps when rules overlap or no longer apply.


Manage Rules

You can view, edit, or delete rules:

  1. Go to Home > Conditional Formatting > Manage Rules.
  2. The Conditional Formatting Rules Manager will appear.
  3. You can:
    • View all rules in the current worksheet or selection
    • Change the formula or formatting
    • Delete or reorder rules

Managing rules is essential for maintaining complex formatting.

Manage Rules Conditional Formatting Conditional Formatting
Step 1: Home > Conditional Formatting > Manage Rules
Conditional Formating Rules Manager Conditional Formatting
Step 2: & Step 3

Best Practices

  • Use relative vs. absolute references correctly
  • Keep rules organized using Manage Rules
  • Avoid too many formatting rules—they can slow performance
  • Use named ranges for dynamic tables

Download Practice Material


Summary

Conditional Formatting is a smart way to highlight trends, patterns, and outliers in your Excel data. With the right formulas, you can make even complex logic easy to visualize.

FAQs – Conditional Formatting in Excel

Yes, use Manage Rules to adjust priority.

Yes, most rules are supported but formula-based ones may be limited.

Yes, if applied on a Table or dynamic range.

What’s Next?

In the next post, we’ll learn about the Data Validation in Excel

Spread the love

Leave a Comment

Your email address will not be published. Required fields are marked *

Translate »
Scroll to Top