Conditional Formatting in Excel: Questions Answered

How-to basics · Updated November 18, 2026

In Excel for Microsoft 365 on Windows, select the cells you want to format, then choose Home > Conditional Formatting and select a rule or choose New Rule to define one. Excel applies the chosen font, fill, border, data bar, icon, or color scale when the cell values meet the rule; for more specific logic, use a formula-based rule.

What conditional formatting does

Conditional formatting changes how cells look according to their contents or a rule, without changing the stored values. A sales figure can turn red when it falls below a target, overdue dates can stand out, and a color scale can show the relative high and low values in a range. The formatting updates when the underlying values change or when Excel recalculates a rule.

It is useful for drawing attention to exceptions, trends, duplicates, and status changes. It is not a data-cleaning or validation feature: a red cell is still the same value, and users can still enter data that breaks a business rule unless you also use a tool such as Data Validation.

  • Highlight Cells Rules can format values greater than, less than, equal to, or between specified values, as well as text containing a phrase, dates in a chosen period, and duplicate or unique values.
  • Top/Bottom Rules identify items such as the top 10 values, above-average values, or a chosen percentage of a range.
  • Data Bars, Color Scales, and Icon Sets visualize value size or position rather than applying only a simple pass-or-fail highlight.
  • New Rule provides additional choices, including rules based on formulas.

How to create a rule

For a quick rule, select the target cells and use Home > Conditional Formatting. For example, to flag values below a target, choose Highlight Cells Rules > Less Than, enter the threshold, choose a format, and select OK. The rule applies to the selected range. If you later select other cells, check the rule’s range rather than assuming Excel extended it.

For a custom rule in desktop Excel for Microsoft 365 on Windows, select the range, choose Home > Conditional Formatting > New Rule, and select the rule type. A common choice is “Use a formula to determine which cells to format.” Enter the formula, choose Format to set the appearance, and confirm. The formula should return TRUE for cells that need the format and FALSE for those that do not.

On Mac, the Conditional Formatting command is also on the Home tab, though dialog layouts and some controls can differ. Excel for the web has conditional-formatting commands on the Home tab too, but its rule-management options and dialogs may not match desktop Excel exactly. If a workbook has complex rules or you cannot find a control in the web interface, open it in the desktop app.

Formula rules and cell references

Formula-based rules are especially useful when the condition depends on another column or combines multiple tests. The crucial detail is that Excel evaluates the formula relative to the top-left cell of the selected “Applies to” range. Relative references move as the rule is evaluated across the range; dollar signs lock a column, row, or both.

Suppose the range A2:D100 contains task details, column C contains status, and column D contains due dates. To highlight an entire row when the task is open and its due date has passed, select A2:D100, create a formula rule, and use the formula below. The dollar sign before C and D locks those columns, while the row number remains relative so each row checks its own status and date.

With statuses in C2:C100 and due dates in D2:D100, and the rule applied to A2:D100, use =AND($C2="Open",$D2<TODAY()). For a row where C is Open and D contains a date before today, the formula returns TRUE and formats all four cells in that row. For a closed task, or a task due today or later, it returns FALSE. TODAY() uses the current date and updates when Excel recalculates.

A frequent mistake is selecting A2:D100 but writing references for row 1. The rule then checks the wrong row. Another is locking every reference: =$C$2>100 always checks C2, whereas =$C2>100 checks column C separately for each row. Use dollar signs deliberately: lock what must stay fixed, and leave relative what should shift.

Useful examples and choosing a format

The best rule depends on what a reader needs to notice. A bright fill may be appropriate for an exception that requires action; a subdued color scale is often easier to read for a dense numeric table. Consider color contrast and do not rely on color alone if the workbook must remain accessible in grayscale or for readers with color-vision differences.

  • Flag overdue dates: Select the date cells and use a formula rule to compare each date with TODAY(). Exclude blank cells where needed, or blank cells may be treated unexpectedly by a comparison rule.
  • Highlight duplicate IDs: Select the ID range and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Check whether the selected range includes headers, since a repeated heading or blank can affect what gets highlighted.
  • Show a threshold: Use Highlight Cells Rules > Greater Than or Less Than for a straightforward numeric cutoff. This is easier to maintain than a formula when the condition is only a single comparison.
  • Compare values within a range: Use a color scale or data bars to show relative magnitude. These emphasize distribution and ranking, not whether a value passes a fixed business target.

For instance, a data bar can make monthly sales differences visible at a glance, while a separate threshold rule can flag months below a target. These do different jobs: a bar shows relative size, and a threshold identifies a specific condition. If both are used, check the result in the cells because overlapping visual effects can become busy.

Rule order, scope, and conflicts

Conditional formatting rules are associated with a range, shown as “Applies to.” To inspect them, select a cell in the range and choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, use the option for the current selection or the worksheet to see the relevant rules. The exact controls can vary by platform, and a rule may apply to more cells than the currently selected ones.

When multiple rules are true for the same cell, order matters. Excel lists rules by priority, and higher-priority formatting takes precedence when two rules set the same formatting property. If a higher rule has Stop If True enabled, lower-priority rules are not evaluated for that cell after it is true. Without that setting, lower rules can still contribute formatting properties that do not conflict. Move rules up or down in Manage Rules if their priority is wrong.

For example, a red fill rule for “overdue” and a bold-font rule for “high priority” can both be useful on one task if they affect different properties. Two rules that both set the fill color compete, so the higher-priority applicable rule determines the fill. This is a common reason a rule appears to have stopped working when it is actually being overridden.

Editing, extending, and removing rules

To change a condition or its appearance, open Manage Rules, select the rule, and choose Edit Rule. Review both the formula or condition and the Applies to range. A correct formula attached to the wrong range can be just as misleading as an incorrect formula.

When adding rows, consider using an Excel Table for structured data. A rule applied to a table column often extends as table rows are added, but check the Applies to range after changes, especially if data was pasted or the rule was copied from elsewhere. Copying cells can also copy conditional formatting, creating duplicate or overlapping rules. Use Manage Rules to remove unnecessary entries.

To remove formatting rules, choose Home > Conditional Formatting > Clear Rules. Excel offers options to clear rules from selected cells or from the entire worksheet. Clearing a rule does not delete the values, but it removes the conditional appearance. If you only want to adjust one range, avoid clearing rules from the whole sheet.

Common problems and fixes

  • The wrong rows are highlighted: Check the top row in the formula against the first row in Applies to. Then review whether each column or row reference should be absolute or relative.
  • Nothing is highlighted: Confirm the condition, data type, and range. A date stored as text may not compare like a real Excel date, and numbers stored as text may not behave like numeric values.
  • Only one cell changes: The Applies to range may contain only that cell. Edit the range in Manage Rules and make sure the formula is written relative to its top-left cell.
  • Some cells show an unexpected color: Look for another rule with higher priority, conflicting formatting, or Stop If True. Also check whether blanks, spaces, or hidden values meet the condition.
  • Rules multiply after pasting: Pasting formatted cells can bring their rules along. Inspect the worksheet’s rules and remove redundant entries rather than repeatedly applying the same rule.

For formula-based date rules, decide explicitly how blanks should behave. With dates in B2:B100, and the rule applied to B2:B100, this formula excludes empty cells and flags dates earlier than today: =AND(B2<>"",B2<TODAY()). A nonblank past date returns TRUE; a blank or future date returns FALSE. The formula assumes actual Excel dates are in column B and that the selected range begins at row 2.

Conditional formatting versus other Excel tools

Conditional formatting is for visual feedback. Data Validation can restrict or guide what a person enters, but it does not replace visual rules. Formulas can calculate a status in a separate column, which may be preferable when the result needs to be filtered, reused in calculations, or exported. Conditional formatting can then highlight that status for easier scanning.

For example, a status formula can label a deadline “Overdue,” while conditional formatting colors the resulting label. That separates business logic from appearance: readers can filter the status, and the formatting remains a visual aid. If the logic is simple and only the display matters, a conditional-formatting rule may be enough.

Frequently Asked Questions

Can conditional formatting use a formula?

Yes. Choose Home > Conditional Formatting > New Rule, then select the formula-based rule option. The formula must return TRUE or FALSE, and its references should be written for the top-left cell of the Applies to range.

Does conditional formatting change the cell’s value?

No. It changes the cell’s appearance only. The underlying value remains available to formulas, sorting, filtering, and other workbook features.

Why does my conditional formatting apply to the wrong cells?

Check the Applies to range and the formula’s row and column references in Manage Rules. A mismatch between the first row of the range and the row used in the formula is a frequent cause.

Can I apply more than one conditional formatting rule to a cell?

Yes. Multiple rules can apply to the same cell. Their priority and whether Stop If True is enabled determine how overlapping formatting is resolved.

Will conditional formatting update when values change?

Generally, yes: Excel reevaluates rules as cell values change and during recalculation. Rules that depend on volatile functions such as TODAY() can change as the date changes, subject to workbook recalculation.

Practical takeaway

Start with the simplest rule that expresses the condition, select the full intended range before creating it, and verify both the formula and Applies to range afterward. Use formula rules when logic spans columns or needs multiple tests; use built-in rules for common comparisons and visualizations. Finally, inspect rule priority and test representative cases, including blanks and boundary values, so the formatting communicates the data accurately rather than merely decorating it.

Related

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.