Yes—Excel conditional formatting can use an IF statement, but it usually does not need one. Create a formula-based rule that evaluates to TRUE or FALSE, choose the formatting to apply, and Excel will update the appearance as the cell values change.
What an IF statement does in conditional formatting
Conditional formatting changes a cell’s appearance when a rule is met: for example, it can shade overdue tasks, highlight high sales, or flag duplicate entries. A formula rule is useful when the built-in rules do not express the condition you need, or when the condition depends on another cell.
In a formula rule, Excel checks the formula for each cell in the rule’s applied range. A TRUE result applies the chosen format; a FALSE result does not. You can write an IF function that returns TRUE or FALSE, but a logical test by itself usually gives the same result with less typing.
For example, with task statuses in B2:B100 and a rule applied to A2:F100, the formula =IF($B2="Overdue",TRUE,FALSE) returns TRUE for a row whose status is Overdue. The same condition can be written more simply as =$B2="Overdue". Both can highlight the row, provided the rule is set up with the correct applied range and reference.
An IF function is still useful when the logic genuinely has two outcomes or when the test is easier to read as an IF expression. The important point is that the conditional-formatting rule must produce a logical result that Excel can evaluate.
How to create a formula rule in Excel
In Excel for Microsoft 365 on Windows, select the cells to format, then choose Home > Conditional Formatting > New Rule. Select Use a formula to determine which cells to format, enter the formula, choose Format to set the font, fill, border, or number format, and confirm the dialogs.
- Select the full range that should receive the formatting. For a row-based rule, select all the columns in each row you want to shade, not just the column containing the condition.
- Open Home > Conditional Formatting > New Rule and choose the formula-based rule type.
- Enter a formula beginning with an equals sign. Use references based on the top-left cell of the selected range.
- Choose the appearance in the Format Cells dialog, then select OK to save the rule.
- Check the result on a row that should match and one that should not. If the wrong cells are highlighted, check the formula references and the rule’s applied range.
To inspect or change existing rules, use Home > Conditional Formatting > Manage Rules. The rule manager lets you view the formula, adjust the Applies to range, change priority, and remove rules. If several rules apply to a cell, their order can affect the result. The Stop If True option can prevent lower-priority rules from being evaluated after a higher-priority rule is true.
On Excel for Mac, the conditional-formatting commands are also on the Home tab, although dialog layout and labels can vary slightly by release. Excel for the web has conditional-formatting tools too, but some rule-editing options may differ from the desktop app. If a workbook relies on complex rules, check the rule manager in desktop Excel.
Use IF to highlight a row based on a value
A common request is to color an entire record when one field meets a condition. Suppose a task table runs from A2:F50, with status in column B. Select A2:F50 before creating the rule. With the status in B2:B50 and the applied range A2:F50, use =IF($B2="Overdue",TRUE,FALSE). If B2 contains Overdue, the formula returns TRUE and the first row is formatted; Excel adjusts the row reference for the other rows.
The dollar sign before B locks the condition to column B as the rule moves across the selected row. The missing dollar sign before 2 allows the row number to change as the rule is checked down the range. If you wrote =$B$2="Overdue" instead, every row would check only B2, which is generally not what you want for a row-by-row rule.
The shorter version, =$B2="Overdue", is usually preferable because the comparison already returns TRUE or FALSE. Use IF when it improves readability or when you need different logical outcomes; do not add it automatically just because the request says “if.”
Practical formula examples
Formula rules can compare cells, combine conditions, and count matches. Each formula below assumes the stated layout; adjust the references to match your worksheet and the top-left cell of the range being formatted.
- Highlight values above a target: with actual amounts in C2:C50, targets in D2:D50, and the rule applied to A2:D50, enter
=$C2>$D2. If C2 is 125 and D2 is 100, the test returns TRUE, so the row is formatted. - Flag open tasks past their due date: with status in B2:B100, due dates in C2:C100, and the rule applied to A2:F100, use
=AND($B2="Open",$C2<TODAY()). A row is highlighted only when its status is Open and its due date is earlier than today. TODAY updates the date when Excel recalculates the workbook. - Find duplicate IDs: with IDs in A2:A100 and the rule applied to A2:A100, use
=COUNTIF($A$2:$A$100,$A2)>1. A value appearing more than once is highlighted. If the range includes a header, exclude it so the header is not counted as a potential duplicate. - Highlight a cell when another cell contains a word: with notes in C2:C80 and the rule applied to C2:C80, use
=ISNUMBER(SEARCH("urgent",C2)). SEARCH checks for the text without requiring the same letter case; ISNUMBER makes the rule true when SEARCH finds it.
These examples illustrate different kinds of tests. A comparison such as greater than is suitable for numeric checks; AND requires every listed condition to be true; COUNTIF counts matches; and SEARCH can find text within a longer note. Choose the simplest formula that expresses the rule, and test it with known matching and nonmatching rows.
Relative and absolute references
Reference choices are the main reason a correct-looking formula can format the wrong cells. In conditional formatting, Excel evaluates a formula relative to the cells in the rule’s applied range. For a range starting at A2, a reference such as B2 shifts as Excel checks each row and column. A dollar sign locks a column, a row, or both: $B2 locks column B, B$2 locks row 2, and $B$2 locks both.
For a row-wide rule, select the full set of rows and columns first, then refer to the relevant condition column with a locked column and an unlocked row, such as $B2. For a rule that should always compare against one fixed threshold, lock both parts of the threshold reference. If the applied range begins at row 5 but the formula refers to row 2, Excel will evaluate a different row than intended.
When you change the Applies to range later, revisit the formula. Expanding a range upward or shifting its first column can change which cell should be used as the relative starting point. The formula’s first reference should correspond to the top-left cell of the range being formatted.
Formula rules versus built-in conditional formatting
Not every condition calls for a formula. Excel’s built-in options are often faster for straightforward tasks. Use Home > Conditional Formatting > Highlight Cells Rules for conditions such as values greater than a number, text that contains a phrase, dates in a period, or duplicate values. Top/Bottom Rules and Data Bars, Color Scales, and Icon Sets cover other common comparisons and visual summaries.
Choose a formula-based rule when the condition involves another cell, multiple tests, a custom text check, or a pattern the preset options cannot describe. For example, the built-in duplicate rule is convenient for a single selected list, while a formula using COUNTIF can target a particular range or help apply a duplicate condition to a larger row. A formula is more flexible, but that flexibility makes references and rule priority more important.
Common problems and how to fix them
- The whole range has the same result: check for an accidentally fixed row reference such as $B$2. For row-by-row evaluation, the row number usually needs to remain relative, as in $B2.
- The format appears one row too high or low: compare the formula’s row number with the first row in the Applies to range. They should be aligned.
- Nothing changes: confirm that the formula starts with =, that the condition is true for at least one cell, and that the chosen format is visually noticeable. Also check whether another rule with higher priority is taking precedence.
- Text comparisons do not match: inspect for extra spaces, spelling differences, or a formula that checks the wrong column. SEARCH can be useful when a word may appear inside a longer note.
- Dates behave unexpectedly: make sure the cells contain actual Excel dates rather than text that only looks like a date. A date comparison cannot reliably work as intended if the underlying value is text.
- Old formatting remains: an existing conditional-formatting rule may still apply. Review Manage Rules, check the scope, and remove or edit duplicate or obsolete rules.
Conditional formatting changes how values look; it does not alter the underlying cell contents. If a cell is shaded because it matches a rule, the fill color is not a replacement for storing a status or result in the data itself. That distinction matters when sorting, filtering, or sharing a workbook: another person may need to inspect the rule to understand why a cell looks different.
Frequently Asked Questions
Can I use an IF statement in Excel conditional formatting?
Yes. Choose a formula-based rule and enter an IF formula that returns TRUE or FALSE. In many cases, the logical test alone is shorter and produces the same formatting result.
How do I highlight an entire row if one cell contains a word?
Select the full row range to format, create a formula rule, and refer to the word-containing column with a locked column and a relative row. Make sure the formula’s row matches the first row of the selected range.
Does conditional formatting change the cell value?
No. It changes the cell’s appearance while the value and formula remain as they were. To change data, use a worksheet formula, data entry, or another appropriate editing method.
Why is my conditional-formatting formula not working?
Check the leading equals sign, the formula’s references, the first row of the applied range, and the rule priority. Then test whether the condition is actually true for the example row you expect to highlight.
Can conditional formatting use a formula from another worksheet?
Direct references to another worksheet can be restricted in conditional-formatting formulas. A common workaround is to bring the needed value into the current sheet, for example with a helper column, and base the rule on that local cell.
Can I use AND or OR instead of IF?
Yes. AND and OR combine logical tests and return TRUE or FALSE, which makes them suitable for formula rules. Use AND when every condition must be met and OR when any one of the conditions is enough.
Related
- VA Budget Worksheets: What to Include and How to Use
- Debt Snowball Spreadsheets: Build a Payoff Tracker
- How to Add Multiple Transitions in PowerPoint
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Conditional Formatting Dates in Excel: Rules and FormulasTo conditionally format dates in Excel for Microsoft 365 on Windows, select the date cells and choose Home…
- 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…
- How to Sort Alphabetically in Excel Without Mixing RowsTo sort alphabetically in Excel for Microsoft 365 on Windows, select a cell in the column, then choose…
- XLOOKUP in Excel: What If the Return Cell Is Blank?If XLOOKUP finds a match whose return cell is truly empty, Excel normally displays 0—not a blank. With…
- Excel COUNTIF for Text That Contains a WordTo count cells that contain a particular word or phrase anywhere in their text, use Excel’s COUNTIF function…
- Calculate Days Left in Excel with Simple FormulasTo calculate days left in Excel, enter the deadline in A2 and use =A2-TODAY() in another cell. Excel…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.