Excel Conditional Formatting

Conditional formatting automatically changes a cell's appearance based on its value, making patterns like overdue dates or low stock visible at a glance without manual highlighting.

Formatting That Reacts to Your Data

Unlike regular formatting, which you set once and it stays fixed, conditional formatting re-evaluates every affected cell whenever the worksheet changes. If a value crosses a threshold you defined, its formatting updates immediately, whether the change came from typing a new number or from a formula recalculating.

Built-in Rule Types

  • Highlight Cells Rules: format cells that are greater than, less than, between, equal to, or contain specific text.
  • Top/Bottom Rules: highlight the top 10 items, bottom 10%, or above/below average values in a range.
  • Data Bars: fill each cell with a proportional bar so relative size is visible instantly.
  • Color Scales: shade cells on a gradient, such as red for low values through green for high values.
  • Icon Sets: place a small icon, like an arrow or traffic light, next to each value based on its rank.

Writing a Custom Rule with a Formula

For conditions the built-in rules cannot express, choose "Use a formula to determine which cells to format." The formula you enter must evaluate to TRUE or FALSE, and it is written relative to the top-left cell of the range you selected before opening the dialog.

Example

=$B2<10
Note: When the selected range spans multiple columns, lock the column with $ (as in $B2) but leave the row relative, so the rule checks column B's own value on every row while still applying across all the selected columns.

Managing Rule Order and Priority

The Conditional Formatting Rules Manager lists every rule applied to a sheet and lets you edit, delete, or reorder them. Rules are evaluated from top to bottom, and by default a cell can display formatting from more than one matching rule; checking "Stop If True" on a rule prevents any rules below it from also applying to the same cell.

Note: When two rules can both match the same cell, such as one highlighting values over 100 and another highlighting values over 200, check their order and Stop If True settings, otherwise the formatting you see may come from a rule you did not expect.

Exercise: Excel Formatting

What does Conditional Formatting do?

Keep Going

Excel Interview QuestionsPractice real questions asked for ExcelData Analyst SalarySee what Data Analyst roles pay in the marketAuto Apply for JobsApply to open roles related to Excel and tweak your resume