Conditional Formatting in Excel With Colors

In Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting and pick a rule; set its fill or font color in the rule’s formatting options. Excel then changes how qualifying cells look automatically as their values change, without changing the underlying data.

What conditional formatting does

Conditional formatting applies visual styles when specified conditions are met. A rule might color overdue dates red, highlight sales above a target, or shade values according to their size. Unlike manually filling cells, a rule can update its appearance when the value changes or when new data is entered within the rule’s range.

The color is a visual signal, not a change to the cell’s value. For example, a cell containing the number 1250 remains 1250 even if a rule displays it with a green fill. You can still calculate with that number, sort it, or use it in a chart. If you remove the rule, the conditional appearance disappears and the cell’s ordinary formatting shows again.

Conditional formatting is useful for finding patterns in tables without reading every entry. It is especially effective when the color communicates a simple, consistent meaning, such as red for below target and green for meeting target. It can also draw attention to duplicates, date ranges, blanks, or unusual values.

Apply a built-in color rule

For common conditions, Excel provides ready-made rules. To highlight numbers above a threshold in a range:

Select the cells you want to check, such as B2:B50.
Choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
Enter the threshold in the dialog, then choose a preset format or select Custom Format to specify the appearance.
Select OK to apply the rule.

Excel offers similar commands for values less than or equal to a number, text that contains specified characters, dates in a chosen period, and duplicate or unique values. The precise options depend on the rule selected. For instance, the Duplicate Values dialog lets you choose whether to highlight duplicates or unique entries and select a format.

To make a specific fill or font color, use Custom Format when available, or choose Home > Conditional Formatting > New Rule and select a rule type. In the formatting controls, choose a fill color on the Fill tab, a text color on the Font tab, or both. A fill colors the cell background; a font color changes the text itself.

Choose the right kind of color rule

The rule type should match the question the worksheet needs to answer. Excel’s main options serve different purposes:

Highlight Cells Rules: Mark values that meet a condition, such as dates before today, numbers above a limit, or text containing a word.
Top/Bottom Rules: Emphasize the highest or lowest values, or values above or below an average. These rules are useful for ranking, but they do not explain why a value ranks where it does.
Data Bars: Place a bar inside each cell, with longer bars representing larger values. They show relative magnitude while leaving the number visible.
Color Scales: Shade cells along a color gradient. A two-color scale maps values between low and high colors; a three-color scale adds a midpoint color.
Icon Sets: Display symbols such as arrows or traffic-light icons according to value ranges. These add a visual cue but may take up space or be harder to interpret in a dense table.
Formula-based rules: Apply formatting when a formula evaluates to TRUE. They allow conditions that the built-in rules cannot express, including formatting whole rows based on one column.

Color scales work best for comparing values within a range, rather than communicating a fixed pass/fail threshold. A red-to-green scale, for example, assigns colors relative to the selected data. If the data changes, the points represented by the scale’s colors can change too. For a fixed target, a rule such as “greater than 100” is usually clearer.

Use a formula to color a whole row

A formula rule can check a value in one column and format several cells in the same row. Suppose A1:D5 contains a sales table: column A is Date, B is Sales, C is Region, and D is Owner, with the records in rows 2 through 5. The Sales values are 800, 1250, 1000, and 600 in B2:B5. Select A2:D5, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter this rule:

=$B2>=1000

Choose a fill color and confirm the rule. Because the applied range begins on row 2, the formula checks column B on each corresponding row. The dollar sign fixes the column reference at B, while the row number stays relative. The result is that rows 3 and 4 are highlighted: 1250 and 1000 both meet the threshold, including the value equal to it. Rows 2 and 5 are not highlighted.

When writing a formula rule, base its row references on the first row of the selected range. If the selected range begins at row 2, use row 2 in the formula. A common mistake is to select A2:D5 but write a formula referencing B1; that shifts the checks and can make the formatting appear one row out of place. The formula should return TRUE for cells or rows that should receive the format.

Manage rule order and overlapping formats

A cell can meet more than one rule. For example, it might be both above average and above a target. Excel evaluates rules in priority order, which you can inspect and change through Home > Conditional Formatting > Manage Rules. In the Rules Manager, use the controls to move rules up or down and review the Applies to range to see which cells each rule affects.

When rules set different properties, such as one rule setting a fill and another setting a font color, both effects may appear. When rules set the same property, the priority order matters. The Stop If True option can prevent lower-priority rules from being evaluated for a cell after a higher-priority rule is true. Use it only when that behavior is intended; otherwise, it can hide formatting from later rules.

In the Rules Manager, the view selector can show rules for the current selection, the current worksheet, or other relevant scopes. If a rule seems missing, check the selected range and switch the view to the whole worksheet. This is also a useful place to edit a threshold, adjust the applied range, or remove a rule without clearing unrelated formatting.

Choose colors that communicate clearly

A useful color scheme makes important information easier to scan rather than turning every cell into a separate signal. Reserve strong colors for exceptions or important categories, and use softer fills for background context. Keep the meaning stable: if red means below target in one part of a workbook, avoid using red to mean above target elsewhere without a clear reason.

Do not rely on color alone when the worksheet will be read by people with color-vision differences, printed in grayscale, or viewed on a low-quality display. Pair color with a label, a symbol, or an explicit status column where practical. For example, a status value such as “Late” gives meaning that a red fill by itself may not provide. Check that text remains legible against the fill, particularly with dark backgrounds or strong gradients.

Color scales are most helpful when comparing many numeric values on the same scale. They can mislead when the range contains an extreme outlier, because most other cells may appear nearly the same color. Consider whether the reader needs relative comparison or a clear threshold before choosing a scale. For a small table, a single highlight color for values that need attention may be easier to understand.

Fix common conditional-formatting problems

If colors do not appear as expected, first verify the rule’s condition and the range it applies to. Open Home > Conditional Formatting > Manage Rules, select the relevant worksheet view, and inspect both the rule and its Applies to range. A rule may be correct but attached to the wrong cells, or a newly added row may fall outside the range.

The wrong row is colored: For a formula rule, check whether its references align with the first row in the applied range. Decide which references should be fixed with dollar signs and which should move across rows or columns.
Some values are missed: Check for text stored in cells that should contain numbers or dates. A value that looks numeric may not be interpreted as a number, so a numeric comparison may not work as intended.
Unexpected colors appear: Look for another rule applying to the same cells. Review rule order, overlapping ranges, and whether Stop If True is enabled.
New rows are not formatted: Expand the rule’s Applies to range. If the data is an Excel table, adding rows within the table can help keep the data range organized, but review the rule range if the format does not extend.
Manual formatting seems inconsistent: Conditional formatting controls the displayed style while its condition is true. Changing a cell’s ordinary fill does not remove the conditional rule that is producing the visible color.

To clear rules, select the relevant cells and choose Home > Conditional Formatting > Clear Rules. The menu offers options to clear rules from the selected cells or from the entire worksheet. Check the selection before clearing, since clearing from a whole sheet removes rules beyond the area currently in view.

Excel versions and alternatives

The instructions here are for Excel for Microsoft 365 on Windows. Excel for Mac also provides conditional-formatting commands on the Home tab, though dialog layouts and some controls can differ. Excel for the web supports common conditional-formatting tasks, but editing options may not match the desktop application in every case. If a particular advanced rule is unavailable in the version being used, open the workbook in desktop Excel to inspect and manage it.

Conditional formatting is not the only way to distinguish information. A table style can provide consistent banded rows, and a separate status column can make categories explicit and filterable. Manual formatting is reasonable for a one-time presentation that will not change, but it will not automatically respond to updated values. For changing data, a conditional rule is generally more reliable than repeatedly applying colors by hand.

Frequently Asked Questions

Can I use more than one color rule on the same cells?
Yes. Multiple rules can apply to the same cells, and their order may affect the result when they set the same formatting property. Review their priority and Stop If True settings in Manage Rules.

Does conditional formatting change the cell’s value?
No. It changes the cell’s appearance when a condition is met. The value remains available for calculations, sorting, and other workbook operations.

How do I color a cell based on another cell?
Create a formula-based rule for the cells to format, then write a formula that refers to the cell containing the condition. Set the references so the formula checks the intended row or column across the applied range.

Can I copy conditional formatting to other cells?
Yes. You can copy cells and use Paste Special for formats, or use Format Painter. After copying, check Manage Rules to confirm that the rule’s applied range and relative references still match the destination cells.

Why did the color disappear after I changed a value?
The changed value may no longer meet the rule’s condition, which is normal: conditional formatting updates dynamically. If it should still qualify, inspect the rule threshold, applied range, and the value’s data type.

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.