Excel Conditional Formatting for Cell Fill Color

To apply a conditional fill color in Excel for Microsoft 365 on Windows, select the cells, go to Home > Conditional Formatting, choose a rule type, and set the fill color in the rule’s Format options. Excel then applies that color automatically whenever the cell values meet the condition, and removes it when they no longer do.

What conditional fill color does

Conditional formatting changes how cells look based on rules; it does not change their contents. A cell might turn red when a balance is below zero, green when a task is marked Complete, or yellow when a date is approaching. If the value changes and no longer meets the rule, Excel stops showing the conditional fill.

This is different from choosing a permanent color with the Fill Color button on the Home tab. A manually applied fill stays until someone changes or clears it. A conditional fill is controlled by a rule, so the displayed color can change as data is edited, recalculated, or refreshed.

Conditional formatting can set more than a solid fill. Depending on the rule, it can also change font color, borders, icons, or data bars. A color scale uses a gradient to show relative values, while a standard rule can apply one chosen fill color to cells that meet a condition.

Apply a basic fill-color rule

For a quick rule based on a value, select the range and use one of Excel’s built-in conditions. For example, to highlight figures above a threshold, choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than. Enter the threshold, choose a preset format, or select Custom Format to choose a specific fill.

To control the fill and other formatting directly, create a rule with the New Rule dialog:

Select the cells you want Excel to format. For example, select B2:B100 to highlight values in that range.
Choose Home > Conditional Formatting > New Rule.
Choose a rule type, such as Format only cells that contain.
Set the condition, such as Cell Value greater than 100.
Select Format, open the Fill tab, choose a color, and select OK. Select OK again to create the rule.

In the dialog, the Fill tab controls the cell background. The Font tab controls text appearance, and the Border tab controls cell edges. Use a pale fill with dark text for dense tables so the values remain easy to read. If the built-in rule offers a preset such as Light Red Fill, that is a quick choice; Custom Format gives you more control.

Choose a rule that matches the data

Excel’s built-in rule families suit common comparisons, but the best choice depends on what the values mean. Home > Conditional Formatting includes Highlight Cells Rules for comparisons such as greater than, less than, equal to, text that contains, dates occurring, and duplicate values. Top/Bottom Rules can highlight high or low values, while Data Bars, Color Scales, and Icon Sets show patterns across a range.

Use a single fill rule when a clear threshold matters, such as marking inventory below a reorder point.
Use a color scale when you want to compare relative values across a range, such as low, middle, and high scores. Excel shades cells along a gradient based on the selected scale.
Use data bars or icons when color alone would not show the relative size or status clearly. These options can complement values rather than replacing them.
Use text or date rules for labels such as Overdue or dates in the next month. Check that the cells contain actual dates, not date-looking text.

Color scales are not the same as a rule that assigns a specific status color. A gradient is useful for spotting relative variation, but it may not communicate a fixed business threshold. If a value of 10 must always be red, use a rule tied to that threshold rather than relying on the range’s relative color scale.

Use a formula for more flexible conditions

A formula rule can test a condition that the preset options cannot express, or apply a color to an entire row based on one column. Select the full area to format, choose Home > Conditional Formatting > New Rule, and select Use a formula to determine which cells to format. Enter a formula that evaluates to TRUE or FALSE, then choose the fill on the Format dialog’s Fill tab.

For example, with values in B2:B100, select B2:B100 and use =B2>100 to format values greater than 100. The reference changes by row as Excel evaluates the rule across the selected range. If B2 is 125, the condition is TRUE for that cell and the chosen fill appears.

To color whole rows based on a status column, suppose the table occupies A2:D100 and the status is in column C. Select A2:D100, then use =$C2="Late" as the formula. The dollar sign fixes the column at C, while the row number remains relative, so each row’s formatting depends on its own status cell. If C7 contains Late, the rule colors A7:D7.

Formula rules often fail because the reference is anchored incorrectly or the formula was written for a different starting cell. In the row example, the selected range starts at row 2, so the formula refers to row 2. If the selected range instead starts at row 5, adjust the formula’s row reference to 5. Also check that text conditions use quotation marks and match the cell text exactly, including spaces.

Manage rule order and the Applies to range

When multiple conditional-formatting rules apply to the same cells, the result depends on rule priority and the formats each rule sets. To inspect or change rules, choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, use the drop-down to view rules for the current selection or the worksheet, then check the order and the Applies to range.

Rules higher in the list have higher priority when rules conflict. The Stop If True option can prevent lower-priority rules from being evaluated after a rule is true; it is available for applicable rule types in the Rules Manager. Use it deliberately. For instance, a high-priority exception rule may need to stop a general rule from adding a second format. If rules set different properties, such as one setting a fill and another setting a font, both effects may be visible.

The Applies to field is especially important when a rule works in some cells but not others. It specifies the range controlled by that rule. A formula rule created with only one cell selected may apply to just that cell; edit Applies to if the rule should cover a larger range. When adding rows, verify that the range expands as intended rather than assuming every rule will automatically include new data.

Common problems and practical fixes

The fill does not appear: In Manage Rules, confirm that the condition is true for the cell and that Applies to includes it. For formula rules, check the starting row, dollar signs, and text spelling. A value stored as text may not behave like a number in a numeric comparison.
The wrong color appears: Look for another rule with higher priority, a conflicting rule, or an unintended range. Change the order or revise the condition rather than layering extra rules without checking them.
A manually chosen fill seems missing: A conditional format can visually override the ordinary cell fill while its condition is true. Change or remove the rule if you want the permanent fill to show instead.
The rule stops at the old end of the data: Expand Applies to in Manage Rules, or create the rule after selecting the intended range. If the list is an Excel table, test how the rule behaves when a new table row is added.
Colors are hard to distinguish: Choose a readable fill and font combination, and do not rely on color as the only way to communicate an important status. A label, icon, or clear threshold can help people using printed sheets or different displays.

For a rule that should mark overdue dates, check that the dates are real Excel date values and that the comparison reflects the intended boundary. A date displayed as 11/02/2026 might be stored as text, in which case a date-based comparison may not work as expected. Changing the number format alone changes how a value is displayed; it does not necessarily convert text into a date value.

Copy, edit, or remove conditional formatting

To reuse formatting, copy a cell with the rule and use Paste Special > Formats on the destination, or use Format Painter on the Home tab. After copying, inspect Manage Rules because relative references and Applies to ranges can shift depending on where the format is pasted. A formula written for one range may need adjustment for another.

To edit an existing rule, select a cell it affects and choose Home > Conditional Formatting > Manage Rules. Select the rule, choose Edit Rule, update the condition or format, and confirm. To remove rules, use Home > Conditional Formatting > Clear Rules, then choose whether to clear rules from the selected cells or the entire worksheet. Clear Rules removes conditional formatting; it is not the same as changing a cell’s ordinary fill color.

Excel for Mac and Excel for the web

In current Excel for Mac, the Conditional Formatting commands are also on the Home tab, and the basic process—select cells, create a rule, then choose a format—works much like it does on Windows. Dialog wording or layout can vary slightly by release. Excel for the web also supports common conditional-formatting rules, though some advanced editing options and dialog details may differ from desktop Excel. If you cannot find a particular rule-management option in the web version, open the workbook in the desktop app and check there.

Frequently Asked Questions

Can conditional formatting change only the fill color?
Yes. In the rule’s Format dialog, set a fill on the Fill tab and leave the font and border unchanged. The rule can still affect only the cell background.

How do I make a cell change color based on another cell?
Select the cells to color, create a formula-based rule, and refer to the other cell in the formula. For example, a row rule can use a fixed column and a relative row reference so each row checks its own status.

Can I use more than one conditional fill color?
Yes. Add separate rules for separate conditions, such as red for values below a limit and green for values above it. Check rule order and overlapping conditions in Manage Rules.

Why does the color disappear after I change a value?
That is normally expected: conditional formatting is dynamic. When the value no longer meets the rule, Excel stops displaying that rule’s fill. Use ordinary Fill Color instead if you want a permanent color.

How do I remove a conditional fill without clearing the cell’s value?
Use Home > Conditional Formatting > Clear Rules and choose the selected cells or worksheet. This clears the rules, not the values. If other rules apply to those cells, inspect Manage Rules to remove only the intended rule.

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.