Conditional Formatting Colour in Excel: Rules and Fixes

In Excel, conditional formatting colour is a fill, font, border, data bar or icon that appears automatically when a cell meets a rule. In Microsoft Excel for Microsoft 365 on Windows, select the cells and go to Home > Conditional Formatting to create or change that rule; the colour is a visual cue, not a change to the cell’s underlying value.

What conditional formatting colour means

Ordinary formatting is applied directly: you select a cell and choose a fill or font colour. Conditional formatting instead tells Excel when to show a particular appearance. For example, a rule can turn a late status red, colour sales above a target green, or shade a range from low to high values. When the cell’s value or a value used by its rule changes, Excel can update the appearance automatically.

This distinction is useful when a worksheet changes frequently. You do not have to remember to recolour each updated value, and a reader can see patterns without changing the data. The stored value remains a number, date, or text; applying a red fill does not turn a number into text or make it negative. Likewise, colour alone is not a reliable way to store information, because it may not be visible to every reader or in every file format.

How to add a colour rule

For a straightforward rule in Excel for Microsoft 365 on Windows, select the cells you want to format, then choose Home > Conditional Formatting. The menu offers ready-made rules as well as options for scales and bars. For example, to highlight values greater than a threshold, use Highlight Cells Rules > Greater Than, enter the threshold, and choose a formatting style. You can also select Custom Format or create a rule with New Rule when you need a specific appearance.

To choose a precise fill or font colour, open the rule’s formatting options and use the Format Cells dialog. Its tabs let you set a fill, font, or border; the exact controls depend on the rule type. Check the sample or preview before confirming, then inspect the worksheet to make sure the result is clear. On Mac, the main Conditional Formatting controls are also on the Home tab, though dialog layouts and some controls can differ slightly. Excel for the web has conditional-formatting controls too, but advanced editing options may be more limited than in the desktop app.

A practical setup sequence is:

Select the intended range before creating the rule. If you select only one cell, Excel may apply the rule to only that cell.
Choose the rule type that matches the data: a comparison, a text or date condition, a scale, a bar, or a formula.
Set the condition and choose a colour with enough contrast against the worksheet background.
Confirm the rule, test it with a value that should match and another that should not, and correct the range if needed.

Choose a rule type that fits the data

Excel’s built-in conditional formatting options are useful for different kinds of comparisons. Under Home > Conditional Formatting, Highlight Cells Rules can flag values such as numbers above or below a threshold, text containing a word, dates in a period, or duplicate values. Top/Bottom Rules can identify high or low values. These rules are often the quickest choice when the condition is simple and applies to the cell being evaluated.

Color Scales use two or three colours to show relative values across a selected range. A two-colour scale might shade the lowest value one colour and the highest another, with intermediate values blended between them. A three-colour scale adds a midpoint. The midpoint can be useful when there is a meaningful centre, but the default midpoint may not represent a business target. Edit the rule if the scale should be anchored to a particular number or percentile rather than simply the observed range.

Data Bars put a bar inside each cell to represent the value relative to other values in the range. Icon Sets place symbols such as arrows or traffic-light-style indicators beside values according to thresholds. Both can make a comparison quick to scan, but they still rely on thresholds and the selected range. A bar’s length, for instance, is meaningful only in relation to the scale Excel uses for that rule. Review the rule settings if a negative number, zero, or unusually large value makes the display confusing.

Choose colours that communicate clearly

Use colour to support a comparison, not to decorate every cell. A restrained scheme makes exceptional values easier to find: for instance, a pale red fill for overdue items and a dark text colour that remains readable. Avoid combinations that differ only by hue, because some people have difficulty distinguishing certain colours and a printed or copied version may lose subtle differences. Contrast matters for text as well as fills.

Consider what the colour means before applying it. Red and green may suggest danger and success to many readers, but the meaning is not universal and can be inaccessible when used as the only signal. Pair a colour with a status word, icon, label, or clear column heading where appropriate. For a continuous scale, use an ordered progression that readers can interpret as low-to-high rather than a collection of unrelated colours. If the worksheet is likely to be printed in grayscale, test whether the distinctions remain visible without colour.

Colour scales can also mislead when applied to different groups that should be compared on the same fixed standard. If one department’s values are scaled only against its own range, its highest value may look as strong as another department’s even when the actual numbers differ. In that case, set comparable thresholds or use a single shared range, rather than interpreting the intensity of separate scales as an absolute comparison.

Use a formula to colour related cells

A formula-based rule can colour a whole row based on one cell, or apply a condition that is more complex than a built-in rule. In the example below, the worksheet has records in A2:D25, the status is in column C, and the rule applies to A2:D25. In New Rule, choose Use a formula to determine which cells to format, then enter:

=$C2="Late"

Choose a format and confirm. Excel checks the status in column C for each row and formats the row’s A:D cells when the status is Late. The dollar sign before C locks the status column as the rule moves across the row; the row number is not locked, so it changes from 2 to 3, 4, and so on. If you lock both the column and row as =$C$2="Late", every row will depend on C2 instead, which is usually not what a row-by-row rule needs.

Formula rules are sensitive to the first row of the selected range. If the range begins at row 2, references in the formula should generally be written for row 2 so Excel can adjust them for later rows. A formula that is logically sound can appear broken if its relative references do not match the applied range. To create or review this setup, use Home > Conditional Formatting > New Rule, and later inspect it through Home > Conditional Formatting > Manage Rules.

Manage rule order and applied ranges

A cell can be affected by multiple conditional-formatting rules. Excel evaluates rules according to their order in the Rules Manager, and a rule’s Stop If True setting can prevent lower-priority rules from being applied after a match. This can matter when, for example, one rule colours overdue items red while another colours all completed items green. If the rules overlap, check their order and the Stop If True boxes rather than assuming the most recently created rule always wins.

In Manage Rules, check the Show formatting rules for selection control so you are viewing rules for the intended worksheet or selection. Review the Applies to range: an incomplete range leaves some records unformatted, while an overly broad range may include headings or unrelated cells. You can edit, delete, reorder, or change a rule’s range there. When a worksheet is copied, sorted, or expanded, verify that the range still covers the relevant data; do not assume a rule will always expand exactly as intended.

To remove formatting rules, select the relevant cells and use Home > Conditional Formatting > Clear Rules. The menu includes options to clear rules from the selected cells or from the entire worksheet. Clearing a conditional-formatting rule removes that automatic formatting, not the cell contents. Directly applied formatting can remain, so a cell may still have a manually applied fill after its rule is cleared.

Common reasons the colour does not appear

If a rule seems not to work, first check whether the value actually meets its condition and whether the rule’s Applies to range includes the cell. Then inspect the rule type and its references. A text condition may not match because the cell contains different wording; a formula rule may refer to the wrong row or column; and multiple rules may compete. Also check whether a number is stored as text, because a numeric comparison rule may not behave as expected when the underlying content is text.

Other common causes include a rule based on a different cell than expected, thresholds that exclude the value, and an accidental rule on only the active cell. If a colour seems to disappear after pasting, note that pasting can bring formatting or rules from another source and can change what is visible. Inspect Manage Rules and the cell’s formatting rather than repeatedly creating new rules, which can leave overlapping conditions behind.

Conditional formatting displays a result; it does not rewrite a cell’s value. A cell may therefore look red while its formula bar still shows the original number or text. This is expected. If an output file is saved as CSV, the file retains tabular values but not Excel’s conditional-formatting rules and visual styling. Keep a workbook format such as .xlsx when the rules need to remain editable.

Conditional formatting versus manual colour

Use manual colour when a cell needs a fixed appearance that does not depend on its contents, such as a heading or a label. Use conditional formatting when the appearance should respond to a value or condition. The two approaches can coexist: a rule can control the fill while manual formatting supplies another property. But when a conditional-formatting rule sets the same visual property, such as fill colour, its displayed result can take precedence over the manual fill. This is one reason a manually chosen colour may appear to have no effect while a rule is active.

Neither method is a substitute for clear data. A manually coloured cell may not update when its status changes; a conditional colour may be lost in a format that does not preserve Excel rules. If colour represents an important status, keep the status in a text or numeric field as well. That makes the meaning searchable, filterable, and available to readers who cannot distinguish the colour.

Frequently Asked Questions

Can I change the colour of an existing conditional-formatting rule?
Yes. Select a cell in the affected range, go to Home > Conditional Formatting > Manage Rules, select the rule, and choose Edit Rule. Change its format settings and confirm. Check that you edited the rule applying to the cell you intended.

Can conditional formatting colour an entire row?
Yes. Apply a formula-based rule to the full row range and use a relative row reference with a locked status column. For example, with records in A2:D25 and statuses in C2:C25, the formula =$C2="Late" can format each A:D row when its corresponding status is Late.

Does conditional formatting change the cell’s value?
No. It changes how a cell is displayed when a rule is met. The original value or formula remains in the cell, even if a fill, font, icon, or data bar makes it look different.

Why is a manually chosen fill colour not showing?
A conditional-formatting rule may be setting the fill for that cell and taking precedence over the manual fill. Review the rules in Manage Rules, edit or clear the relevant one, or adjust the rule so it does not apply to that cell.

Will conditional formatting remain in a CSV file?
No. CSV is a plain-text data format and does not preserve Excel’s conditional-formatting rules or cell styling. Save the workbook as .xlsx if you need the rules and their appearance to remain available in Excel.

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.