The direct answer: Excel’s conditional formatting does not have a built-in “IF false” rule, but you create one by writing a formula that returns TRUE when you want the formatting applied. For example, to highlight cells where a value is not greater than 100, select the range, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, enter =NOT(A1>100) (or =A1<=100), and set the format. The rule fires only when the formula evaluates to TRUE, so “false” is simply the inverse of your condition.
How Conditional Formatting Formulas Actually Work
Conditional formatting evaluates a formula for each cell in the selected range. If the formula returns TRUE, the format is applied; if FALSE, it is not. There is no separate “else” branch—you can only add more rules with their own formulas. The key is that the formula must be relative to the active cell (the first cell in the selection), not the entire range. For instance, if you select B2:B10 and the active cell is B2, your formula should reference B2, and Excel will adjust it for B3, B4, etc.
To invert a condition, you have three main options:
- Use NOT():
=NOT(A1>100)returns TRUE when A1 is not greater than 100. - Use the opposite comparison:
=A1<=100is simpler and often clearer. - Use IF() inside a formula:
=IF(A1>100,FALSE,TRUE)works but is redundant—conditional formatting already treats any nonzero number as TRUE, so=IF(A1>100,0,1)also works but is less readable.
Remember: the formula must start with an equals sign, use commas as argument separators (semicolons in some European locales), and return a logical value or a number (0 is FALSE, any other number is TRUE).
Step-by-Step: Applying an “IF False” Rule
Assume you have sales figures in A2:A20 and you want to highlight any cell that is NOT greater than 100 (i.e., 100 or less). Here’s the exact process:
- Select A2:A20 (make sure A2 is the active cell—it’s the white cell in the selection).
- Go to Home > Conditional Formatting > New Rule.
- Choose “Use a formula to determine which cells to format.”
- In the formula box, enter
=A2<=100. - Click Format, choose a fill color (e.g., light red), and click OK.
- Click OK again to apply the rule.
The result: every cell in A2:A20 with a value of 100 or less gets the red fill. If you wanted to highlight cells that are NOT blank, you’d use =NOT(ISBLANK(A2)). If you wanted to highlight cells that do NOT contain the word “Paid”, use =NOT(ISNUMBER(SEARCH("Paid",A2)))—note SEARCH is case-insensitive, FIND is case-sensitive.
Common Patterns: NOT, ISERROR, and Blank Cells
Three patterns cover most “if false” scenarios:
- NOT(ISERROR(...)): Highlight cells where a lookup does NOT produce an error. For example, with lookup values in A2:A10 and a table in D2:E10, use
=NOT(ISERROR(VLOOKUP(A2,$D$2:$E$10,2,FALSE)))to highlight rows where the lookup succeeds. - NOT(ISBLANK(...)): Highlight non-empty cells. Use
=NOT(ISBLANK(A2)). To highlight blanks, use=ISBLANK(A2). - NOT(COUNTIF(...)): Highlight values that do NOT appear in another list. If your list is in B2:B10 and the range to check is A2:A20, use
=COUNTIF($B$2:$B$10,A2)=0—this returns TRUE when the count is zero, effectively “not found.”
These patterns work in Excel 2010 and later, including Microsoft 365, Excel 2021, and Excel for the web. On a Mac, the steps are identical, but the ribbon path is the same (Home > Conditional Formatting).
How to Choose Between NOT() and a Direct Comparison
Use NOT() when the original condition is complex or you want to invert a function like ISERROR or ISBLANK. For simple numeric comparisons, a direct opposite operator is clearer:
- Instead of
=NOT(A1>100), use=A1<=100. - Instead of
=NOT(A1="Yes"), use=A1<>"Yes"(the not-equal operator). - Instead of
=NOT(ISNUMBER(A1)), use=ISTEXT(A1)or=NOT(ISNUMBER(A1))—the latter is fine, butISTEXTis shorter.
There’s no performance difference; choose whichever is easier to read. However, be careful with text comparisons: =A1<>"" checks for non-empty, but it also treats cells with spaces as non-empty. Use =TRIM(A1)<>"" to ignore spaces.
Common Problems and Fixes
Several issues trip up users:
- Formula applies to the wrong cells: If you select A2:A20 but your formula references A1, the rule will shift. Always reference the active cell, not the top of the selection unless they are the same.
- Absolute vs. relative references: If you want the rule to check a fixed cell (e.g., compare each value to $C$1), use absolute references with dollar signs. If you want it to check each row’s own value, use relative references (no dollar signs).
- Rule doesn’t trigger on blanks: By default, conditional formatting ignores blank cells unless your formula explicitly handles them. For example,
=A1<=100returns TRUE for a blank cell (since blank is treated as 0), so it will highlight blanks. To exclude blanks, add=AND(A1<>"",A1<=100). - Formula returns text or error: Ensure your formula returns TRUE/FALSE or a number. If it returns an error, the rule won’t apply. Wrap with IFERROR if needed, e.g.,
=IFERROR(A1>100,FALSE). - Multiple rules conflict: When you have several rules, the first rule in the list that evaluates TRUE wins unless “Stop If True” is checked. To invert a previous rule, you may need to reorder them.
Comparisons: Excel vs. Google Sheets
Google Sheets uses the same principle: a custom formula must return TRUE to apply formatting. The menu path is Format > Conditional formatting > Custom formula is. The syntax is identical, but Google Sheets uses semicolons in some locales. One difference: in Excel, you can reference other sheets directly (e.g., =Sheet2!A1>100), but in Google Sheets, you must use INDIRECT for cross-sheet references in conditional formatting. Also, Excel’s “Stop If True” checkbox is in the rule manager; Google Sheets has a similar “Stop if true” checkbox in the rule editor.
Performance and Best Practices
Conditional formatting formulas recalculate whenever the sheet recalculates. Avoid volatile functions like INDIRECT, OFFSET, and TODAY in large ranges—they slow down the sheet. Use helper columns instead if you have thousands of rows. For example, instead of a complex formula in conditional formatting, add a column with =NOT(ISERROR(VLOOKUP(...))) and then apply a simple rule like =$H2=TRUE. This is faster and easier to debug.
Also, remember that conditional formatting is not a substitute for data validation. It only changes appearance; it does not prevent incorrect data entry. Use Data Validation for that.
Frequently Asked Questions
Can I use IF() in conditional formatting? Yes, but it’s unnecessary. Conditional formatting already treats the result as TRUE/FALSE. You can write =IF(A1>100,FALSE,TRUE), but it’s longer than =A1<=100. However, IF() is useful when you need to return a specific value to compare, like =IF(A1="",0,A1)>100 to treat blanks as zero.
Why is my formula not working even though it returns TRUE? Check the active cell reference. If you selected B2:B10 but your formula says =A2>100, it will check column A, not B. Also, check that the formula is not returning text or an error. Use the “Evaluate Formula” tool (Formulas > Evaluate Formula) to step through it.
How do I highlight cells that are NOT blank in Excel? Use =NOT(ISBLANK(A1)) or =A1<>"". The latter treats cells with spaces as non-blank, so use =TRIM(A1)<>"" to ignore spaces. Apply this as a custom formula rule.
Can I use conditional formatting to highlight entire rows based on a false condition? Yes. Select the entire range (e.g., A2:D20) with the active cell in A2, then use a formula like =$A2<=100 (with a dollar sign before the column letter) to highlight the whole row when column A is not greater than 100. The dollar sign locks the column reference so the rule only checks column A.
Does conditional formatting work in Excel for the web? Yes, it does. The steps are the same, but the interface is slightly different: select the range, go to Home > Conditional Formatting, and choose “New Rule.” Custom formulas are supported. However, some advanced features like “Stop If True” are available, but performance may be slower on very large datasets.
How do I remove a conditional formatting rule? Select the range, go to Home > Conditional Formatting > Manage Rules, select the rule, and click Delete. You can also clear all rules from the selected range using Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
Related
- Splunk Split Command: How to Use It
- Python split() and join() Explained with Examples
- Snowflake SPLIT_PART: Syntax, Examples, and Edge Cases
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIF Over 1: Formula & ExamplesThe direct answer: use =COUNTIF(range,">1") to count cells in a range whose value is greater than 1. For…
- Excel Filter by Cell Value: Shortcut, Steps, and TipsIn Excel for Microsoft 365 on Windows, there is no single dedicated shortcut that immediately filters a list…
- DATEDIF in Excel: Calculate Completed Months Between DatesIn Excel, use =DATEDIF(A2,B2,"m") to count the complete months between a start date in A2 and an end…
- Excel MIN Ignore Errors: Formulas and Common FixesTo find the smallest number in a range while ignoring error cells, use =AGGREGATE(5,6,A2:A10). The 5 selects MIN,…
- XLOOKUP in Excel: Find and Return Data From Another SheetYes. XLOOKUP can search a range on one worksheet and return the matching value from another range on…
- LARGE and SMALL Excel Formulas: Find Ranked ValuesUse Excel’s LARGE and SMALL functions to return the value at a chosen rank in a set of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.