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 evaluates to TRUE for the cells that should receive the color, choose a format, and confirm. For example, with values in B2:B100, the formula =B2<0 colors negative values when you set the rule’s Applies to range to B2:B100.
What a formula-based color rule does
Conditional formatting applies a visual format when a specified condition is met. A formula rule gives you more flexibility than built-in options such as “Greater Than” or “Text That Contains”: it can test multiple cells, combine conditions, and use information in one column to format another. The formula does not change the cell’s value. It tells Excel when to apply a fill color, font style, border, or another supported format.
Excel evaluates the formula for each cell in the rule’s Applies to range. A result of TRUE means the formatting is applied; FALSE means it is not. The key is to write the formula as if it were being evaluated for the first cell in that range. Excel then adjusts relative references as it evaluates the other cells. That reference behavior is why a correct formula can still produce surprising results if the selected range and formula do not start on the same row.
Color cells according to their own values
For a straightforward rule, make the formula refer to the top-left cell of the range. Suppose B2:B100 contains amounts and you want negative amounts shaded red:
- Select B2:B100.
- Choose Home > Conditional Formatting > New Rule.
- Select Use a formula to determine which cells to format.
- With the selected range B2:B100, enter
=B2<0. - Choose Format, select a fill color on the Fill tab, and click OK. Click OK again to create the rule.
For example, if B2 contains -12, the test is TRUE and Excel formats B2; if B3 contains 8, the test is FALSE and B3 is not formatted by this rule. You can change the test to suit the data, such as checking whether a value equals a target or exceeds a threshold. If the range starts at B5 instead, use B5 in the formula. The formula’s starting reference should correspond to the first cell Excel formats.
Color an entire row based on one column
A common use is highlighting a whole record when a status cell meets a condition. Suppose a task list occupies A2:D20, with its status in column D, and you want the entire row colored when the status is Overdue. Select A2:D20, create a formula rule, and enter =$D2="Overdue". Choose the desired format, then check that Applies to is $A$2:$D$20 in Conditional Formatting > Manage Rules.
The dollar sign before D locks the column, so Excel always checks the status column. The row number is not locked: as the rule moves down the range, Excel checks D3 for row 3, D4 for row 4, and so on. If D2 contains Overdue, the test is TRUE and A2:D2 receives the format. If D3 contains Complete, that row does not meet this rule. Text comparisons in Excel formulas are generally case-insensitive, so “overdue” also matches “Overdue.”
Understand relative and absolute references
Reference choice controls which cells Excel checks as it applies a rule. A relative reference such as B2 adjusts across the range. An absolute reference such as $B$2 stays fixed. A mixed reference such as $D2 locks the column while allowing the row to change. For a row-based rule, a mixed reference is often what you need: each row should check the same status column, but its own row’s status.
Use a fixed reference when every cell in the range should be compared with one control cell. For example, if A2:A20 contains figures and F1 contains a target, select A2:A20 and use =A2>$F$1. Here A2 changes as Excel evaluates each row, but F1 remains the target for every comparison. If A2 is 15 and F1 is 10, the condition is TRUE, so A2 is formatted. If A3 is 8, its comparison is FALSE. In the rule editor, you can click the reference in the formula and press F4 to cycle through reference styles in Windows Excel; on a Mac, the equivalent shortcut is usually Command+T, though keyboard settings can affect it.
Useful formula examples
Conditional formatting formulas can handle practical checks beyond a single comparison. In each example, select the stated range before creating the rule, and choose a format after entering the formula.
- Highlight duplicate entries: With names or IDs in A2:A20 and Applies to set to A2:A20, use
=COUNTIF($A$2:$A$20,A2)>1. A value appearing at least twice meets the condition, so every occurrence of that repeated value is highlighted. - Flag overdue dates while ignoring blanks: With due dates in C2:C50 and Applies to set to C2:C50, use
=AND(C2<>"",C2<TODAY()). A date earlier than today is highlighted; a blank cell is not. For example, if today is July 10 and C2 contains July 9 of the same year, the test is TRUE. TODAY updates when Excel recalculates, so the result can change from day to day. - Highlight a row when a number is below a limit: With records in A2:D20, the amount in column C, and Applies to set to A2:D20, use
=$C2<50. If C2 is 42, the condition is TRUE and the first row is formatted; if C3 is 75, the second row is not.
In formulas that combine tests, AND requires every included condition to be TRUE, while OR requires at least one. COUNTIF counts matches in a range. These are regular worksheet functions that can also be used in conditional formatting rules. Keep the reference pattern matched to the Applies to range, and test the rule against a few known values before relying on it across a large table.
Set the range, order, and format correctly
The formula is only part of the rule. In Conditional Formatting > Manage Rules, select the relevant worksheet from the “Show formatting rules for” list if needed, then inspect Applies to. A range that is too short leaves later rows unformatted; one that starts on the wrong row can make the formula’s relative references misalign. You can edit the formula, format, and range in this manager rather than deleting and rebuilding the rule.
When more than one rule applies to a cell, rule order can affect the visible result. Excel lists rules by priority in Manage Rules, and you can move them up or down. If a higher-priority rule has Stop If True selected, lower-priority rules are not evaluated for cells where that rule is true. Without that setting, multiple rules may contribute formatting, although overlapping formats can make the final appearance depend on which properties each rule changes. If the color you expect does not appear, review the other rules affecting the same cells as well as the formula and range.
Formula rules versus built-in color options
Use a built-in conditional formatting command when the test is simple and the option directly describes it. For example, Home > Conditional Formatting > Highlight Cells Rules offers common comparisons, while Color Scales show relative values across a range. These options are quicker when you only need a standard threshold or visual scale.
Choose a formula rule when the condition depends on another cell, needs a combination of tests, or must format a complete row. A formula rule also lets you choose a specific format through the Format dialog. Neither approach permanently paints a cell: conditional formatting remains linked to its condition. If you want a color that will not change when values change, use the regular fill color instead, but remember that a manually applied fill does not automatically reflect the data.
Common problems and how to fix them
- Every row gets the same result: Check for a fully locked reference, such as $D$2, when each row should check its own status. Use $D2 for a range spanning multiple rows.
- The wrong row is highlighted: Make sure the formula refers to the top-left cell of the Applies to range. If Applies to starts at A3, the corresponding row reference should generally start at row 3, not row 2.
- The rule never activates: Check that the formula begins with an equals sign, has balanced parentheses and quotation marks, and compares compatible values. A date stored as text, for example, may not behave like a genuine Excel date.
- The format is hidden: Another rule may have higher priority, Stop If True may be enabled, or the chosen font and fill colors may be difficult to distinguish. Check Manage Rules and the Format settings.
- Only some new rows are colored: Expand Applies to to include the new rows, or use an Excel table when appropriate so the range can grow with added records. Verify the rule after adding data.
For Windows, the menu paths above use the Microsoft 365 ribbon. Excel for Mac has the Conditional Formatting commands on its Home tab, though dialog details and keyboard shortcuts can differ. Excel for the web also supports conditional formatting, but some rule-management and editing controls can differ from the desktop application. If a workbook is shared across platforms, check the rule in the version people will use.
Frequently Asked Questions
Can conditional formatting change a cell’s fill color with a formula?
Yes. Create a formula-based conditional formatting rule and choose a fill color in the Format dialog. The formula decides when the fill appears; it does not assign a permanent color or alter the cell’s value.
Does the formula have to return TRUE or FALSE?
That is the clearest approach. A comparison such as a cell being less than zero returns TRUE or FALSE. Excel evaluates the result to determine whether to apply the rule, so build the formula around the condition you want to test.
Can I color a row based on a cell in another column?
Yes. Select the full row range you want formatted and write a formula that locks the relevant column but leaves the row relative. For example, for A2:D20 and a status in D, =$D2="Overdue" checks each row’s status and can format the entire matching row.
Why does my formula work in a worksheet cell but not in conditional formatting?
Often the formula is aligned to a different starting cell than the Applies to range, or its references are locked differently than intended. Also check for unsupported references, mismatched data types, and other rules that override the visible format. Test the formula against the first cell in the range.
Can one rule use more than one condition?
Yes. Use AND when all conditions must be met, or OR when any one condition is enough. For example, you can check that a date is not blank and is earlier than today. Keep each reference aligned with the top-left cell of the range being formatted.
Related
- Change Block Capitals to Lowercase in Excel
- Cell in Microsoft Excel: Meaning, Types, and How to Use It
- Count Numbers vs Count in Excel PivotTables
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for High and Low Values: MIN and MAXIn Excel, use =MIN(B2:B10) to find the lowest number in a range and =MAX(B2:B10) to find the highest.…
- Excel Filter Duplicates Without Deleting Data SafelyYes. In Excel for Microsoft 365 on Windows, add a helper column that identifies repeated values, then choose…
- How to Make Excel Sort Dates Correctly, Oldest FirstTo make Excel sort dates correctly, first ensure the date cells contain real Excel dates rather than text,…
- Identify Offsetting Entries in Excel With Formulas and ChecksTo flag entries with an equal and opposite amount, put the date in column A, account in B,…
- Using ROUNDUP in Excel: 10 Practical Formula ExamplesTo round a number up in Excel, use ROUNDUP with the number and the number of digits to…
- Tableau IF Function: Syntax and Practical ExamplesIn Tableau, IF is a conditional expression that returns a value when a condition is true, with optional…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.