Conditional Formatting in Excel: Rules and Examples

Conditional formatting in Excel automatically changes how cells look when their values meet conditions you define. Select the cells, then use Home > Conditional Formatting to choose a built-in rule or create a formula-based one; Excel updates the formatting as the data changes.

What conditional formatting means

A conditional formatting rule links a condition to a visual treatment, such as a fill color, font color, border, icon, or data bar. The condition might be a number above a threshold, a date before today, text containing a word, or a formula that evaluates to TRUE. The cell’s stored value is not changed: conditional formatting changes its appearance, not the underlying data.

This makes it useful for drawing attention to exceptions and patterns without manually coloring cells one by one. For example, a sales sheet could shade amounts below a target, a project tracker could flag overdue dates, and an inventory list could mark items with low stock. If a value changes so that it no longer meets the rule, Excel removes the conditional appearance automatically.

Rules belong to a worksheet and apply to a range of cells. A workbook can have many sheets, each with its own ranges and rules. Copying cells or a sheet may also copy formatting rules, so it is worth checking their ranges after moving or duplicating data.

How rules work in Excel

Excel evaluates a rule against the cells in its “Applies to” range. A built-in rule compares values with a condition you select. A formula rule evaluates an expression for each cell in that range. When the condition is true, Excel applies the rule’s formatting, subject to the rule order and any stop-processing setting.

Conditional formatting is a display layer. A cell can look red because a rule applies a red fill even though the cell’s ordinary fill setting is different. Clearing conditional formatting removes the rule-based appearance but does not delete the cell’s value. Conversely, changing a cell’s manual fill does not necessarily override a conditional format that still applies.

Rules can overlap. A cell might meet both a “below target” rule and a “duplicate value” rule, for instance. Excel uses the order shown in the Rules Manager to resolve conflicts in formatting, and some overlapping formats can be combined. The outcome depends on which formatting properties each rule changes, so rule priority matters when results seem inconsistent.

Built-in rule types and when to use them

In Excel for Microsoft 365 on Windows, the main options are under Home > Conditional Formatting. The built-in categories cover common comparisons and visual summaries:

• Highlight Cells Rules: highlight values greater than, less than, between, or equal to a value; text that contains a phrase; dates in a selected period; or duplicate and unique values.
• Top/Bottom Rules: show the highest or lowest values, percentages, or above- and below-average values in a range.
• Data Bars: place bars inside cells to compare relative magnitudes while retaining the numbers.
• Color Scales: shade cells across a gradient, such as green for higher values and red for lower values.
• Icon Sets: display symbols, such as arrows or traffic lights, according to thresholds.
• New Rule: create a custom condition, including a formula rule, and choose formatting that suits the data.

Use direct comparison rules when the condition is simple and easy to describe, such as “less than 10.” Data bars and color scales are useful for scanning a range, but they do not explain why a value is high or low. For operational lists, a clear fill or font change can be easier to interpret than a decorative icon.

Create a basic conditional formatting rule

For example, to highlight sales below a target, select the cells containing the sales figures. In Excel for Microsoft 365 on Windows, choose Home > Conditional Formatting > Highlight Cells Rules > Less Than. Enter the threshold, select a format, and choose OK. Excel applies the chosen appearance to cells whose values fall below that threshold.

To adjust or remove a rule, select a cell in the affected range and choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, inspect the rule, its order, and the range in “Applies to.” The “Show formatting rules for” menu lets you view rules for the current selection or a worksheet. Select a rule to edit or delete it. If the selected cell is outside the range, the rule may not appear in the view you expect.

On Excel for Mac, conditional formatting is also available from the Home tab, though the dialog layout can differ from Windows. In Excel for the web, use the Home tab’s Conditional Formatting controls; some advanced editing options may be presented differently or be unavailable compared with the desktop application. If a menu item is missing, check which Excel version and platform you are using.

Use formula rules for more control

A formula rule is useful when a built-in comparison cannot express the condition, or when the format of one cell should depend on another. Select the range to format, choose Home > Conditional Formatting > New Rule, and select the option to use a formula to determine which cells to format. Enter a formula that returns TRUE or FALSE, choose a format, and confirm. The formula should be written for the first cell in the selected range; Excel adjusts relative references for the other cells.

For an overdue-date example, assume due dates are in C2:C100 and you want to highlight the corresponding rows A2:D100. Select A2:D100, then create a formula rule using this formula. It checks whether the date in column C is earlier than today and ignores blank date cells:

=AND($C2<TODAY(),$C2<>