Conditional Formatting Custom Formula: Excel Rules

In Excel for Microsoft 365 on Windows, a conditional formatting custom formula is a TRUE-or-FALSE formula that decides which cells receive a format. Select the cells to format, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter a formula beginning with =, then choose the formatting.

How a custom formula rule works

Excel evaluates the formula for each cell in the rule’s “Applies to” range. When the formula is TRUE, Excel applies the chosen fill, font, border, or other available format; when it is FALSE, that rule does not apply its format. A formula can test the cell being formatted, a different cell in the same row, or a fixed value elsewhere on the sheet.

The important detail is that Excel adjusts relative references as it evaluates different cells. If a rule applies to A2:D100 and its formula refers to D2, Excel evaluates the reference as D2 for the first row, D3 for the next row, and so on. That lets one rule format a whole row based on a value in one column. The formula is not copied into the worksheet cells, and it does not change their contents.

Custom formulas are useful when the built-in conditional formatting options cannot express the relationship you need. For example, you can format a row based on its status, compare values in different columns, or highlight a date only when it falls on a weekend.

Create a custom formula rule in Excel

Select the cells that should receive formatting. The top-left cell in the selection matters because relative references in the formula are interpreted from that starting position.
On the Windows ribbon, choose Home > Conditional Formatting > New Rule.
Select “Use a formula to determine which cells to format.”
Enter a formula in the formula box. Start with an equals sign and make the formula return TRUE or FALSE.
Choose Format, set the appearance you want, and select OK. Confirm the rule to apply it.

Afterward, inspect the rule’s range if the result is unexpected. Choose Home > Conditional Formatting > Manage Rules to review or edit rules, including their formulas and “Applies to” ranges. In the Rules Manager, check that the intended worksheet is selected in the “Show formatting rules for” list.

These paths describe Excel for Microsoft 365 on Windows. Excel for Mac also provides conditional formatting from the Home tab, though dialog wording and layout can vary by release. Excel for the web supports many conditional formatting tasks, but its editing experience and available controls can differ; use the desktop app if a complex rule is difficult to create or manage there.

Relative and absolute references

Dollar signs control how a reference moves when Excel evaluates the rule across its range. A relative reference such as A2 can change both its column and row. An absolute reference such as $A$2 stays fixed. Mixed references, such as $A2 and A$2, lock only the column or the row.

For a rule applied to A2:D100 that checks the status in column D, use =$D2="Overdue". The dollar sign before D keeps the test in the status column as the rule moves across columns A through D. The row number remains relative, so each row checks its own status. If you instead use =$D$2="Overdue", every row checks only D2, which is usually not what a row-based rule needs.

A reliable habit is to write the formula as if it were being evaluated for the top-left cell of the “Applies to” range. For example, if the range starts at B5, a formula that should test the current cell generally needs to refer to B5, not B2. A mismatch between the starting cell and the reference is a common cause of highlighting that appears shifted by one or more rows.

Useful custom formula examples

Each example below states the assumed layout and target range. Enter only the formula shown into the custom formula box, then choose a format that makes the result easy to read.

Highlight complete rows by status. With task data in A2:D100 and status values in D2:D100, select A2:D100 and use =$D2="Overdue". Rows whose status cell says Overdue are formatted; a row marked Done is not.
Compare each amount with a target. With amounts in B2:B50 and one target value in F1, select B2:B50 and use =B2>$F$1. The dollar signs keep the target fixed while Excel checks each amount. If F1 contains 100 and B2 contains 120, B2 meets the condition; an amount of 90 does not.
Find repeated identifiers without marking blanks. With identifiers in A2:A100, select A2:A100 and use =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1). The COUNTIF test identifies identifiers appearing more than once, while the first test prevents empty cells from being highlighted as duplicates.
Highlight weekend dates. With actual Excel dates in A2:A100, select A2:A100 and use =WEEKDAY(A2,2)>5. With the return type 2, WEEKDAY numbers Monday as 1 and Sunday as 7, so values above 5 are Saturday or Sunday. Text that merely looks like a date may not behave as a real date value.

These examples use comma separators and English function names, as shown for Excel formulas here. Some regional settings use a different argument separator in the formula box. If Excel rejects an otherwise correctly structured formula, check the separator expected by your installation.

When to use a formula instead of a built-in rule

Excel’s built-in rules are often faster for straightforward tasks, such as highlighting cells greater than a number, finding duplicate values, or applying a color scale. They provide guided options and reduce the chance of reference errors. A custom formula is the better choice when the condition depends on multiple tests, another column, or a relationship that the standard dialog does not offer.

For example, a built-in “Greater Than” rule can highlight amounts above a fixed number. A formula rule is more flexible when each amount must be compared with a different target in the same row, or when a whole row should be highlighted based on one status cell. Choosing the simplest rule that expresses the requirement makes a workbook easier for other people to maintain.

Before writing a complex formula, identify three things: which cells the rule formats, which cell or cells the condition tests, and which references must stay fixed. If those are clear, the formula often becomes much simpler. You can also test the logical expression in an unused worksheet cell, then reuse it in the conditional formatting rule after confirming it returns TRUE for a case that should be highlighted and FALSE for one that should not.

Rule order and formatting conflicts

A cell can be covered by more than one conditional formatting rule. Excel’s Manage Rules window shows the rules and their order, and it lets you adjust that order. When rules set different formatting attributes, more than one rule’s formatting may be visible; when they compete over the same attribute, rule order can affect the result.

The “Stop If True” option can prevent lower-priority rules from being evaluated for a cell when a rule above it is true. Use it only when that behavior is intended. For instance, a workbook might use one rule for an urgent status and another for a general overdue status. Decide which appearance should take precedence, arrange the rules accordingly, and verify the result on cells that meet both conditions.

Common problems and how to fix them

The highlight is one row off. Compare the first row of “Applies to” with the row number in the formula. A rule covering A2:D100 should generally use row 2 as its starting reference.
Every row gets the same result. Check for an accidentally absolute row reference, such as $D$2, when the rule should check D2, D3, and later rows. Keep the column fixed but leave the row relative when evaluating a status down a list.
The rule works in one column but not across a row. Check whether the tested column needs a dollar sign. For a range spanning several columns, $D2 keeps the test in column D; D2 would shift horizontally.
Nothing is formatted. Test the formula’s logic against the actual values, inspect “Applies to,” and check for extra spaces or different text in the data. Also confirm that the selected format is noticeable and that another rule is not overriding it.
Excel reports a formula error. Confirm the leading equals sign, balanced parentheses, quotation marks around text, and the argument separator expected by your regional settings. Function names and cell references must also be valid.

Conditional formatting changes appearance, not the underlying value. A date that appears red is still the same date for calculations, and a number highlighted as a duplicate is not removed or corrected. If the worksheet needs a permanent status or a value that can be filtered independently of formatting, use a separate worksheet formula or data field for that purpose.

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 =. The result should evaluate to TRUE or FALSE for each cell being checked.

Can a custom formula format an entire row?
Yes. Select the row-wide range and use a mixed reference to lock the test column while allowing the row to change. For status in D2:D100 and formatting across A2:D100, =$D2="Overdue" checks the status for each row.

Can the formula refer to another worksheet?
It can refer to cells on another worksheet in the same workbook using a valid sheet reference. References to another workbook are more limited and are not a dependable basis for a conditional formatting rule; keep the needed values in the same workbook when possible.

Can I use AND or OR in a rule?
Yes. These functions let one rule combine conditions. For example, a formula can require both a nonblank cell and a duplicate count above one. Make sure each test refers to the correct row and that fixed ranges use dollar signs where needed.

Why does a formula show TRUE in a cell but not format the range?
The test cell may not correspond to the top-left cell of the rule’s “Applies to” range. Check that the formula’s references align with that starting cell, then inspect the applied range and any higher-priority rules in Manage Rules.

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.