Conditional Formatting to Change Cell Color in Excel

In Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting and select a rule, such as Highlight Cells Rules > Greater Than. Set the condition and choose a fill color; Excel will apply that color automatically whenever the condition is true.

What conditional formatting does

Conditional formatting changes how a cell looks when its value or another specified condition meets a rule. Unlike a color applied with the Fill Color button, a conditional format can update as worksheet data changes. For example, a low inventory count can turn red when it falls below a reorder threshold, then return to its normal appearance when the count rises.

The format is visual rather than a change to the cell’s contents. A cell that looks red still contains its original number, date, or text; the formatting does not replace the value or alter calculations that use it. This distinction matters when sorting, filtering, copying data, or sharing a workbook: the underlying value remains the same, while the displayed color depends on the rule and its current result.

Apply a simple color rule

For a straightforward test, use one of Excel’s built-in rules. Suppose a worksheet has weekly sales in cells B2:B20, and you want values above 1,000 to stand out:

Select B2:B20. Selecting the range first tells Excel which cells the rule should format.
Choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
Enter 1000 in the value box, then choose a preset format or select Custom Format to set a specific fill color.
Choose OK. Values greater than 1,000 receive the selected format; values equal to 1,000 do not.

Other built-in rules cover values less than or equal to a number, text that contains particular words, dates in a period, duplicate values, and values that rank among the top or bottom of a range. In the formatting dialog, use the Fill tab to select a background color. The Font tab changes text appearance, and the Border tab can add an outline. You can combine these choices when a color alone is not sufficiently clear.

To highlight duplicate entries, select the relevant range and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. The dialog lets you choose a format. This flags repeated values in the selected cells; it does not delete duplicates or decide which record should be kept.

Use a formula for custom conditions

A formula rule is useful when the condition is more specific than the built-in choices, or when the color of one cell should depend on another cell. Select the cells to format, 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, select Format, choose a fill on the Fill tab, and confirm.

With due dates in C2:C30, this formula highlights dates earlier than today:

With due dates in C2:C30, use =C2<TODAY(). The result is TRUE for a date before today and FALSE for today or a later date, so the past dates receive the chosen format.

Use the reference for the first cell in the selected range, here C2. Excel adjusts the relative reference for each cell in the range: the rule tests C3 for C3, C4 for C4, and so on. If the first selected cell is C5 instead, the formula should refer to C5. A mismatch between the selected range’s top-left cell and the formula reference is a common reason a formula rule appears shifted.

A formula can also test one column and format a different one. Suppose task names are in A2:A30 and their status is in B2:B30. Select A2:A30 and use the following rule:

With task names in A2:A30 and statuses in B2:B30, use =$B2="Late". The dollar sign locks the status column while the row number remains relative, so each task name is highlighted when the status in the same row is Late.

For a row-wide highlight, select A2:D30 and use the same formula, =$B2="Late". With task names in A, status in B, and other row details in C:D, every selected cell in a row is formatted when that row’s B cell says Late. The fixed column reference keeps the test in B as Excel applies the rule across the row.

Choose the right rule type

Conditional formatting offers more than a single solid color. The best choice depends on what readers need to notice and how they will interpret the sheet:

Highlight Cells Rules: Use for clear yes-or-no thresholds, matching text, dates, or duplicates. These are usually easiest to understand in trackers and lists.
Color Scales: Use when the relative size of many numeric values matters. A two- or three-color scale shades cells according to their position between low, middle, and high values. The colors represent a gradient, not separate categories.
Data Bars: Use when readers should compare values visually while still seeing the numbers. A bar within each cell indicates its value relative to the range.
Icon Sets: Use when a small symbol can communicate categories or thresholds. Check that the icons are understandable and that the thresholds match the intended meaning.
Formula rules: Use for logic involving other cells, multiple conditions, or custom comparisons. They provide flexibility but need careful reference management.

Color scales and bars can be helpful for exploration, such as spotting the highest and lowest monthly totals. They are less suitable when a color must mean an exact status. For example, a gradient that makes the smallest value red does not necessarily mean the item is overdue; readers may interpret the color as a warning even when it only indicates a relative minimum.

Manage overlapping rules and ranges

A cell can be affected by more than one conditional formatting rule. To review them, select a relevant cell and choose Home > Conditional Formatting > Manage Rules. In the rules manager, set the display list to This Worksheet when you need to inspect rules beyond the current selection. The manager shows each rule’s condition, format preview, and Applies to range.

When rules overlap, their order can influence the displayed format. Use the move up and move down controls to adjust priority. The Stop If True option can prevent lower-priority rules from being applied when a higher-priority rule is true, where that option is available for the rule. If two rules set different properties—for example, one sets a fill and another changes the font—Excel may display both properties. If they set the same property, priority becomes especially important.

Check the Applies to range when a rule seems to affect too many or too few cells. A rule intended for B2:B100 may accidentally cover an entire column or stop at row 20. You can edit the range in Manage Rules rather than recreating the rule. For formula rules, also verify that the formula’s relative and absolute references are appropriate for the full range.

Common problems and practical fixes

The color appears on the wrong row: Make the formula reference match the top-left cell of the Applies to range. Lock only the row or column that should remain fixed.
A number is not highlighted: Check whether it is stored as text, whether the threshold is correct, and whether the rule applies to that cell. Imported numbers sometimes look numeric but are text.
A date rule behaves unexpectedly: Excel stores valid dates as serial numbers. Confirm that the entries are actual dates, not text that merely looks like a date, and check whether the rule compares against today or a fixed date.
The fill color does not show: Another rule may take precedence, the rule may not include the cell, or the cell’s direct formatting may confuse the visual result. Review Manage Rules and the Applies to range.
A rule works for some rows only: Inspect blanks, inconsistent text such as extra spaces, and mixed data types. A text comparison such as Late may not match a cell containing Late followed by a space.

When troubleshooting, test the condition on a few known examples before applying it to a large table. Also remember that conditional formatting can make a worksheet harder to interpret if colors are used inconsistently or if too many rules compete for attention.

Conditional formatting versus manual color

Manual fill color is appropriate for a one-time annotation, such as marking a cell for discussion. It remains in place until someone changes it, even if the cell’s value changes. Conditional formatting is better when the color should reflect a rule and update with the data. For instance, manually coloring a late task red will not automatically turn it green when its status changes; a conditional rule can respond to the status value.

Conditional formats can also be copied, but copying cells may copy rules and alter their ranges or references. If you duplicate a worksheet section, review Manage Rules afterward to ensure the new rules apply to the intended cells. To remove a rule, select the affected cells and choose Home > Conditional Formatting > Clear Rules, then choose whether to clear rules from the selected cells or the entire sheet. Clearing a rule removes its conditional effect; it does not erase the cell values.

Accessibility and good color choices

Color should not be the only signal for an important status. Some people have difficulty distinguishing particular colors, and a printout or grayscale display may remove the distinction. Pair a fill with readable text, an icon, a border, or a separate status label. For example, use both a red fill and the word Late rather than relying on red alone.

Choose adequate contrast between text and background, and avoid using many similar shades for categories that must be distinguished quickly. Consider how the sheet will look on different screens and when printed. A simple, consistent legend can explain what colors mean, especially in shared reports. The meaning should be tied to the rule—for example, red means a value is below a stated threshold—not left for readers to guess.

Frequently Asked Questions

Can conditional formatting change a cell’s value?
No. It changes the appearance of a cell when a condition is met, but it does not replace the value or change the result of a formula in that cell.

Can I change the color based on another cell?
Yes. Create a formula rule and reference the other cell. For example, select A2:A30 and use =$B2="Late" to format each cell in column A according to the status in column B on the same row.

Will the color update when I edit the data?
Yes. Excel recalculates conditional formatting as relevant values change. A rule based on TODAY() can also change as the date changes, subject to Excel’s recalculation behavior.

How do I remove conditional formatting without deleting data?
Select the cells, then choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. The values remain; only the selected cells’ conditional formatting rules are cleared.

Can I use more than one conditional formatting rule on a cell?
Yes. Rules can overlap. Review their order and formatting in Home > Conditional Formatting > Manage Rules, and use Stop If True when appropriate to control lower-priority rules.

Does conditional formatting work in Excel for the web or on Mac?
Conditional formatting is available in Excel for the web and Excel for Mac, though the layout and available controls can differ from Excel for Microsoft 365 on Windows. On Mac, use the Home tab’s Conditional Formatting controls; in the web app, use the Home tab and inspect the available rule options in its formatting menu.

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.