Excel Conditional Formatting Based on a Formula

Yes. In Excel for Microsoft 365 on Windows, 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 or FALSE—for example, with status values in B2:B100 and the target range A2:D100, use =$B2="Paid" to format each row whose status is Paid.

What formula-based conditional formatting does

Conditional formatting changes a cell’s appearance when a condition is met. A formula rule lets you define that condition with an Excel formula, rather than choosing only from built-in rules such as greater than, duplicate values, or dates occurring. The format might change a fill color, font, border, or number format; it does not change the cell’s contents or the value used in calculations.

Excel evaluates the rule for the cells covered by the rule. When the formula evaluates as TRUE for a cell, Excel applies the chosen format. When it evaluates as FALSE, the rule does not apply. A formula can examine the cell itself, another cell on the same worksheet, or a combination of values and functions. This makes formula rules useful when the condition involves several columns, a custom threshold, or a relationship that a built-in rule cannot express.

For example, a sales list might need a whole row highlighted when its status is Paid, or a deadline cell might need attention only when it is overdue and the task is not complete. The formatting is a visual signal: the underlying data remains available to sort, filter, and calculate as usual.

Create a formula rule in Excel

To create a rule, first select the cells you want Excel to format. Then follow these steps in Excel for Microsoft 365 on Windows:

Choose Home > Conditional Formatting > New Rule.
Select Use a formula to determine which cells to format.
Enter the formula in the rule description box.
Choose Format, set the desired fill, font, border, or number format, and select OK.
Select OK again to create the rule. If needed, use Home > Conditional Formatting > Manage Rules to inspect its range or order.

For a row-based example, assume a table has names in A2:A100, status values in B2:B100, and other row details in C2:D100. Select A2:D100 before creating the rule, and use =$B2="Paid". Choose a fill color. Rows with Paid in column B will be formatted across columns A through D.

The dollar sign before B fixes the column reference, so each cell in a row checks column B. The row number has no dollar sign, so it adjusts as Excel evaluates the rule down the selected range: the first row checks B2, the next checks B3, and so on. If the selected range begins on a different row, make the formula’s row number match its first row.

On Mac, the Conditional Formatting commands are also on the Home tab, though the rule dialog’s layout can differ. Excel for the web has a browser interface; if a rule is difficult to create or manage there, open the workbook in the desktop app to use the full rule dialog.

How cell references control the result

Reference style is the most important detail in many formula rules. A relative reference changes as Excel evaluates different cells. An absolute reference, with dollar signs before both the column and row, stays fixed. A mixed reference fixes only the column or only the row.

A2 is relative: both the column and row can adjust.
$A$2 is absolute: both stay fixed.
$A2 fixes column A but lets the row change.
A$2 fixes row 2 but lets the column change.

To highlight a range based on a single control cell, use an absolute reference for that cell. For example, if the threshold is in F1 and values are in A2:A100, select A2:A100 and use =A2>$F$1. The formula checks each value against the fixed threshold. If F1 contains 100, a value of 125 meets the condition, while 80 does not.

A useful way to diagnose a reference problem is to click the top-left cell of the selected range and read the formula as if it were being evaluated for that cell. Then check whether the references should move across columns, down rows, or remain fixed. A formula can be syntactically valid but still point at the wrong cells because its dollar signs or starting row are wrong.

Practical formula examples

Conditional formatting formulas can combine comparisons, text checks, and standard worksheet functions. In each case, select the stated range before creating the rule, and make the formula correspond to its top-left cell.

Highlight values above a threshold: With numbers in A2:A100 and a limit in F1, apply the rule to A2:A100 using =A2>$F$1. If A2 is 125 and F1 is 100, A2 is formatted because the comparison is TRUE.
Flag overdue, unfinished items: With due dates in C2:C100 and status values in D2:D100, apply the rule to A2:D100 using =AND($C2<TODAY(),$C2<>"",$D2<>"Complete"). A row is formatted when its due date is before today, the date cell is not blank, and its status is not Complete.
Highlight duplicate entries: With values in A2:A100, apply the rule to A2:A100 using =COUNTIF($A$2:$A$100,A2)>1. A value that appears at least twice is highlighted, including its first occurrence. To ignore blank cells, use =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1) instead.
Highlight a row when a checkbox-linked cell is TRUE: With TRUE or FALSE values in E2:E100 and details in A2:D100, apply the rule to A2:D100 using =$E2=TRUE. This can mark rows selected by linked checkboxes or other formulas that return logical values.
Match either of two statuses: With status values in B2:B100 and rows in A2:D100, apply the rule to A2:D100 using =OR($B2="Urgent",$B2="Blocked"). The row is formatted if either status matches.

In the overdue example, TODAY() returns the current date and updates as the workbook recalculates. A deadline that is past today can therefore become highlighted without manually changing the rule. If the dates are stored as text rather than actual Excel dates, comparisons may not work as expected; check the cell values and convert them to real dates if necessary.

Choose the right range and rule design

Before building a rule, decide exactly what should change appearance. Selecting only the date column formats only those cells; selecting the full data rows formats every selected cell in a qualifying row. The formula’s reference should then be written relative to the top-left cell of the selected range.

For a new list, it is often helpful to apply the rule to the rows you expect to use, or to an Excel table’s data area. When rows are added, check that the conditional formatting range includes them. Excel may extend a rule as data is entered near the existing range, but the result depends on how the data and rule are arranged; do not assume every pasted or inserted row inherits the intended rule.

For conditions based on multiple tests, AND requires every included test to be true, while OR requires at least one. You can use these functions to express a business rule plainly, but keep formulas readable. If a rule becomes difficult to understand, helper columns that calculate a clear TRUE/FALSE result can make the workbook easier to maintain.

Rule order and formatting behavior

A cell can be covered by more than one conditional formatting rule. Excel evaluates applicable rules in the order shown under Home > Conditional Formatting > Manage Rules. When rules set different formatting attributes, their effects may combine. If two rules set the same attribute, such as different fill colors, the rule order and the Stop If True setting can affect which format appears.

Manage Rules also lets you check the Applies to range, edit a formula, change rule order, or remove a rule. The display can be limited to the current selection or show rules for the worksheet, so check the viewing option if a rule seems to be missing. If a row looks unexpected, inspect all rules applying to that cell, not just the one you most recently created.

Common problems and fixes

Every row highlights, or none do: Check the formula’s first-row reference and whether the rule is meant to return TRUE or FALSE. Test the logic in an ordinary worksheet cell if necessary.
The wrong row is highlighted: Make sure the formula row matches the top row of the Applies to range. For example, a range starting at row 2 generally needs references based on row 2.
The whole range checks one cell: A reference such as $B$2 is locked to one cell. For a row-by-row test, a mixed reference such as $B2 usually lets the row update.
A formula appears correct but dates do not format: Confirm the cells contain Excel date values rather than text that only looks like a date. Also check whether blank dates are being treated as zero in the comparison; an explicit nonblank test can prevent false alerts.
The format does not show: Confirm the rule’s Applies to range includes the cell, and inspect other rules that may set the same formatting. Check that the chosen format is visually distinct from the cell’s existing appearance.
Copied cells behave differently: Pasting cells can bring formatting rules with them or alter their ranges. Use Manage Rules to review the resulting rules and remove redundant or unintended entries.

One common misconception is that a formula rule writes a result into a cell. It does not: it only controls appearance. If you need a visible label such as Overdue or a value that can be filtered as text, use a worksheet formula in a helper column as well as, or instead of, conditional formatting.

Formula rules compared with built-in rules

Built-in conditional formatting is quicker for common tasks. Home > Conditional Formatting includes options such as Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets. Choose these when their available settings match the task—for instance, highlighting values greater than a number or showing relative values with color scales.

A formula rule is the better fit when the condition depends on another cell, more than one test, or a custom relationship. It can also format an entire row based on one column, which is not the same as applying a simple greater-than rule to each cell independently. The trade-off is that formulas require careful range selection and reference design. Start with a built-in rule for a standard comparison; use a formula when the built-in choices cannot express the logic you need.

Frequently Asked Questions

Can conditional formatting format an entire row based on one cell?
Yes. Select the full range of rows to format, then write a formula that locks the test column but leaves the row relative. For example, with statuses in B2:B100 and rows A2:D100, use =$B2="Paid".

Does a conditional formatting formula need to start with an equals sign?
Yes. Enter it as a normal Excel formula beginning with =, and use a test that evaluates to TRUE or FALSE. Excel uses that result to decide whether to apply the chosen format.

Can a formula rule refer to another worksheet?
Conditional formatting formulas have restrictions on direct references to other worksheets. A practical approach is to bring the needed value onto the same sheet in a helper cell or use a defined name where appropriate. Test the rule in your version of Excel, especially if the workbook will be shared.

Why does my formula work in a cell but not in conditional formatting?
The rule may use a different reference context from the worksheet cell where you tested it. Check the top-left cell of the Applies to range, adjust relative and absolute references, and verify that the formula returns the intended TRUE or FALSE for that starting cell.

Does conditional formatting change the cell value?
No. It changes only how the cell is displayed. To calculate or store a status that can be used in formulas, filters, or exports, put a formula or value in a worksheet cell separately.

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.