Conditional Formatting Color in Excel: How It Works

In Excel, conditional formatting color is a fill color, font color, or other visual style that appears automatically when a cell meets a rule. To apply one, select cells, choose Home > Conditional Formatting, select a rule type, set its condition, and choose the format color.

What conditional formatting color means

Conditional formatting changes how Excel displays a cell when its value or contents match a condition. For example, you can make overdue dates red, highlight sales above a target in green, or shade a range of numbers according to their relative size. The cell’s underlying value does not change: a date remains a date and a number remains a number, even when its appearance changes.

The color is part of a formatting rule, not a permanent property assigned by typing a color into the cell. If a value changes and no longer meets the rule, Excel removes that rule’s formatting and displays the cell’s ordinary formatting again. If another active rule applies, its formatting may appear instead.

This makes conditional formatting useful for scanning lists and spotting exceptions without sorting or changing the data. A colored cell is a visual signal, however, not proof of why the condition occurred. Keep the rule understandable and make sure the data itself is reliable.

How color rules work

A rule defines the cells it applies to, the condition that must be met, and the format Excel should display when it is met. A rule might test whether a value is greater than 100, whether text contains the word Pending, or whether a date is earlier than today. Excel evaluates the condition against the relevant cells and updates the formatting when values change or the workbook recalculates.

Some rules compare a cell with a fixed value, while others compare values across a range. A color scale, for example, assigns different shades based on the distribution of numbers in the selected cells. A formula-based rule lets you describe a condition Excel’s built-in options do not cover, such as highlighting an entire row when one column contains a particular status.

Conditional formatting is dynamic, but it is not a data-cleaning tool. It does not correct misspelled labels, convert text that looks like a number into a number, or establish that a threshold is appropriate. It only applies the visual instruction you give it.

Apply a color using a built-in rule

For a simple condition, a built-in rule is usually the quickest option in Excel for Microsoft 365 on Windows. For example, to color values below 50 in a selected range:

Select the cells you want to format, such as B2:B20.
Choose Home > Conditional Formatting > Highlight Cells Rules > Less Than.
Enter 50 in the value box.
Choose one of the suggested formats, or select Custom Format to choose a fill color, font color, or other available formatting.
Select OK and check a few cells, including values on both sides of the threshold.

The menu also includes rules for greater-than or equal-to comparisons, text that contains a specified string, dates in a selected period, duplicate values, and other common checks. The exact choices shown depend on the rule type. For instance, a color scale asks you to choose a scale, while a text rule asks for text to match.

In recent Excel for Mac desktop versions, the conditional-formatting options are available from the Home tab as well, though dialog layouts can differ. In Excel for the web, the options are also available from Home > Conditional Formatting, but some advanced rule controls may differ from the Windows desktop application.

Common types of color formatting

Excel offers several approaches because different data calls for different visual cues:

Single-color rules: Apply one chosen format when a condition is true, such as a red fill for values below a target.
Color scales: Use a gradient or a set of colors to show relative low, middle, and high values across a range. These are useful for patterns, but the color for a number can depend on the other numbers in the selected range.
Data bars: Add an in-cell bar whose length represents the value relative to other values. The number remains in the cell.
Icon sets: Display symbols such as arrows or traffic-light icons to group values into bands. These are not simply fill colors, and their thresholds can be adjusted.
Formula-based rules: Apply a format when a formula evaluates to TRUE, which supports conditions involving multiple cells.

A color scale is not the same as marking values above a fixed goal. If a range contains only low sales numbers, its highest value can still receive the high-end color. Use a fixed-threshold rule when the meaning depends on an absolute target, and use a scale when the goal is to compare values within the current range.

Use a formula for a more specific condition

A formula rule is useful when the condition depends on another cell or when you want to format a complete row. Suppose sales are in B2:B20 and a comparison threshold is in D1. Select B2:B20, choose Home > Conditional Formatting > New Rule, and select Use a formula to determine which cells to format. With values in B2:B20 and the threshold in D1, enter =B2<$D$1. Choose a format color and confirm the rule.

If D1 contains 50, a value of 42 in B2 meets the condition and receives the chosen format; a value of 60 does not. The formula begins with an equals sign, as Excel formulas do. B2 is a relative reference, so Excel evaluates each cell in the selected range against the fixed threshold in D1. The dollar signs keep that threshold reference fixed as the rule is applied down the range.

To color whole rows based on a status column, select the rows you want covered and create a formula rule that refers to the status cell in the first selected row. For example, if the status is in column C and the first selected row is row 2, the condition can test C2 while locking the column as needed. The exact formula depends on the range and the text you want to match, so check that the row number corresponds to the first row in Applies to.

Choose colors that communicate clearly

Use colors consistently so readers can interpret them without guessing. A common convention is red for an exception or warning and green for a favorable result, but color meanings vary between organizations and contexts. Add a legend or a clear column heading when the intended meaning is not obvious. For a report shared with others, explain whether a color indicates a fixed threshold, a comparison within the displayed range, or a manually defined category.

Do not rely on color alone to convey essential information. Some readers have color-vision differences, and a workbook may be printed in grayscale or viewed on a display with different settings. Pair color with text, symbols, or a separate status field where practical. Choose sufficient contrast between text and fill, especially for small text, and avoid using many similar shades that are hard to distinguish.

Keep the number of colors manageable. A red, amber, and green scheme can work for a small number of meaningful categories, but a rainbow of fills can make a data table harder to read. If the exact value matters, preserve it visibly and treat the color as a prompt to investigate rather than as a substitute for the number.

Rule order and overlapping formats

A cell can meet more than one conditional-formatting rule. Excel manages rules in a priority order, which can affect the result when rules specify conflicting formatting. For example, one rule might set a red fill for a low value while another sets a yellow fill for all values in a selected category. If both apply, the displayed result depends on the rules and the formatting properties they set.

To inspect or change rules, choose Home > Conditional Formatting > Manage Rules. In the rules manager, use the Show formatting rules for list to choose whether to inspect the current selection, the worksheet, or another scope. You can edit a rule, change its Applies to range, or adjust its priority. The Stop If True option prevents lower-priority rules from being evaluated for a cell when the rule above it is true; use it only when that behavior is intended.

When the result looks unexpected, check the rules manager before creating another rule. A second rule can make a workbook more confusing rather than fixing the first one. Also check whether the rules apply to the cells you expect and whether a broad rule overlaps a more specific range.

Common problems and how to fix them

The wrong cells change color: Open Manage Rules and review Applies to. A rule may cover a larger or smaller range than intended.
A value that looks below a limit is not highlighted: Check whether the cell contains a true number or text that only looks like a number. Also verify that the rule uses the intended comparison, such as Less Than rather than Less Than or Equal To.
The fill seems to disappear: The value may no longer meet the condition, or another rule may be overriding the format. Inspect the rule order and any overlapping rules.
A formula rule highlights the wrong rows: Check the formula references and the first row of the Applies to range. Relative and absolute references determine which cells Excel tests as it applies the rule.
The color scale changes after editing data: This is expected when the scale is based on the minimum, midpoint, and maximum in the range. Adding or removing values can change the relative colors.

To remove a rule without deleting cell values, select the affected cells and choose Home > Conditional Formatting > Clear Rules. Excel provides choices to clear rules from the selected cells or from the entire worksheet. Use the selection option carefully: it removes conditional-formatting rules for that selection, not just the particular color you noticed. If you want to keep a rule but change its color, edit the rule instead.

Frequently Asked Questions

Does conditional formatting change the cell value?
No. It changes the display format when a rule is satisfied. A number, date, or text entry remains the same unless you edit the cell or use a separate formula or operation.

Can I choose my own conditional formatting color?
Yes. When creating or editing a rule, use the format options to select a custom fill or font color. The available controls depend on the rule type; color scales and icon sets use their own formatting choices.

Why does my conditional formatting color change when I add data?
A color scale usually reflects values relative to its selected range. New numbers can shift the range’s minimum, midpoint, or maximum, changing how existing values appear. Use fixed-value rules if the color should represent an absolute threshold.

Can a conditional-formatting rule color an entire row?
Yes. Select the row range and create a formula-based rule that checks the relevant cell in each row. Set the formula references and Applies to range so the condition moves by row while staying tied to the correct column.

How do I remove conditional formatting but keep the data?
Select the cells, then choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This removes the applicable conditional rules while leaving the cell contents in place.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.