Conditional Formatting in Excel: Color Coding Guide

Yes—conditional formatting in Excel automatically color-codes cells when their values meet rules you set. Select the cells, then choose Home > Conditional Formatting and pick a rule, such as Greater Than, Duplicate Values, or a color scale; for custom conditions, use New Rule.

What conditional formatting does

Conditional formatting changes a cell’s appearance when its contents satisfy a condition. It can apply a fill color, font color, border, or certain visual indicators without changing the underlying value. If the value changes and no longer meets the rule, Excel updates the formatting accordingly. This makes it useful for spotting exceptions in a live worksheet rather than manually coloring cells one at a time.

For example, a sales list could show values below a target in red and values at or above the target in green. A project tracker could highlight overdue tasks, while a stock list could use a color scale to distinguish low quantities from high ones. These colors are visual cues, not edits to the data: a red cell still contains its original number, date, or text.

Conditional formatting is different from applying a permanent fill with the paint bucket. A manually applied fill stays until someone changes or removes it. A conditional format responds to its rule, so the display can change as the worksheet changes.

How Excel’s color rules work

A rule consists of a condition, a range of cells to check, and a format to show when the condition is true. Excel includes ready-made rules for common tests, as well as rules based on formulas. A rule can check each cell on its own or use one cell to determine the color of an entire row.

For a quick value-based rule, select the cells and go to Home > Conditional Formatting > Highlight Cells Rules. Choose an option such as Greater Than, Less Than, Between, Text that Contains, or Duplicate Values, enter the condition, and select a format. Preset formats are convenient, and the Custom Format option lets you choose a fill, font, or border.

Excel also offers three visual rule types under Home > Conditional Formatting: Color Scales, Data Bars, and Icon Sets. These show relative differences across a range rather than simply applying one color when a yes-or-no test passes. They are useful for scanning patterns, but they should not replace clear labels or exact values when readers need precise information.

Choosing the right type of color coding

Choose a rule based on the question the worksheet should answer. A single-color rule is usually clearest for a specific exception, such as a due date that has passed. A color scale is better for seeing a gradual spread, such as which products have relatively high or low sales. Data bars show approximate magnitude inside each cell, while icon sets can group values into categories such as high, medium, and low.

Use a single color for a clear condition: highlight negative balances, late dates, or scores below a fixed threshold.
Use a color scale for relative comparisons: compare values across a range, keeping in mind that the colors depend on the range’s minimum and maximum.
Use data bars to compare amounts: bars make larger numbers stand out while leaving the cell values visible.
Use icon sets for a small number of categories: check the thresholds so readers understand what each icon means.

A three-color scale does not mean that the middle color represents a universal target. Its meaning depends on the selected range and the rule’s settings. If a business threshold matters—for example, every score below 70 must be flagged—use an explicit threshold rule rather than relying on a relative scale.

Set up a rule step by step

For a straightforward rule in Excel for Microsoft 365 on Windows, select the range first. Then open Home > Conditional Formatting, choose the rule type, provide its condition, and select the formatting. Review the sample in the dialog before confirming. For a rule that is not covered by the built-in choices, select Home > Conditional Formatting > New Rule.

Select only the cells that should receive the formatting. For example, select B2:B30 to color the sales figures, not the heading above them.
Choose a built-in rule or open New Rule. For a formula-based condition, select Use a formula to determine which cells to format.
Enter the condition or formula, then choose Format to set the fill, font, or other available formatting.
Confirm the rule and check several cells, including values that should pass and values that should not.
If the result is unexpected, open Home > Conditional Formatting > Manage Rules and check the rule’s range, priority, and condition.

On a Mac, the Conditional Formatting command is also on the Home tab, although dialog layouts and some controls can differ from Windows. Excel for the web provides conditional-formatting tools from the Home tab too, but available rule-editing options can vary. If a formula-based rule or detailed rule management is difficult to edit in a browser, open the workbook in the desktop app.

Color an entire row from a status cell

A useful custom rule can color a complete row based on one status column. Suppose a task table has task names in A2:A20, owners in B2:B20, due dates in C2:C20, and statuses in D2:D20. To color every cell in a row when its status is Overdue, select A2:D20, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter the formula below.

With statuses in D2:D20 and the selected formatting range A2:D20, use =$D2="Overdue". Choose a fill color and confirm. The dollar sign fixes the status column at D, while the row number stays relative, so Excel checks D2 for the first row, D3 for the next, and so on. The formula begins with the top row of the selected range; if the range starts at row 5, adjust the reference to row 5.

For overdue dates, you can check that a date is earlier than today and that the date cell is not blank. With due dates in C2:C20, select C2:C20 and use the following formula for a date-based rule.

With due dates in C2:C20, use =AND($C2<>"",$C2<TODAY()). This checks that the cell is not empty and that its date is before today. TODAY updates with the current date when Excel recalculates, so a task can become overdue without someone changing its cell. The rule assumes the cells contain actual Excel dates rather than date-like text.

Manage ranges, overlapping rules, and priorities

Conditional formatting applies only to the range shown in the rule’s Applies to field. If a new row falls outside that range, it may not receive the formatting. In Manage Rules, select the relevant rule and inspect Applies to; edit the range if needed. When adding rows regularly, an Excel table can help keep related data organized, but verify the rule’s Applies to range after adding data rather than assuming every rule will expand as intended.

More than one rule can apply to the same cell. Excel orders rules by priority in Manage Rules, and a higher-priority rule takes precedence when rules set conflicting formatting. The Stop If True option can prevent lower-priority rules from being evaluated once a higher-priority rule is true. Use it deliberately: it can make a special exception easier to manage, but it can also hide a lower rule that you expected to see.

For example, a cell might qualify both as below a target and as part of a special category. Decide which message should be most prominent, place that rule higher in priority, and test a value that meets both conditions. If two rules use different formatting features, Excel may show more than just the higher rule’s format; avoid relying on complicated overlaps when one clearly designed rule can express the result.

Common problems and practical fixes

The color appears on the wrong row: check the formula’s row reference against the first row of Applies to. For row-based rules, lock the condition column with a dollar sign but usually leave the row number relative.
New entries are not highlighted: expand the Applies to range in Manage Rules, or check whether the new data is inside the range already covered.
A date rule misses some entries: confirm that the cells contain valid Excel dates and not text that only looks like a date. A date stored as text may not compare as expected.
Colors seem inconsistent: inspect overlapping rules and their priority. Also check the scale’s minimum, midpoint, and maximum settings if you are using a color scale.
Old colors remain after a change: the cells may have a manually applied fill in addition to a conditional rule. Select the affected cells and inspect both the regular formatting and the conditional-formatting rules.
Too many rules make the sheet confusing: open Manage Rules, remove rules that are no longer needed, and use a small, consistent set of colors with a clear meaning.

Make color coding readable and useful

Color should reinforce information, not carry it alone. Some readers have difficulty distinguishing certain color combinations, and worksheets may be printed in grayscale or viewed on a screen with different settings. Pair color with a status word, symbol, or clear label where possible. For example, show Overdue in a status column as well as using a red fill.

Use a restrained palette. If every category has a different bright color, the important exceptions become harder to find. Keep the same color meaning throughout a workbook, check that text remains readable against the fill, and avoid using red and green as the only way to distinguish two important outcomes. A written legend can clarify what a color means, especially when a color scale or icon set is not self-explanatory.

Frequently Asked Questions

Can Excel automatically color a cell based on its value?
Yes. Select the cells, choose Home > Conditional Formatting, and create a rule such as Greater Than or Less Than. Excel updates the appearance when the cell value changes and the condition is met.

Can I color a whole row based on one cell?
Yes. Select the full row range, create a formula-based rule, and use a formula that fixes the condition column while leaving the row reference relative. Confirm that the formula’s row matches the first row of the selected range.

Does conditional formatting change the cell’s value?
No. It changes how the cell is displayed, not the value stored in it. A number remains a number, even when a rule gives the cell a colored fill.

Why does my conditional formatting stop at the last row?
The rule’s Applies to range may end before the new data. Check it in Home > Conditional Formatting > Manage Rules and extend the range if necessary.

Can I use more than one conditional-formatting rule?
Yes. Multiple rules can apply to the same cells. Review their order in Manage Rules, especially when their conditions overlap or their formats conflict.

Can I remove the colors without deleting the data?
Yes. 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. This removes conditional-formatting rules, not the cell contents.

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.