To use a custom formula in Excel conditional formatting, select the cells to format, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula that returns TRUE for the cells that should receive formatting, choose a format, and select OK.
What a custom formula does
A custom-formula rule lets you decide exactly when Excel applies a color, font, border, or number format. Instead of relying on a built-in test such as “greater than” or “duplicate values,” you write a normal worksheet formula that evaluates each cell or row in the rule’s Applies to range.
The formula does not alter cell contents or calculate a value for display. Excel uses its result as a condition: when the result is TRUE, the selected formatting appears; when it is FALSE, that rule does not apply. The underlying values remain available for calculations, sorting, and filtering. Conditional formatting is therefore useful for drawing attention to records without adding helper columns or changing the data.
Custom formulas can use ordinary Excel functions and logical tests, including AND, OR, COUNTIF, TODAY, and SEARCH. A rule can evaluate the cell itself, another column in the same row, a fixed threshold elsewhere on the sheet, or a combination of conditions.
Create a formula-based rule in Excel
- Select the cells you want the rule to affect. To shade whole records based on a value in one column, select the entire data area, not just the column containing the test value.
- On Excel for Microsoft 365 for Windows, choose Home > Conditional Formatting > New Rule.
- Select Use a formula to determine which cells to format.
- Type the formula in the rule description box. Start it with an equals sign, as you would in a worksheet cell.
- Select Format, choose the desired number format, font, border, or fill, and select OK. Select OK again to create the rule.
To review or adjust a rule later, choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, check the “Show formatting rules for” setting: choose the current selection, current worksheet, or another available scope as appropriate. The Applies to field shows the range affected. Correcting this range is often as important as correcting the formula.
Excel for Mac has the same basic formula-rule workflow through Home > Conditional Formatting > New Rule, though the dialog layout can differ. Excel for the web also offers conditional formatting, but some rule-management controls can differ from the desktop version. If a particular option is missing, open the workbook in the desktop app.
Understand relative and absolute references
Reference style is the key to making a rule work across a range. Excel evaluates the formula relative to the top-left cell of the Applies to range, then adjusts relative references as it checks other cells. A dollar sign fixes a column, a row, or both. This is the same reference behavior used when copying ordinary formulas.
- Relative reference: A2 can shift to A3, A4, and so on as Excel evaluates successive rows.
- Fixed column: $A2 keeps checking column A but changes the row.
- Fixed row: A$2 changes the column but keeps checking row 2.
- Fixed cell: $F$1 always points to F1, which is useful for a shared threshold.
For example, with scores in B2:B20, a cutoff in F1, and the rule applied to A2:D20, use:
=$B2>=$F$1
Here, $B fixes the score column while the row changes, so each record is tested against its own score. $F$1 keeps the threshold fixed. If B2 contains 92 and F1 contains 85, the condition is TRUE and the formatting appears across A2:D2. If the rule were applied only to B2:B20, only the scores would be formatted.
If the formula seems to check the wrong cells, first compare its references with the top-left cell of the Applies to range. For instance, a formula written for a range beginning at A2 may shift incorrectly if the range actually begins at A1.
Practical custom-formula examples
Highlight overdue open items. With status values in C2:C100, due dates in D2:D100, and the rule applied to A2:D100, use:
=AND($C2="Open",$D2<TODAY())
AND requires both tests to be true. A row marked Open with a due date before today is highlighted; a closed item or a future due date is not. TODAY updates with the current date when Excel recalculates, so this is a moving rule rather than a fixed snapshot. If blank due dates should never be treated as overdue, add a nonblank test:
=AND($C2="Open",$D2<>"",$D2<TODAY())
Find repeated identifiers. With IDs in A2:A20 and the rule applied to that same range, use:
=AND(A2<>"",COUNTIF($A$2:$A$20,A2)>1)
COUNTIF counts how many times the current ID appears in the full list. A count above one marks every occurrence of a repeated ID. The first test prevents empty cells from being highlighted as duplicates. If the range grows, update both the Applies to range and the COUNTIF range, or use an Excel Table so the data and rule can expand together when rows are added.
Highlight a row when a category matches. With category names in C2:C50 and the rule applied to A2:D50, use:
=$C2="Urgent"
This compares the category in column C with the text Urgent. The fixed column means every cell in a given row checks the same category cell, while the row reference changes from record to record.
Flag text containing a word. With notes in B2:B100 and the rule applied to B2:B100, use:
=ISNUMBER(SEARCH("urgent",$B2))
SEARCH looks for the text without distinguishing uppercase and lowercase letters. It returns a position when it finds the word and an error when it does not; ISNUMBER converts that result into TRUE or FALSE for the rule. This finds “urgent” even when it appears inside a longer note.
Choose a formula that matches the range
Before writing a formula, decide what one formatted cell represents. Are you highlighting a single value, every cell in a row, or cells that meet a condition elsewhere? That decision determines both the selection and the references.
- For a value-only highlight, select the value cells and refer to the first cell in that selection.
- For whole-row highlighting, select all the record columns and usually lock the test column while leaving its row relative.
- For a shared limit, put the limit in a worksheet cell and use an absolute reference so it stays fixed.
- For several conditions that must all be true, use AND. For alternatives where any condition can be true, use OR.
For example, with project names in A2:A30, status in C2:C30, and budget in D2:D30, suppose a row should stand out when its status is High or its budget is above 1,000. Apply the rule to A2:D30 and use:
=OR($C2="High",$D2>1000)
OR makes either test sufficient. If both conditions must be met, replace OR with AND. Writing the condition in ordinary language first—“status is High or budget exceeds 1,000”—can help prevent accidental changes to the intended logic.
Formula rules compared with built-in rules
Built-in rules are faster for common, straightforward checks. Home > Conditional Formatting includes options such as Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets. Use those when their settings describe the condition clearly; a custom formula is not automatically better simply because it is more flexible.
Choose a formula rule when the condition involves another column, a combination of tests, a comparison with a cell elsewhere on the sheet, or a pattern not offered by the built-in dialogs. For example, a built-in “Greater Than” rule can highlight scores above a fixed number, while a formula can compare each score to a threshold stored in a cell or highlight a complete row based on that score.
These rule types can coexist, but multiple rules may apply to the same cell. In Manage Rules, rule order and the Stop If True option can affect which formatting is visible. If a cell looks different from what one rule suggests, inspect all rules applying to it and their order rather than assuming the formula alone is responsible.
Common problems and reliable fixes
- The formula is valid but the wrong rows light up. Check whether the Applies to range begins on the row assumed by the formula. Then inspect which references need dollar signs. A locked row that should change, or a relative column that should stay fixed, can shift the test.
- Nothing is highlighted. Confirm that the formula starts with an equals sign, that text is spelled and quoted as intended, and that the chosen format is visually noticeable. Also check whether the formula returns TRUE for a sample row when entered in a worksheet cell.
- Blank records are flagged. Add an explicit nonblank condition where appropriate. Blank cells, zero values, and formulas returning an empty text string are not always interchangeable, so decide which kind of “empty” your data contains.
- The rule works only on the first row. A fixed row reference may be preventing the test from moving down the list. For row-by-row tests, the row is usually relative; for whole-row formatting, the test column is commonly fixed.
- A date rule changes unexpectedly. TODAY is dynamic and depends on recalculation. If the desired rule is based on a fixed date, use a date stored in a cell or a fixed date condition rather than expecting TODAY to preserve the date when the rule was created.
One useful diagnostic is to temporarily enter the condition in a blank worksheet column for a few sample rows. Because an ordinary formula displays TRUE, FALSE, or an error, it can reveal whether the issue is the condition itself or the conditional-formatting range and formatting settings. Remove the temporary test column when finished.
Frequently Asked Questions
Does a conditional-formatting formula have to return TRUE or FALSE?
It should evaluate to a logical result for the intended condition. Formulas that return TRUE apply the rule’s formatting, and formulas that return FALSE do not. Many expressions, including comparisons and functions such as AND, produce those logical results directly.
Can I use a formula to highlight an entire row?
Yes. Select the full row range in your data, then write the test using a reference to the relevant column. Usually you lock that column but leave the row number relative, so each record is tested against its own value.
Why does Excel shift my references?
Excel adjusts relative references for each cell in the Applies to range. Use dollar signs to lock the column, row, or cell that must not move. Also make sure the formula is written for the range’s top-left cell, since that is the starting point for the adjustments.
Can one custom rule use more than one condition?
Yes. AND combines tests that must all be satisfied, while OR combines alternatives where at least one must be satisfied. You can also nest logical functions when the condition requires more than a simple pair of tests.
Does conditional formatting change the value in a cell?
No. It changes how Excel displays a cell when a rule is true; it does not replace, edit, or remove the cell’s underlying value. Use a worksheet formula or data-editing method if you need to change the actual contents.
Related
- Free Cash Flow Template for Excel: Setup and Use
- Cash Flow Tracker Excel Template: Build and Use It
- Cash Flow Statement Template Excel: Build and Use
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Length of Service Formula: Calculate Years and MonthsIf the start date is in B2 and the date to measure service through is in C2, use…
- SAS String Functions: A Practical GuideIn SAS, string functions are character functions used to inspect, extract, join, clean, and transform text; there is…
- COUNT Function in Excel: Examples and Common UsesTo count numeric values in Excel, use =COUNT(B2:B10) when the values are in B2:B10. COUNT includes numbers and…
- Weighted Average in Excel: Formula and ExamplesTo calculate a weighted average in Excel, multiply each value by its weight, add those products, and divide…
- Excel Time Card Formula: Track Hours and OvertimeFor a basic Excel time card, enter a start time in B2, an end time in C2, and…
- Calculate Age in Months in Excel: Formulas and TipsTo calculate completed age in months in Excel, put a birth date in A2 and enter =DATEDIF(A2,TODAY(),"m") in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.