MS Excel - Conditional Formatting

In Microsoft Excel, Conditional Formatting allows you to automatically format cells based on specific rules, conditions, or values. It’s a powerful tool for highlighting important data, visualizing trends, and making spreadsheets easier to analyze.

1. What Is Conditional Formatting?

Conditional Formatting changes the appearance of cells — such as font color, background color, data bars, color scales, or icons — based on certain conditions you set.

Examples:

  • Highlight cells greater than 100 in green.

  • Shade duplicate entries in red.

  • Show progress using data bars.

  • Color-code sales figures with a 3-color scale.

2. Where to Find Conditional Formatting

Ribbon Path:
Home → Styles Group → Conditional Formatting

From here, you can choose:

  • Highlight Cell Rules

  • Top/Bottom Rules

  • Data Bars

  • Color Scales

  • Icon Sets

  • New Rule (custom rules)

  • Manage Rules (edit or delete)

3. Types of Conditional Formatting

A. Highlight Cell Rules

Used to highlight cells based on a specific value or text condition.

Steps:

  1. Select your data range.

  2. Go to Home → Conditional Formatting → Highlight Cell Rules.

  3. Choose one of the options:

    • Greater Than… → e.g., highlight sales > 5000.

    • Less Than… → e.g., highlight marks < 40.

    • Between… → e.g., highlight ages between 20 and 30.

    • Equal To… → highlight matching values.

    • Text That Contains… → e.g., highlight cells with “Pending.”

    • A Date Occurring… → highlight today’s or last month’s dates.

    • Duplicate Values → mark repeated entries.

B. Top/Bottom Rules

Highlights extreme values in a dataset.

Options:

  • Top 10 Items → Highlight the top 10 numbers.

  • Top 10% → Highlight top-performing data points.

  • Bottom 10 Items → Highlight the lowest performers.

  • Bottom 10% → Spot low-performing entries.

  • Above Average → Highlight all values above the average.

  • Below Average → Highlight all values below the average.

C. Data Bars

Data bars visually represent cell values as horizontal bars.

  • Longer bars = higher values.

  • Found in Home → Conditional Formatting → Data Bars.

  • You can choose gradient fill or solid fill for better visuals.

D. Color Scales

Color scales shade cells based on their relative values.

  • Available under Conditional Formatting → Color Scales.

  • Common examples:

    • Green-Yellow-Red → Higher values = Green, Lower = Red.

    • Red-Yellow-Green → Reverse shading.

  • Great for heatmaps and performance analysis.

E. Icon Sets

Adds icons based on values, making data visually intuitive.

  • Found in Home → Conditional Formatting → Icon Sets.

  • Options include:

    • Directional Arrows → For increasing/decreasing trends.

    • Shapes → Circles, squares, and flags.

    • Ratings → Stars or traffic lights.

  • Useful for dashboards and reports.

F. Custom Conditional Formatting Rules

For more complex conditions:

  1. Go to Home → Conditional Formatting → New Rule.

  2. Choose:

    • Use a formula to determine which cells to format.

  3. Enter a formula, e.g.:

    =B2>5000

    This highlights cells in column B greater than 5000.

4. Manage and Clear Conditional Formatting

  • Manage Rules:
    Go to Home → Conditional Formatting → Manage Rules to edit or delete rules.

  • Clear Rules:
    Home → Conditional Formatting → Clear Rules → From Selected Cells or Entire Sheet.

5. Tips for Effective Conditional Formatting

  • Use Relative and Absolute References:

    • Relative → =A2>50 (applies differently per row)

    • Absolute → =$A$2>50 (fixes the reference).

  • Combine Multiple Rules:
    For example, color salaries above 50,000 green and below 20,000 red.

  • Limit Overuse:
    Too many rules can slow down Excel.

  • Use with Formulas:
    Examples:

    • Highlight weekends: =OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7)

    • Highlight blank cells: =ISBLANK(A2)

    • Highlight duplicates: =COUNTIF($A$2:$A$100,A2)>1

6. Best Use Cases

  • Highlight overdue tasks in project trackers.

  • Visualize top performers in sales dashboards.

  • Create heatmaps for KPIs.

  • Mark duplicates or errors in large datasets.

  • Track financial trends with color-coded reports.