Conditional Formatting in Excel: How It Works

Conditional formatting in an Excel spreadsheet automatically changes a cell’s appearance when its value or a formula meets a condition. In Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting and select a rule type or create a custom formula rule.

What conditional formatting does

Conditional formatting makes patterns and exceptions easier to see without changing the underlying cell values. For example, a sales report can shade high figures green and low figures red; a task list can highlight overdue dates; and a stock sheet can mark items that need reordering. The formatting updates when the cells’ contents change and Excel recalculates the rules.

A rule usually specifies a condition and a format to apply when that condition is true. The format might change a cell’s fill color, font color, font style, border, or number format. Some rule types display visual indicators, such as color scales, data bars, and icon sets. The cell still contains its original value: a red fill does not turn a number into text or alter the result of a calculation.

Conditional formatting is useful for highlighting information, not for replacing clear labels or sound data design. A reader should still be able to understand the worksheet if the colors are hard to distinguish, printed in grayscale, or hidden by an accessibility setting.

How Excel evaluates formatting rules

Excel stores conditional formatting rules with the worksheet. A rule applies to a specified range, and Excel checks the rule against the cells in that range. When its condition is met, Excel displays the chosen format. If the cell value changes, the visible result can change too. This is why a rule can be more useful than manually coloring cells: the formatting can stay aligned with the data as it is updated.

Built-in rules cover common comparisons and visualizations. Highlight Cells Rules can flag values greater than a threshold, text containing a phrase, dates in a selected period, or duplicate values. Top/Bottom Rules can identify the highest or lowest values in a range. Data Bars, Color Scales, and Icon Sets show relative size or status at a glance.

Rules can overlap. A cell might meet both a “greater than 100” rule and a “duplicate values” rule, for example. Excel assigns rules an order of priority; the order and the option to stop processing other rules can affect the final appearance. For complex sheets, reviewing the full rule list is part of maintaining the workbook, not just an optional cleanup task.

Create a basic conditional formatting rule

To apply a built-in rule in Excel for Microsoft 365 on Windows:

1. Select the cells to format. For example, select B2:B50 if those cells contain order totals.
2. Go to Home > Conditional Formatting.
3. Choose a rule group, such as Highlight Cells Rules, then choose the condition you need, such as Greater Than.
4. Enter the threshold, select a format, and confirm the dialog.

For the example range B2:B50, a “Greater Than” rule with a threshold of 500 can highlight orders above 500. The rule applies to the selected cells; it does not add a new column or change the amounts. To change the threshold or appearance later, select a cell in the range and use Home > Conditional Formatting > Manage Rules.

To show a gradient across values, select the range and choose Home > Conditional Formatting > Color Scales. To show proportional bars, choose Data Bars. These options are most useful when comparing values on a consistent scale. If a few unusually large values dominate the range, smaller values may look nearly identical; a threshold rule or a more focused comparison may communicate the data better.

Use a formula for custom conditions

A formula rule can express conditions that the built-in menus do not cover, including checking one column while formatting another. Select the cells you want the format applied to, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula that evaluates to TRUE or FALSE, choose the format, and confirm.

For example, suppose due dates are in D2:D100 and you want to highlight dates that have passed while leaving blank cells unformatted. With D2:D100 as the selected range and D2 as its top-left cell, use =AND($D2<TODAY(),$D2<>