Excel Conditional Formatting Formula Examples That Work

Excel conditional formatting formulas let you highlight cells or entire rows when a logical test is true. In Microsoft 365 for Windows, select the range, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter a formula such as =$C2="Overdue", and choose a format.

What formula-based conditional formatting does

Conditional formatting changes a cell’s appearance when its value meets a rule; it does not change the underlying value. A formula rule is useful when the built-in rules are too limited, when the condition depends on another cell, or when you want to format a whole row based on one column. Excel evaluates the formula for the cells in the rule’s “Applies to” range and applies the chosen format wherever the result is TRUE.

A formula rule needs to return a logical result such as TRUE or FALSE. Comparisons like greater than, equal to, and not equal to return those results naturally. Functions such as AND, OR, COUNTIF, and MOD can combine tests or produce a result that Excel can use. The formula normally begins with an equals sign, just like a worksheet formula, but it is entered in the conditional-formatting rule dialog.

Conditional formatting is best for visual signals: spotting late work, unusual values, duplicates, gaps, or patterns. If you need to calculate a status that other formulas will use, put that calculation in a worksheet cell instead. A visual rule is not a substitute for data validation, and a highlight alone does not prevent someone from entering an incorrect value.

How to create a formula rule

Use this sequence in Excel for Microsoft 365 on Windows:

Select the cells you want the formatting to affect. For a whole-row rule, select the full table area, not just the column containing the test.
Choose Home > Conditional Formatting > New Rule.
Select Use a formula to determine which cells to format.
Enter the formula, select Format, choose a fill, font, border, or number format, and select OK.
Review the range in Home > Conditional Formatting > Manage Rules. Set “Show formatting rules for” to the relevant worksheet if the rule is not visible.

Write the formula as if it is being evaluated for the top-left cell of the selected range. If the selected range begins at A2, references should normally be written with row 2 as the starting row. Excel adjusts relative references as it checks other cells. On Excel for Mac, the rule is also created from Home > Conditional Formatting > New Rule; dialog wording and layout can vary slightly by release. Excel for the web has conditional-formatting controls too, although its dialog and available options may not match the desktop dialog exactly.

Examples for thresholds and target values

To flag values below a target stored in a separate cell, use a formula rule rather than typing the target directly into the rule. Assume monthly results are in D2:D20 and the target is in F1; enter this rule after selecting D2:D20:

With results in D2:D20 and a target of 100 in F1, the formula is =D2<$F$1. A value of 82 in D2 is highlighted because 82 is less than 100; a value of 100 is not highlighted because it is not less than the target.

The dollar signs lock the target reference so it remains F1 as the rule checks D3, D4, and the rest of the range. The D2 reference is relative, so it changes to D3 for the next cell. To flag values that meet or exceed the target, use =D2>=$F$1 instead. For a fixed threshold, you can put the number in the formula, but a target cell is easier to update and makes the rule’s intent visible.

You can also combine conditions. Suppose column B contains a score and column C contains a review status. Select B2:B50 to highlight scores below 70 only when the review is marked “Final”; the rule formula is =AND(B2<70,C2="Final"). If B2 is 64 and C2 contains Final, it highlights. If the score is 72, or the status is Draft, it does not. Keep in mind that text in a formula must match the cell text, including spaces, though comparisons are generally not case-sensitive.

Highlight an entire row from one column

Row-based formatting is a common reason to use a formula. Assume a table occupies A2:F50, and column C contains task status. Select A2:F50, then create a rule using this formula:

With statuses in C2:C50 and the selected table beginning on row 2, use =$C2="Overdue". If C2 says Overdue, all six cells in row 2 receive the chosen format; if C3 says Complete, row 3 does not. The dollar sign fixes the status column at C while leaving the row number relative. That lets Excel test C2 for row 2, C3 for row 3, and so on.

For alternating row shading, select A2:F50 and use =MOD(ROW(),2)=0. Since the first selected row is row 2, this formula shades even-numbered worksheet rows, including row 2, 4, and 6. If the selected range begins on a different row, the pattern may start differently; adjust the formula or use a table style if you want banding that automatically follows the table’s own row sequence.

Find duplicates, missing entries, and dates

Conditional formatting can identify repeated values without deleting or changing them. Assume product IDs are in A2:A100 and select that range:

With IDs in A2:A100, use =COUNTIF($A$2:$A$100,A2)>1. If the ID in A2 appears at least twice in the range, its cell is highlighted. The dollar signs keep the full lookup range fixed while A2 moves as Excel checks each ID. This marks every occurrence of a repeated ID, not just later copies.

To flag a missing required entry only when a row has otherwise been started, assume names are in A2:A100 and email addresses are in B2:B100. Select B2:B100 and use =AND($A2<>"",B2=""). A row with a name in A2 and a blank B2 is highlighted; a completely empty row is not. This can help with review, but it does not block incomplete data entry.

For upcoming due dates, assume due dates are in B2:B100 and status is in C2:C100. Select the rows or date cells you want to format, starting at row 2, and use =AND($B2>=TODAY(),$B2<=TODAY()+7,$C2<>"Complete"). A date five days from today with a status other than Complete is highlighted. A date already in the past is excluded, as is a completed task. TODAY updates as Excel recalculates, so the highlighted window moves over time; it is a dynamic reminder, not a fixed historical record.

Relative and absolute references: the key to correct rules

Most formula-rule mistakes come from references that move differently than intended. A relative reference such as A2 adjusts across the applied range. An absolute reference such as $A$2 stays fixed. A mixed reference such as $A2 locks the column but lets the row change, while A$2 locks the row but lets the column change.

For checking each cell in a selected column, use a relative reference such as D2.
For checking a fixed threshold elsewhere, lock both parts, such as $F$1.
For formatting full rows based on a status column, lock the column but not the row, such as $C2.
For a horizontal range where every cell in a row should compare against a fixed header row, a reference such as B$1 may be appropriate.

Before building a complex rule, test the logic in a spare worksheet cell using the same references. If the result is TRUE or FALSE for the first row, confirm it also behaves correctly when copied down. Then create the conditional-formatting rule with the matching top-left reference. Pressing F4 while editing a reference in a formula cycles through reference styles in Windows Excel; on many Mac keyboards, use Command+T to cycle reference types, though keyboard settings can affect function-key behavior.

Built-in rules, rule order, and common problems

Use built-in rules when they already express the condition. Home > Conditional Formatting includes Highlight Cells Rules for comparisons and text, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets. These are quicker for a simple “greater than 100” or duplicate-value highlight. Formula rules are more flexible for criteria involving multiple columns, custom date windows, or row-wide formatting. A formula rule does not automatically make the workbook faster or more accurate; it simply gives you more control over the test.

If a rule appears not to work, check these points:

Confirm that the “Applies to” range begins on the same row used in the formula. A rule applied from A2 should generally refer to row 2, not row 1.
Check whether a reference should be relative, absolute, or mixed. Locking the wrong row or column can make every cell test the same place.
Check for extra spaces, unexpected text, numbers stored as text, or dates stored as text. A date-looking string may not compare like a real Excel date.
Open Manage Rules and look for overlapping rules. Rule order and the “Stop If True” setting can affect which formats appear when multiple rules match.
Use a contrasting fill or font temporarily. The formula may be working while another rule or the existing cell style makes the difference hard to see.

Conditional formatting uses the workbook’s regional formula conventions. In some locales Excel displays semicolons rather than commas between function arguments; the examples here use the standard English comma syntax requested for Excel formulas. When sharing a workbook across locales, Excel generally translates formula separators for the user’s settings. Also note that formatting is not a filter: a highlighted row remains in the data, and copying values may not carry the rule in the way you expect unless you also copy the formatting.

Frequently Asked Questions

Can a conditional formatting formula refer to another worksheet?
Direct references to another worksheet are restricted in conditional-formatting formulas in some cases. A common workaround is to define a named range referring to the other sheet, then use that name in the rule. For straightforward rules, keep the comparison data on the same sheet or test the workbook’s exact setup before applying the rule widely.

Why does my whole column highlight instead of just one row?
Usually, the formula has an absolute row reference, such as $C$2, so every row checks the same status cell. For a row-based rule applied from row 2, use a mixed reference such as $C2. It keeps the status column fixed while allowing the row number to adjust.

Can conditional formatting use AND or OR?
Yes. AND returns TRUE only when all its tests are true; OR returns TRUE when at least one test is true. For example, combine a date test with a status test using AND when both conditions must apply. Make sure the references in each test align with the top-left cell of the applied range.

Does TODAY update a date-based highlight automatically?
TODAY returns the current date when Excel recalculates the workbook, so a rule using TODAY can move as days pass. Calculation settings and workbook recalculation affect when formulas refresh. If you need a fixed date for an audit or report, enter and reference a date in a cell instead of relying on a moving TODAY condition.

Can I use conditional formatting to prevent bad entries?
No. It only changes appearance after a value is entered. Use Data Validation when you need to restrict what users can enter, and use conditional formatting as a visual warning or review aid. Both can be used together, but they serve different purposes.

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.