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
  • 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