Conditional Formatting Formula in Excel: How to Use It

Formulas & functions · Updated November 22, 2026

In Excel, a conditional-formatting equation is a formula that returns TRUE or FALSE; Excel applies the chosen format when the result is TRUE. With numbers in B2:B20, for example, a rule applied to B2:B20 can use =B2>100 to highlight values greater than 100.

What a conditional-formatting formula does

Conditional formatting changes how cells look when a condition is met. A formula-based rule lets you define that condition with an Excel formula, rather than choosing only from a preset option such as “Greater Than” or “Text That Contains.” You can use it to highlight a single cell, a whole row, or a range based on values elsewhere in the worksheet.

The formula does not put a result into the cell, and it does not change the cell’s underlying value. It evaluates the condition and, if the condition is true, displays the formatting you selected—such as a fill color, font color, or border. If the underlying data changes, Excel reevaluates the rule and updates the appearance.

A conditional-formatting formula must evaluate to TRUE or FALSE for each cell Excel checks. It usually starts with an equals sign, just like a worksheet formula. The key is to write it as though you are describing the condition for the first cell in the range to which the rule applies. Excel then adjusts relative references for the other cells.

How to create a formula-based rule

In Excel for Microsoft 365 on Windows, select the cells to format, then choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, select “Use a formula to determine which cells to format.” Enter the formula, choose Format to set the appearance, and select OK. The preview shows the selected style before you confirm the rule.

For the example in the opening paragraph, select B2:B20 before creating the rule. The layout is numbers in B2:B20, with the rule applied to B2:B20; the formula is =B2>100. A value of 125 in B2 meets the condition and is formatted, while a value of 85 does not. You can choose a fill color, for example, to make qualifying values stand out.

To review or revise a rule later, choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, check the “Show formatting rules for” setting: it can display rules for the current selection, the current worksheet, or other available scopes. The “Applies to” field shows which cells the rule affects. This is also where you can change a rule’s formula, format, range, or priority.

On Excel for Mac, the Conditional Formatting commands are also on the Home tab; use Home > Conditional Formatting to create and manage rules. Dialog wording or layout can vary slightly by build. Excel for the web supports conditional formatting, though some rule-editing options may differ from the desktop app. If a workbook will be edited in different versions, check that the rule remains available and displays as intended in each one.

Relative and absolute references

Cell references determine how Excel tests a range. A reference such as B2 is relative: as Excel evaluates the next row, it checks B3, then B4, and so on. Dollar signs lock a column or row. For instance, $C2 keeps the check in column C while allowing the row number to change. This distinction is essential when the formatting range covers multiple rows or columns.

To highlight entire rows according to a status in column C, assume a table occupies A2:D100, with each row’s status in column C. Select A2:D100, so A2 is the upper-left cell of the applied range, and create a formula rule using =$C2="Open". The dollar sign fixes the status column, while the row number remains relative. If C2 contains Open, the formatting applies across A2:D2; a row with Closed in column C is not highlighted.

A common mistake is writing the formula for the wrong starting cell. If the selected range begins at A2 but the formula refers to row 1, Excel evaluates each row against the wrong record. Another mistake is locking both the row and column when the rule needs to move down the sheet. Before saving, verify that the first reference in the formula matches the top-left cell in the “Applies to” range.

Useful examples for common tasks

Highlight repeated entries: Suppose names or product codes are in A2:A20. Select A2:A20 and use =COUNTIF($A$2:$A$20,A2)>1. The fixed range checks the full list, while A2 adjusts for each cell being evaluated. Any entry that appears more than once is formatted. Excel also offers a built-in duplicate-values rule under Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values; use the formula version when you need to tailor the logic or apply formatting based on a different range.

Flag overdue open tasks: Assume task status is in C2:C100 and due dates are in D2:D100. Select A2:D100 to format whole task rows, then use =AND($C2="Open",$D2<TODAY()). The rule is true only when the status is Open and the due date is earlier than today. If a row has Open in C2 and yesterday’s date in D2, it qualifies; a due date of today does not, because the test uses “less than,” not “less than or equal to.” TODAY updates with the current date when Excel recalculates.

Shade alternating rows: For a range beginning at A2, select the range and use =MOD(ROW(),2)=0. ROW returns the row number and MOD checks the remainder after division by 2. The rule is true on even-numbered worksheet rows, so row 2 is shaded and row 3 is not. If the selected range begins on a different row, the pattern still alternates, but which row receives the first shade depends on its worksheet row number.

Compare values with a threshold in another cell: If actual amounts are in B2:B20 and a threshold is in E1, select B2:B20 and use =B2>$E$1. The relative B2 reference moves down the list; $E$1 stays fixed. With E1 set to 100, a value of 125 is highlighted and a value of 85 is not. This makes it easy to change the threshold without editing the conditional-formatting rule.

Formula rules versus built-in rules

Built-in conditional-formatting rules are usually quicker when the condition is simple. Highlight Cells Rules can identify values greater than a number, text that contains a phrase, dates in a selected period, or duplicate values. Data Bars, Color Scales, and Icon Sets provide visual comparisons across a range without requiring a custom formula.

Use a formula rule when the condition involves more than one cell, a combination of tests, or formatting a row based on one of its fields. For example, a built-in “Greater Than” rule can compare a cell with a fixed value, but a formula can compare each value with a threshold stored in another cell. Formulas also let you combine conditions with functions such as AND or OR. Choosing the simplest rule that expresses the requirement makes later maintenance easier.

Common problems and how to fix them

  • The wrong rows are highlighted: Check the top-left cell of the “Applies to” range and make sure the formula’s relative row reference starts on that same row.
  • Every row uses one comparison cell: Check whether a dollar sign has locked a reference that should move. Use a mixed reference such as $C2 when the column should stay fixed but the row should change.
  • No cells are formatted: Test whether the condition is actually true for the sample data. Check text spelling, dates stored as real Excel dates, and whether a number is stored as text.
  • A rule seems to be ignored: Open Manage Rules and confirm the “Applies to” range includes the cells in question. Also check whether another rule takes precedence or uses Stop If True.
  • The format is hard to see: Choose a fill or font color that contrasts with existing styles, and check whether another rule or a table style is affecting the same cells.

Rules can overlap, and more than one can be true for the same cell. Excel’s Rules Manager lists the rules and lets you change their order; higher-priority rules can affect which formatting appears. Stop If True can prevent lower-priority rules from being evaluated for a cell when an earlier rule applies. If the result is unexpected, inspect the rule order and the “Applies to” ranges as well as the formulas.

Frequently Asked Questions

Does a conditional-formatting formula have to start with an equals sign?
Yes. Enter it as an Excel formula beginning with =, and make sure its result is a logical TRUE or FALSE. In the rule editor, a formula that calculates a number or text can also be interpreted as true or false in some contexts, but a clear logical test is easier to understand and troubleshoot.

Why do I use a formula like B2 when formatting a whole range?
The formula is written for the first cell in the applied range. Excel adjusts relative references for each cell it checks. If a range starts at B2, a reference to B2 checks the first cell; as the rule moves down, it checks B3, B4, and the remaining cells.

Can conditional formatting highlight a whole row?
Yes. Select the full row range you want formatted and use a mixed reference to the column containing the condition. For example, a fixed column with a relative row lets Excel check the status in that row while applying the chosen format across the selected row cells.

Can I use AND or OR in a conditional-formatting formula?
Yes. These are standard Excel functions and are useful for combining tests. AND returns TRUE only when every condition is true; OR returns TRUE when at least one condition is true. Keep references aligned with the first cell of the applied range so that the tests evaluate the intended row or cell.

Does conditional formatting change the cell’s value?
No. It changes the cell’s appearance only. The value remains available to formulas, sorting, filtering, and other worksheet operations; changing or removing the rule changes the display, not the stored data.

Choosing a clear, maintainable rule

Start by stating the condition in plain language—for example, “highlight each row where status is Open and the due date has passed.” Identify the cells that should receive the format, then identify which cells supply the test values. From there, choose the simplest built-in rule or formula that expresses the condition. Carefully set relative and absolute references, verify the “Applies to” range, and check the result against a few known examples, including one that should match and one that should not.

A well-built rule is specific enough to be useful but simple enough for another person to understand. Use meaningful ranges, avoid unnecessary overlapping rules, and document unusual logic in a nearby note if others maintain the workbook. These small checks make conditional formatting more predictable as a worksheet grows or its data changes.

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.