To use an IF formula in Excel conditional formatting, select the cells, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter a formula such as =IF($B2="Late",TRUE,FALSE), choose a format, and select OK. The formula must evaluate to TRUE for the format to appear; in many cases, a simpler logical test works just as well.
How formula-based conditional formatting works
Conditional formatting changes a cell’s appearance when a rule’s condition is met. A formula rule lets you define that condition yourself, including checks across multiple columns, comparisons with dates, or combinations of conditions that the built-in menu options do not cover.
Excel evaluates the rule for each cell in its Applies to range. In a range of rows, the formula is typically written relative to the first row, and Excel adjusts the row reference as it checks the rest. The formula is entered in the rule dialog, not in the worksheet cell, so it does not replace the cell’s contents.
For example, suppose column B contains task statuses and you want to color each row from A through D when its status is Late. The rule can check column B while the format applies across all four columns. A dollar sign before B keeps the test in the status column; leaving the row number relative lets the test move from row 2 to row 3, row 4, and so on.
Create a rule in Excel for Microsoft 365
In Excel for Microsoft 365 on Windows, select the cells that should receive formatting, then follow these steps:
- Choose Home > Conditional Formatting > New Rule.
- Select Use a formula to determine which cells to format.
- Enter a formula that returns TRUE when the selected cells should be formatted.
- Choose Format, set the font, fill, border, or number format, and select OK.
- Check the Applies to range in the Rules Manager if the rule should cover more or fewer cells.
With task records in A2:D100, status values in B2:B100, and the goal of highlighting each entire row when its status is Late, enter =$B2="Late" and apply the rule to =$A$2:$D$100. The comparison is TRUE for a row whose B cell contains Late and FALSE for other rows. The dollar signs lock the status column and the applied columns, but the row reference in the formula stays flexible.
Excel for Mac also offers formula-based conditional formatting through Home > Conditional Formatting > New Rule; dialog labels and arrangement can vary by release. In Excel for the web, use Home > Conditional Formatting and choose a new rule; if the formula option or a particular editing control is unavailable in your interface, open the workbook in the desktop app to manage that rule.
When to use IF, and when to skip it
An IF function checks a condition and returns one result when it is true and another when it is false. In conditional formatting, however, the rule itself needs a condition, so a direct comparison is often clearer than wrapping that comparison in IF. Both versions below can express the same decision.
With task statuses in B2:B100 and the rule applied to B2:B100, =IF($B2="Late",TRUE,FALSE) returns TRUE when the status is Late and FALSE otherwise. The shorter rule =$B2="Late" has the same result. The latter is usually easier to read and maintain.
Use IF when the rule genuinely needs to choose between outcomes before returning a logical result, or when the calculation is easier for you to understand in that form. Avoid returning labels such as Late or On time as the rule’s result: a formula-based formatting rule is intended to evaluate to TRUE or FALSE. Also, IF does not make a rule apply to a range by itself; the Applies to setting determines where Excel evaluates it.
Useful formula patterns and examples
Formula rules can evaluate numbers, text, dates, and multiple fields. Choose the range to format separately from the condition being tested, and make the row reference match the first row of that range.
- Highlight values above a limit. With numbers in C2:C50 and the rule applied to C2:C50, enter
=C2>100. A value of 125 makes the formula TRUE and is formatted; 100 and lower do not meet the test. - Require two conditions. With order details in A2:D50, status in B, amount in C, and the rule applied to A2:D50, enter
=AND($B2="Open",$C2>=500). A row is highlighted only when its status is Open and its amount is at least 500. For example, Open with 600 qualifies; Closed with 600 does not. - Accept either condition. With the same layout and applied range, enter
=OR($B2="Urgent",$C2>=1000). The row is formatted if it is Urgent, if its amount is at least 1,000, or if both are true. - Find repeated entries. With IDs in A2:A100 and the rule applied to that range, enter
=COUNTIF($A$2:$A$100,A2)>1. Each ID that appears more than once is highlighted, including every instance of a repeated ID. Blank cells may also be counted as repeats, so exclude blanks if the range contains them.
These examples use comma separators, as in standard English-language Excel formulas. Excel installations with different regional settings may display a different list separator, so follow the separator Excel expects in that environment.
Dates, empty cells, and relative references
Date rules are useful for overdue items and upcoming deadlines. Excel stores dates as serial values, so comparisons with functions such as TODAY can identify dates before or after the current date. The formula is recalculated as the workbook recalculates; TODAY does not need to be typed into each row.
With due dates in C2:C100 and the rule applied to A2:D100 to highlight complete rows, enter =AND($C2<>"",$C2<TODAY()). For a date earlier than today, with C2 nonempty, the formula returns TRUE. The first condition prevents an empty due-date cell from being treated like an old date. A date equal to today is not overdue under this test; use a less-than-or-equal comparison if due today should also qualify.
Reference style matters. In a formula such as =$B2="Late", $B fixes the column and 2 can change as Excel evaluates lower rows. By contrast, B2 allows both the column and row to shift, while $B$2 fixes both. If a rule checks the wrong column or seems to repeat one row’s result, confirm that the formula’s starting row matches the first row in Applies to and that the dollar signs are intentional.
Common problems and how to fix them
- The whole range gets the same result. A fully absolute reference such as
$B$2always checks B2. If each row should check its own status, keep the column fixed but make the row relative, as in$B2. - The rule begins one row too high or low. If Applies to starts at row 2, write the formula for row 2. A formula written for row 1 will be offset when Excel evaluates the selected range.
- Nothing changes. Check that the condition is actually TRUE for at least one record, that the format is visible, and that the correct cells are in Applies to. Text comparisons may fail if the cell contains a different spelling or extra characters.
- A blank row is highlighted unexpectedly. Add a not-blank test when the rule uses a date or number comparison and empty cells should be ignored. The AND example above shows this pattern.
- Another rule overrides the appearance. Choose Home > Conditional Formatting > Manage Rules to review rule order, ranges, and the Stop If True setting where available. Rules that set the same format property can compete; simplify overlapping rules or adjust their order.
When troubleshooting, open Manage Rules and inspect the rule for the selected range. A rule may exist but apply to a different range than expected. Also distinguish a formula error from a formatting issue: first test whether the condition should be true for a specific row, then verify the chosen fill or font color is not hidden by the sheet’s existing style.
Formula rules compared with built-in rules
Built-in conditional formatting options are quicker for common tasks. Home > Conditional Formatting includes options such as Highlight Cells Rules and Top/Bottom Rules, along with color scales, data bars, and icon sets. These are convenient for simple comparisons, duplicate values, and visual summaries.
Choose a formula rule when the decision depends on another column, several conditions, or a row-level test. For instance, a built-in greater-than rule can format a numeric cell directly, while a formula rule can color an entire order row based on that number and a status field. Formula rules offer flexibility, but they also require careful reference and range setup. Use the least complicated rule that clearly expresses the requirement.
Frequently Asked Questions
Can I use an IF formula in conditional formatting?
Yes. Create a formula-based rule and enter an IF expression that returns TRUE when the format should appear and FALSE otherwise. A direct logical comparison is often shorter and gives the same result.
Do I enter the formula in the cell or in the rule?
Enter it in the formula box under Use a formula to determine which cells to format. The cell contents remain unchanged; the formula controls only the appearance.
How do I highlight a whole row based on one cell?
Select the full row range to format, then write a formula that locks the test column but leaves the row relative. For a status in column B and a selected range beginning on row 2, a test such as =$B2="Late" checks each row’s status.
Can conditional formatting use AND or OR?
Yes. AND requires every listed condition to be true; OR requires at least one. Use them in a formula rule when a decision depends on multiple tests.
Why is my formula highlighting the wrong rows?
Check that its row number matches the first row in the Applies to range, then review absolute and relative references. A locked row or a range beginning on a different row commonly shifts the result.
Will a date rule update automatically?
A rule using TODAY updates when Excel recalculates the workbook, so the set of overdue dates can change over time. If calculation is set to Manual, recalculate the workbook to refresh results.
Related
- Change All Caps to Lowercase in Excel: A Practical Guide
- Can You Change Border Color in Excel? Steps and Tips
- Can Git Track Excel Files? Version Control Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Filter Table Multiple Criteria: Practical MethodsTo filter an Excel table by multiple criteria, use the filter arrows in its column headings: set criteria…
- Excel String Functions and Operators ExplainedIn Excel, string functions work with text, while operators combine values or compare them. The main text-joining operator…
- Excel Split Cell String: Formulas, Tools, and FixesTo split a cell string in Excel for Microsoft 365, enter =TEXTSPLIT(A2," ") in an empty cell to…
- SUMIF Excel Formula: Examples, Syntax, and Common FixesTo add values that match one condition, use SUMIF: with regions in A2:A6 and sales in B2:B6, =SUMIF(A2:A6,"East",B2:B6)…
- Excel Formula for Beginning of Month: Dates Made EasyIf A2 contains an Excel date, use =EOMONTH(A2,-1)+1 to return the first day of that date’s month. For…
- Excel Data Validation INDIRECT for Dependent ListsYes. In Excel, INDIRECT can make a data-validation drop-down depend on another cell: name each child list after…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.