How to use conditional formatting in Excel

Last updated: · By the DeskHowTo editorial team
Quick answer

Select the cells, open Home > Conditional Formatting, and pick a rule (Highlight Cells, Top/Bottom, Color Scales, or New Rule).

To highlight an entire row based on one cell, use a formula rule with a mixed reference like $A2="Yes". Manage rules under Manage Rules.

On this page

Applies to Excel for Microsoft 365, Excel 2016+, Mac and web.

Apply a highlight rule

Highlight values greater than a number

  1. Select the numeric range.
  2. Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
  3. Enter the number and choose a format > OK.

Highlight duplicates

  1. Select the column or range.
  2. Highlight Cells Rules > Duplicate Values > OK.

Formula rule for whole rows

  1. Select the full data range starting at row 2.
  2. Conditional Formatting > New Rule > Use a formula to determine which cells to format.
  3. Enter =$C2="Overdue" (adjust column) and set a fill color > OK.

Edit or delete rules

Home > Conditional Formatting > Manage Rules. Set Show formatting rules for the sheet or selection, then Edit or Delete.

Performance tip

Limit the applies-to range to used rows on large sheets.

Frequently asked questions

Formula rule failed?

Usually a missing $ lock on the column, or the applies-to range starts on a different row than the formula assumes.

Copy formatting?

Use Format Painter, or Paste Special > Formats.

Works on Tables?

Yes - rules often auto-expand with the table.

Sources checked: Microsoft Support. Verified October 2026.