Excel Conditional Formatting Formula Rules Explained

Formulas & functions · Updated November 22, 2026

In Excel, a conditional-formatting formula rule applies a format when a formula evaluates to TRUE. On Windows, select the cells, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format; enter a formula beginning with =, choose a format, and select OK.

What a formula rule does

A formula rule lets you control conditional formatting with a logical test rather than choosing only from a preset such as “Greater Than” or “Text That Contains.” Excel checks the formula for the cells in the rule’s “Applies to” range. When the result is TRUE, it displays the format you chose; when the result is FALSE, that rule does not apply its format to that cell.

The format changes how a cell looks, not the value stored in it. For example, a rule can turn an overdue date red without changing the date, replacing it with text, or writing a result into another cell. The rule updates as the values it depends on change, so it is useful for tracking thresholds, statuses, dates, and relationships between columns.

Formula rules can test the cell being formatted, a different cell on the same row, or a combination of conditions. A formula can use ordinary Excel functions, references, and operators, provided it returns a value Excel can interpret as TRUE or FALSE. The formula is not a formatting instruction by itself: you also choose the fill, font, border, or number format to display when the test is true.

How to create a formula rule

On Excel for Microsoft 365 for Windows, use this sequence:

  1. Select the cells the rule should format. For a whole-row highlight, select the full table area, not only the column containing the test value.
  2. Choose Home > Conditional Formatting > New Rule.
  3. Select Use a formula to determine which cells to format.
  4. Enter the formula in the formula box. It must begin with an equals sign.
  5. Select Format, set the desired appearance on the available tabs, and select OK.
  6. Check the preview and confirm the rule. If needed, open Conditional Formatting > Manage Rules to review its range and priority.

For example, suppose a task list has names in A2:A20, due dates in B2:B20, statuses in C2:C20, and notes in D2:D20. To shade an entire row when its status is Late, select A2:D20 first. With that layout, enter =$C2="Late". Choose a fill color and confirm. The dollar sign fixes the status column at C, while the row number remains adjustable, allowing the rule to test C2 for the first row, C3 for the next, and so on.

The order matters: Excel treats the formula as written for the top-left cell of the selected range. If the selection starts at A2, the example must refer to row 2. If the selection instead starts at A5, write the formula for row 5. A mismatch between the first selected cell and the formula’s starting row is a common cause of highlighting the wrong records.

Relative and absolute references

Conditional-formatting formulas follow the same reference rules as ordinary worksheet formulas. A relative reference changes as Excel evaluates the rule across the applied range; an absolute reference stays fixed; and a mixed reference fixes either the column or the row. Dollar signs are therefore central to rules that compare a row’s value with another column or a fixed threshold.

  • Relative reference: A2 can move to B2 when evaluated one column to the right and to A3 in the next row.
  • Absolute reference: $A$2 stays fixed at A2 wherever the rule is applied.
  • Mixed reference: $A2 keeps column A fixed but lets the row change; A$2 keeps row 2 fixed but lets the column change.

For a row-based rule, a useful pattern is to lock the column containing the condition and leave the row relative. For a range-wide comparison against one setting cell, lock both parts of the setting-cell reference. Always consider the top-left cell of the applied range, then check whether references should move across columns, down rows, or neither.

Useful formula-rule examples

Highlight values above a cutoff. Suppose sales amounts are in D2:D100, and you want to mark amounts above 1,000. Select D2:D100. With that layout, enter =D2>1000. Values greater than 1,000 meet the condition; a value of exactly 1,000 does not. To make the cutoff adjustable, put it in F1 and use =D2>$F$1 instead. With sales in D2:D100 and the cutoff in F1, this compares each sales value to the fixed value in F1.

Flag overdue, incomplete work. Suppose due dates are in B2:B100 and statuses are in C2:C100. Select the table rows you want to highlight, beginning on row 2. With that layout, use =AND($B2<>"",$B2<TODAY(),$C2<>"Complete"). It requires a nonblank due date, a date before today, and a status other than Complete. The blank check prevents empty date cells from being treated like old dates. TODAY updates with the current date when Excel recalculates, so this rule is dynamic rather than tied to the day it was created.

Find repeated entries. Suppose codes are in A2:A100, and you want to highlight every occurrence of a code that appears more than once. Select A2:A100. With that layout, use =COUNTIF($A$2:$A$100,A2)>1. The counted range stays fixed while A2 changes for each cell being checked. This marks all copies of a repeated value, not just later copies. If blanks should not be marked as duplicates, use =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1) with the same selected range and layout.

Designing clear and reliable tests

Start by writing the condition in plain language: for example, “the due date is before today and the status is not Complete.” Then translate each part into a test and combine them. AND requires every condition to be true; OR requires at least one. When conditions become difficult to read, split them into simpler rules or use a helper column with a normal worksheet formula. Helper columns can make a workbook easier for colleagues to inspect and troubleshoot.

Text comparisons usually need quotation marks around literal text, as in $C2="Late". References to cells do not go in quotation marks. A misspelled status, an extra space, or a different label such as “Completed” will not match “Complete.” If the source data is inconsistent, consider cleaning it or using a condition suited to the actual entries rather than assuming the wording.

Use the simplest formula that expresses the intended condition. A rule that checks the same information repeatedly may be harder to maintain than one clear test. Also decide how blank cells should behave: explicitly testing for a nonblank value often prevents surprising highlights, particularly in date and numeric comparisons. Test the rule on a few known examples, including one that should pass and one that should fail.

Rule ranges, order, and conflicts

Conditional Formatting > Manage Rules is where you can inspect a rule’s formula, its “Applies to” range, and its priority. A correct formula applied to the wrong range will still produce an incorrect-looking result. When extending a table, verify that the new rows are included in the rule’s range; do not assume every manually created range expands automatically in every workbook setup.

More than one rule can apply to the same cell. Excel evaluates rules according to their order, and the Stop If True option can prevent lower-priority rules from being evaluated after a matching rule. If a cell appears to have an unexpected format, inspect all rules that cover it, their order, and whether Stop If True is selected. Overlapping formats can also make the visible result confusing, especially when rules set different fills, fonts, or borders.

Manage Rules may show rules for the current selection or for the worksheet, depending on the display option you choose. If you cannot find a rule, select a cell where it should apply and open the manager again, or change the display to show rules for the worksheet. This is also a good place to correct a range after copying cells or reorganizing a table.

Formula rules versus built-in rules

Built-in conditional-formatting options are often quicker for straightforward tasks. For example, Home > Conditional Formatting > Highlight Cells Rules can flag values above a number or text that contains a phrase. Color Scales, Data Bars, and Icon Sets provide visual summaries without requiring you to write a logical test.

Choose a formula rule when the condition depends on another cell, combines multiple tests, or must format a whole row based on one column. A built-in rule is generally easier to set up and understand for a simple threshold; a formula rule offers more control but makes reference choices, applied ranges, and rule priority your responsibility. Both approaches change display formatting rather than the underlying data.

Common problems and how to fix them

  • The wrong row is highlighted: Compare the formula’s row number with the top-left cell in “Applies to.” Adjust the formula so it is written for that first cell.
  • Every row uses one status: Check for a locked row reference. For row-by-row checks, a formula such as =$C2="Late" fixes the column but leaves the row relative; =$C$2="Late" always checks C2.
  • The rule never applies: Confirm the formula starts with =, returns TRUE for a known example, and uses the exact text or values in the cells. Verify the selected format is visible and the intended range is included.
  • Blank cells are highlighted: Add an explicit nonblank test where appropriate, such as the first test in the overdue-date example.
  • A different rule’s color appears: Open Manage Rules and review overlapping ranges, priority, and Stop If True.
  • The formula is rejected: Check quotation marks, parentheses, commas, and cell references. Excel’s separator settings can vary by region; some installations display a different argument separator. The examples here use comma separators as requested, but follow the separator Excel expects in your installation.

When troubleshooting, temporarily test the logic in an ordinary worksheet cell using the same references, then compare the result with what the rule should evaluate for its first cell. This can reveal whether the problem is in the condition itself or in the rule’s applied range and relative references. Remove any temporary test afterward if it is not part of the workbook design.

Windows, Mac, and Excel for the web

The main steps above describe Excel for Microsoft 365 on Windows. Excel for Mac also provides formula-based conditional formatting through the Home tab’s Conditional Formatting and New Rule controls, although dialog layouts and some labels may differ slightly by release. Check the formula’s applied range and references in the rule manager if the Mac dialog presents the settings differently.

Excel for the web supports conditional formatting, but its menus and rule-editing interface can differ from the desktop application. For a simple formula rule, look under Home > Conditional Formatting and use the available new-rule options. If a particular workbook’s rule is difficult to create or manage in the browser, open it in the desktop app, where the full rule-management dialog is available. Menu availability can change as Microsoft updates the web interface.

Frequently Asked Questions

Does a conditional-formatting formula have to start with an equals sign?
Yes. Enter it as a worksheet-style formula beginning with =, and make sure the result can be interpreted as TRUE or FALSE. For example, with values in D2:D100, =D2>1000 is a valid test for the first cell in that range.

Can a formula highlight an entire row?
Yes. Select the full rows or table range you want to format, then write the test using the first selected row. Lock the test column but leave the row relative, as in =$C2="Late" for a selection beginning on row 2.

Can I use AND or OR in a rule?
Yes. Use AND when all listed conditions must be true and OR when at least one must be true. For example, the overdue example combines a nonblank date, an earlier-than-today date, and a status that is not Complete.

Why does the formula work in a cell but not in conditional formatting?
The formula may be correct but written for the wrong starting cell, or the “Applies to” range may not match your selection. Check relative and absolute references, confirm the formula evaluates as intended for the range’s top-left cell, and review other rules that may override its appearance.

Does conditional formatting change the cell’s value?
No. It changes how the cell is displayed when a rule is true. To change, calculate, or store data, use a worksheet formula or another appropriate Excel feature; conditional formatting alone does not edit the cell contents.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.