Jun 2, 2026

How to Use Conditional Formatting in Excel (2026)

Excel Conditional Formatting Spreadsheets How-to Productivity

Conditional formatting changes a cell’s look automatically based on its value — highlighting overdue dates, flagging duplicates, or shading a heat map. The rules live under one button on the Home tab. Here’s how to use each type.

1. Highlight Cells by Value

Select the range.
On the Home tab, click Conditional Formatting > Highlight Cells Rules.
Pick a condition — Greater Than, Less Than, Between, Equal To, Text that Contains, or A Date Occurring.
Enter the value and choose a fill color, then click OK.

Example: highlight every cell greater than 100 in red.

2. Top / Bottom Rules

To flag the standouts in a range:

Select the data, then Conditional Formatting > Top/Bottom Rules.
Choose Top 10 Items, Bottom 10%, Above Average, and so on (you can change the number).
Pick a format and click OK.

3. Data Bars, Color Scales, and Icon Sets

These visualize values without a single threshold:

Data Bars — an in-cell bar sized to the value.
Color Scales — a two- or three-color gradient (a heat map).
Icon Sets — arrows, traffic lights, or flags by tier.

Select the range, open Conditional Formatting, and pick one of these galleries.

4. Highlight Duplicates

Select the range.
Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Choose a format and click OK.

This only flags duplicates — to delete them, see how to remove duplicates in Excel.

5. Formula-Based Rules (Most Powerful)

To format a whole row based on one column’s value:

Select the range (e.g., A2:F100).
Conditional Formatting > New Rule > Use a formula to determine which cells to format.
Enter a formula that’s TRUE for rows to format, locking the column with $:

=$E2="Overdue"

Click Format, choose a fill, and click OK.

The $E2 (column locked, row relative) checks column E on every row and shades the entire row when it says “Overdue”.

6. Edit, Clear, or Reorder Rules

Manage: Conditional Formatting > Manage Rules to edit, delete, or change the order (top rules win) and toggle “Stop If True”.
Clear: Conditional Formatting > Clear Rules > from selected cells or the entire sheet.

7. Troubleshooting

The formatting didn’t apply

Confirm the rule’s Applies to range actually covers your cells (check it in Manage Rules).

A whole-row rule only colored one column

You didn’t lock the column. Use $E2 (dollar sign on the column letter only) so the rule reads column E for each row.

Rules conflict

Earlier rules in Manage Rules take priority. Reorder them, or tick Stop If True to prevent later rules from overriding.

Mac and Web

The same options live under Home > Conditional Formatting in Excel for Mac and Excel for the web, though the dialog layout differs slightly.

Ready to automate your busywork?

Carly schedules, researches, and briefs you—so you can focus on what matters.

Get Carly Today →

Or explore our free tools