⚡ What You'll Learn
Conditional formatting is one of Excel's most powerful visual features. It lets you set rules that automatically change the appearance of cells — background color, font color, borders, icons — based on the data those cells contain. Instead of manually scanning rows of numbers, you can see at a glance which values are high, low, overdue, or outside expected ranges. In calendar templates, conditional formatting is what makes weekends appear gray, holidays appear gold, and today's date appear highlighted.
Step 1 — Apply a Basic Highlight Rule
Highlight Cells Based on Value
Select the range you want to format (e.g., a column of sales numbers). Then open the built-in rules:
Enter a threshold (e.g., 500), choose a color preset like "Green Fill with Dark Green Text," and click OK. Every cell exceeding 500 is instantly highlighted.
| A | B | C | |
|---|---|---|---|
| 1 | Salesperson | Q1 Sales | Status |
| 2 | Alice | 1,240 | On target |
| 3 | Bob | 320 | Below target |
| 4 | Carol | 890 | On target |
| 5 | Dan | 510 | Borderline |
Step 2 — Use Color Scales for Heat Maps
Visualize Data with Gradient Colors
Color scales assign a gradient from low to high values, turning any data range into a visual heat map. Select your range, then:
Low values appear red, mid-range values yellow, and high values green (or reverse it). This is ideal for spotting trends in budget reports, attendance sheets, or performance dashboards without reading every number.
Step 3 — Add Data Bars & Icon Sets
In-Cell Charts and Status Indicators
Data Bars insert tiny horizontal bar charts directly inside each cell — longer bars mean higher values. Icon Sets place small symbols (traffic lights, arrows, stars, flags) based on value thresholds.
By default, Excel splits values into thirds for icon sets: top third gets green, middle gets yellow, bottom gets red. You can customize thresholds by editing the rule.
Step 4 — Write a Custom Formula Rule
Format Based on Complex Logic
For scenarios where built-in rules aren't flexible enough, create a formula-based rule. For example, to highlight an entire row where column C says "Overdue":
Select your entire data range (e.g., A2:D50), then enter:
The \$C locks the column to C while the row number stays relative, so Excel checks each row individually. Set the format to a red fill and click OK.
=WEEKDAY(A1,2)>5 to identify Saturday and Sunday cells, then apply a light gray fill to automatically shade all weekend dates across your calendar grid.C2 instead of $C2. Without the dollar sign on the column, Excel shifts the column reference for each cell and checks the wrong data. Always use absolute column + relative row (\$C2) for row-based highlighting.Step 5 — Highlight Duplicates Instantly
Find Repeated Entries in One Click
Select a column (e.g., email addresses or invoice numbers), then:
Choose to highlight "Duplicate" or "Unique" values. This is invaluable for data cleanup — spotting duplicate entries, repeated names, or redundant records before they cause problems in reports or mailing lists.
Step 6 — Manage, Edit & Prioritize Rules
Control Multiple Rules on the Same Range
When you have several rules, manage them in one place:
The Rules Manager shows every rule for the current selection or entire sheet. You can edit conditions, change format styles, reorder priority (rules higher in the list take precedence), and delete unused rules.