Conditional Formatting

Conditional formatting adds visual cues through colors, bars, icons, and heatmaps based on data values. For example, you can create a heatmap showing sales performance by region and month, highlight inventory items below reorder points, or mark past-due tasks.

To create one, highlight the data that you want to be formatted, the in the top bar, to go Conditional Formatting, then select the format you want.

Basic Rules

Greater Than/Less Than

Highlights cells that meet value conditions: - Select range and choose Home > Conditional Formatting - Pick "Greater Than" or "Less Than" - Enter threshold and format

Color Scales

Creates heatmaps using color gradients: - 2-Color Scale: Shows high/low values (e.g., Red-Green) - 3-Color Scale: Adds midpoint color (e.g., Red-Yellow-Green)

Data Bars

Adds bars proportional to cell values: - Solid or gradient fill options - Length indicates relative value - Works best with positive numbers

Icon Sets

Adds icons based on thresholds: - Arrows, flags, ratings - Set breakpoints (e.g., 67%, 33%) - Icons update automatically

Custom Rules

Create rules with formulas:

=AND($B2>1000, $C2="Active")
=OR($D2="High", $E2>90)

Common custom rules: - Date conditions (overdue, upcoming) - Text matches (contains, starts with) - Multiple criteria (AND/OR)

Managing Rules

Access Rule Manager: Home > Conditional Formatting > Manage Rules

Features: - Edit/delete rules - Change priority order - Copy rules between sheets - Set rule scope

Tips

1. Start with built-in rules
2. Use consistent colors
3. Limit to 3-4 rules per range
4. Test with sample data
5. Document complex formulas

Examples

Sales Dashboard

=B2>AVERAGE($B$2:$B$100)    # Above average in green
=B2<AVERAGE($B$2:$B$100)*0.8 # Below 80% in red

Project Status

=TODAY()-$A2>30     # Over 30 days old
=AND($B2="High",$C2="No") # High priority, not started