Excel Conditional Formatting: Highlight Entire Rows, Multiple Conditions, and Weekends
Conditional formatting changes colors automatically based on cell values. This guide covers the basics, formulas that color entire rows, multiple conditions, and highlighting weekends.
2026-11-10
Key points
- 1Basics: Home → Conditional Formatting → pick a rule
- 2To color whole rows, lock only the column in the formula, as in
$C2 - 3Use
ANDandORfor multiple conditions
The basics
Example: color sales cells that are less than 100.
- 1
Select the cells to color
- 2
Click Conditional Formatting on the Home tab
On Windows you can also press Alt → H → L. - 3
Choose Highlight Cells Rules → Less Than
- 4
Enter 100, choose a format, and click OK
If you change the numbers later, the colors update automatically.
Common rules
| Goal | Menu |
|---|---|
| Cells greater or less than a value | Highlight Cells Rules → Greater Than / Less Than |
| Cells containing specific text | Highlight Cells Rules → Text that Contains |
| Duplicate values | Highlight Cells Rules → Duplicate Values |
| Top or bottom items | Top/Bottom Rules → Top 10 Items |
| Show size as a bar in the cell | Data Bars |
| Show size as color intensity | color scale |
Highlight an entire row
Color every row whose Status is Not done. It's a common need, but the menus can't do it, so use a formula.
| Project | Rep | Status |
| Company A | Smith | Done |
| Company B | Johnson | Not done |
| Company C | Williams | Done |
| Project | Rep | Status |
| Company A | Smith | Done |
| Company B | Johnson | Not done |
| Company C | Williams | Done |
- 1
Select the whole table except the headers
Example: A2:C4. Start from a cell in row 2. - 2
Choose Conditional Formatting → New Rule
On Windows: Alt → H → L → N. - 3
Choose "Use a formula to determine which cells to format"
- 4
Enter the formula
=$C2="Not done" - 5
Click Format → Fill, choose a color, and click OK
Where the $ goes
$C2 means lock column C, but let the row change. Every cell in the row checks column C, so the whole row is colored.
The 2 in the formula should match the first row of your selection.
Use multiple conditions
Use AND when all conditions must be true and OR when any one is enough. The steps are the same as for coloring whole rows.
| Goal | Formula |
|---|---|
| New York and 100,000 or more | =AND($B2="New York",$C2>=100000) |
| Not done or On hold | =OR($D2="Not done",$D2="On hold") |
For a different color per condition, create one rule for each.
Highlight weekends
This example has dates in column A. To give Saturday and Sunday different colors, create two rules.
| Target | Formula |
|---|---|
| Saturday and Sunday in one color | =WEEKDAY($A2,2)>5 |
| Saturday only | =WEEKDAY($A2)=7 |
| Sunday only | =WEEKDAY($A2)=1 |
Color overdue rows
This example has due dates in column D.
=AND($D2<>"",$D2<TODAY())
Blank cells count as earlier than any date, so $D2<>"" excludes them.
Edit or delete rules
| Action | Menu |
|---|---|
| Edit a rule | Conditional Formatting → Manage Rules (Alt → H → L → R) → Edit Rule |
| Change the priority | Use ▲ ▼ in Manage Rules. Rules higher up apply first |
| Stop lower rules | Check Stop If True in Manage Rules |
| Delete rules | Conditional Formatting → Clear Rules → from Selected Cells / Entire Sheet |
Note
If Manage Rules shows nothing, change "Show formatting rules for" to This Worksheet.
Troubleshooting
QOnly the first row gets colored.
The formula locks the row too, as in =$C$2. Clicking a cell in the dialog adds $ to both parts, so press F4 to change it to $C2.
QThe colors are off by one row.
The first row of your selection doesn't match the row number in the formula. If you selected from row 2, use $C2.
QNumbers aren't colored even though they meet the condition.
The numbers may be stored as text. Look for a green triangle in the top-left corner of the cell and convert them to numbers.
QCopying cells created extra rules.
Copying or inserting rows can split rules. Merge them into one in Manage Rules and set Applies to to the whole table.
- The formula's row number matches the first row of the selection
- Only the column has a $
- Text is wrapped in double quotes
Related guides and practice
Readable Spreadsheet Drill →
36 exercises that fix hard-to-read tables in a virtual Excel
Building a table →
8 steps from data entry to finishing touches
Merge Cells →
Merging and unmerging, and centering without merging
Custom Number Formats →
Thousands, units, red negatives, dates with weekdays
Hide Zeros →
Whole sheet, some cells, or a formula, and when to use each
Border →
Choosing lines, double lines under totals, removing stubborn lines