Excel

Conditional Formatting

A feature that automatically applies formatting (colors, icons, data bars) to cells based on their values or specified conditions, making patterns and outliers visually apparent.

Examples

Highlighting cells with values above average in green
Adding red background to overdue dates
Showing progress bars for percentage completion

Conditional Formatting

Conditional Formatting automatically changes cell appearance based on the cell's value or a formula you specify. It's essential for making data visually meaningful at a glance.

Why Use Conditional Formatting

  • Instantly spot trends, patterns, and outliers
  • Highlight important data without manual formatting
  • Create visual dashboards
  • Make data self-explanatory

Types of Conditional Formatting

1. Highlight Cells Rules

Format cells based on their value:

  • Greater than / Less than
  • Between values
  • Equal to
  • Text that contains
  • Dates (today, last week, etc.)
  • Duplicate values

2. Top/Bottom Rules

Highlight based on ranking:

  • Top 10 items
  • Top 10%
  • Bottom 10 items
  • Above/Below average

3. Data Bars

Show values as horizontal bars within cells, like inline bar charts.

4. Color Scales

Apply gradient colors based on value—great for heat maps.

  • 2-color scale (low to high)
  • 3-color scale (low, mid, high)

5. Icon Sets

Display icons based on value thresholds:

  • Traffic lights (red, yellow, green)
  • Arrows (up, sideways, down)
  • Stars, checkmarks, flags

Creating Conditional Formatting

Excel

  1. Select your range
  2. Home → Conditional Formatting
  3. Choose rule type
  4. Set conditions and formatting
  5. Click OK

Google Sheets

  1. Select your range
  2. Format → Conditional formatting
  3. Set up rule in the panel
  4. Click Done

Using Custom Formulas

The most powerful option—any formula returning TRUE applies the format.

Examples

Highlight entire row if column A is "Urgent":

=$A1="Urgent"

Highlight weekends:

=OR(WEEKDAY(A1)=1, WEEKDAY(A1)=7)

Alternating row colors:

=MOD(ROW(),2)=0

Above average values:

=A1>AVERAGE($A:$A)

Highlight duplicates:

=COUNTIF($A:$A, A1)>1

Managing Rules

Priority Order

Rules are applied in order. Earlier rules take precedence.

Stop If True

In Excel, check "Stop If True" to prevent later rules from applying.

Clear Rules

Home → Conditional Formatting → Clear Rules

Best Practices

  1. Less is more: Don't over-format—it becomes noise
  2. Be consistent: Use the same colors for the same meanings
  3. Test with data: Verify rules work with edge cases
  4. Document: Note what colors mean if not obvious
  5. Performance: Too many rules on large ranges can slow the file

Common Use Cases

  1. Sales Dashboards: Color-code performance metrics
  2. Project Tracking: Highlight overdue tasks in red
  3. Data Validation: Flag invalid entries
  4. Heat Maps: Visualize data density or intensity
  5. Progress Tracking: Show completion with data bars