To create a formula-based conditional formatting rule in Excel for Microsoft 365 on Windows, select the cells to format and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. For example, if column B contains amounts in rows 2–20 and you want to highlight amounts over 100, enter =$B2>100, choose a format, and select OK.
What a formula rule does
A conditional formatting formula tells Excel when to apply a visual format, such as a fill color, font color, or border. Excel evaluates the formula for each cell in the rule’s Applies to range. When the result is TRUE, the selected format appears; when the result is FALSE, it does not. The rule changes how cells look, not the underlying values or the results of calculations that use those values.
Formula rules are useful when the built-in options do not express the condition you need, or when the formatting depends on several cells at once. A rule can test a value in the same row, compare a cell with a control value, or combine conditions. For a simple task such as highlighting values greater than a fixed number, a built-in Highlight Cells Rule may be quicker. For more flexible logic, choose a formula rule.
How to create a new rule
On Windows in Excel for Microsoft 365, start by selecting the cells the rule should format. Then follow these steps:
- Choose Home > Conditional Formatting > New Rule.
- Select Use a formula to determine which cells to format.
- Type the formula in the box under Format values where this formula is true.
- Choose Format, set the font, border, or fill you want, and select OK.
- Select OK again to create the rule.
For the opening example, assume the selected range is A2:C20 and the amounts to test are in B2:B20. The formula is =$B2>100. It highlights all three cells in a row when that row’s amount in column B is greater than 100. If you want to format only the amounts, select B2:B20 instead; the same formula still works because its reference points to the value in column B.
To review or change rules later, choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, you can edit a formula, change the Applies to range, adjust the order, or remove a rule. Excel for Mac has a similar Conditional Formatting command on the Home tab, though dialog layouts and some controls can differ. In Excel for the web, use Home > Conditional Formatting; the interface and rule-management options may differ from the desktop version.
Relative and absolute references
Reference style is the key to making a formula rule behave correctly across a range. A relative reference changes as Excel evaluates different cells. An absolute reference, marked with dollar signs, stays fixed. A mixed reference locks either the column or the row.
B2lets both the column and row adjust.$B$2keeps both the column and row fixed.$B2keeps column B fixed but lets the row change.B$2keeps row 2 fixed but lets the column change.
In a rule applied to A2:C20, =$B2>100 checks column B for each row. The dollar sign before B prevents the tested column from shifting as formatting is evaluated across columns A, B, and C. The row number has no dollar sign, so the rule checks B2 for row 2, B3 for row 3, and so on. If you accidentally use =$B$2>100, every row will be judged by the one value in B2.
Think of the formula as written for the top-left cell of the Applies to range. If the range starts at A2, references should usually be aligned with row 2. If the range instead starts at A5, a row-based formula should generally start with row 5. When the formula and range start on different rows, Excel may evaluate the wrong record and make the formatting appear offset.
Useful formula examples
Assume a task list has status in column B and due dates in column C, with data in rows 2–50. To highlight rows where the status is Open and the due date is before today, select A2:C50 and use =AND($B2="Open",$C2<TODAY()). The dollar signs lock the tested columns while allowing the row to change. TODAY updates with the current date, so a task can become overdue without manually changing the rule.
Assume names are in A2:A20 and you want to mark repeated names. Select A2:A20 and use =COUNTIF($A$2:$A$20,A2)>1. The fixed range counts occurrences in the full list, while A2 shifts for each cell being formatted. Every occurrence of a repeated name is highlighted, including the first one.
Assume dates are in C2:C100 and you want to flag dates more than 30 days old, while ignoring blank cells. Select the date cells and use =AND($C2<>"",$C2<TODAY()-30). The blank check matters because an empty cell can behave like a zero in date comparisons, potentially producing unexpected formatting.
Assume comments are in D2:D100 and you want to highlight a row when its comment contains the word urgent, without distinguishing uppercase from lowercase. Select A2:D100 and use =ISNUMBER(SEARCH("urgent",$D2)). SEARCH returns a position when it finds the text, and ISNUMBER converts that result into TRUE; if the text is absent, the formula returns FALSE. This example highlights the whole row because the selected range includes columns A through D.
Choosing between a formula and a built-in rule
Use the simplest rule type that matches the job. Excel’s built-in options are easier to configure for common conditions, and often make the intended logic clearer to another person who opens the workbook.
- For a single threshold, use Home > Conditional Formatting > Highlight Cells Rules, then choose an option such as Greater Than.
- For visual comparisons across a range, consider Data Bars, Color Scales, or Icon Sets from the Conditional Formatting menu.
- Choose New Rule and a formula when the condition depends on another column, multiple tests, or a calculation that the built-in dialog does not offer.
A formula rule is not automatically better just because it is more flexible. For example, if the only goal is to color values below zero, a built-in Less Than rule is usually simpler to inspect. If the goal is to color an entire row based on a status in one column, a formula is the appropriate choice because the condition and the formatted cells are different ranges.
Rule order and overlapping formats
A cell can meet more than one conditional formatting rule. When that happens, Excel may apply formatting from multiple rules, and the order can affect the visible result if rules specify conflicting formats. For instance, one rule might set a red fill for overdue items while another sets a green fill for completed items. If a row can satisfy both conditions, decide which appearance should take precedence.
Open Home > Conditional Formatting > Manage Rules to inspect the rules that apply to the current selection or worksheet. Use the move-up and move-down controls to change their priority. The Stop If True option can prevent lower-priority rules from being evaluated when a higher-priority rule is true; use it only when that behavior is intended. Also inspect Applies to: rules that cover a much larger range than necessary can make a workbook harder to maintain and may slow recalculation when there are many complex rules.
Common problems and fixes
- The whole range has the same result: Check whether the row reference was accidentally made absolute, such as
$B$2, when it should change by row as$B2. - The highlight is one row out: Compare the first row in Applies to with the row number in the formula. A range starting at A2 should normally use row 2 in its formula.
- Nothing is formatted: Confirm that the formula returns TRUE for at least one row, that the Applies to range includes the intended cells, and that the chosen format is visible against the sheet’s existing colors.
- Only one column changes: The rule may apply only to that column. To format a full row, select the whole row range and use a formula that locks the condition’s column but not its row.
- Dates behave unexpectedly: Make sure the cells contain real Excel dates rather than text that merely looks like a date. Add a blank check if empty cells should be ignored.
- A formula works in a worksheet cell but not in the rule: Check the rule’s starting reference and ensure the formula is entered as a formula, beginning with an equals sign. Avoid references that depend on a different workbook being open.
When editing a rule, remember that the preview can be hard to interpret if the worksheet contains mixed values or several overlapping rules. Test the logic on a small range first, then expand Applies to after confirming the result. If a format seems stuck after changing a formula, reopen Manage Rules and check for a second rule that also formats the same cells.
Frequently Asked Questions
Does a conditional formatting formula need to start with an equals sign?
Yes. Enter the expression as a worksheet formula beginning with =, such as =$B2>100. Excel evaluates it for the cells in the rule’s Applies to range.
Can I use a formula to highlight an entire row?
Yes. Select the full row range to format, then write the condition with the relevant column locked and the row left relative. For example, if status values are in B2:B20 and A2:C20 should be formatted, use =$B2="Open".
Can a conditional formatting formula refer to another cell?
Yes. A rule can compare a cell with another worksheet cell. For example, if values are in A2:A20 and the threshold is in F1, select A2:A20 and use =A2>$F$1. The fixed reference keeps the threshold cell constant.
Why does Excel highlight the wrong row?
The formula’s relative row reference may not match the first row of the Applies to range, or a reference may be locked when it should move. Check both the range and the dollar signs in the formula.
Can I use TODAY in a conditional formatting rule?
Yes. TODAY returns the current date and updates when Excel recalculates. It is useful for rules such as highlighting past-due dates, but include a blank-cell test if empty date cells should not be highlighted.
Related
- Bps Number Format in Excel: Display Basis Points
- Bottom Border Excel Shortcut and How to Use It
- Billion Number Format in Excel: Display Values in Billions
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Copy the Exact Same Formula in Excel Without Changing ItTo copy the exact same formula text in Excel, select the formula in the formula bar, copy it,…
- Excel Table Header Formula: What Works and What Doesn’tExcel table headers are column labels, not the place to put a formula you expect to calculate or…
- Excel Table Names in Formulas: How to Use Them WellYes. In Excel for Microsoft 365 on Windows, use a table name and its column header in a…
- 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.…
- XLOOKUP Formula: Real Examples in ExcelThe basic XLOOKUP formula is =XLOOKUP(E2,A2:A6,B2:B6): it looks for the value in E2 within A2:A6 and returns the…
- VLOOKUP Errors in Excel: Causes, Checks, and FixesMost VLOOKUP errors come from a lookup value that does not match the first column of the search…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.