The direct answer: in Excel conditional formatting, an “if then” rule is a formula that returns TRUE to apply formatting and FALSE (or any non-TRUE value) to skip it. You enter it in the New Formatting Rule dialog by choosing “Use a formula to determine which cells to format,” then type a formula like =B2>100 and set the format.
What Conditional Formatting Formulas Actually Do
Conditional formatting is Excel’s way of changing a cell’s appearance (fill color, font, border) based on a condition. When you select “Use a formula,” you’re not writing a traditional IF function that outputs a value. Instead, you’re writing a logical test that Excel evaluates for each cell in the selected range. If the formula returns TRUE, the format is applied; if it returns FALSE, nothing happens. This is why you often see formulas like =A1>10 rather than =IF(A1>10,"yes","no") — the IF wrapper is unnecessary because the comparison itself already produces TRUE or FALSE.
For example, to highlight all cells in column A that are greater than 100, you select A2:A100, go to Home > Conditional Formatting > New Rule > Use a formula, and enter =A2>100. Note the reference is relative to the first selected cell (A2). Excel then applies the rule to every cell in the range, adjusting the reference relative to each row.
How to Write a Conditional Formatting Formula (Step by Step)
- Select the range you want to format (e.g., B2:B20).
- Go to Home > Conditional Formatting > New Rule.
- Choose “Use a formula to determine which cells to format.”
- In the formula box, type your logical test (e.g.,
=B2>100). - Click the Format button, choose a fill or font style, and click OK.
- Click OK again to apply the rule.
The key is the formula’s first cell reference. If your selected range starts at B2, your formula should refer to B2, not B1 or B3. Excel uses that reference as the anchor and adjusts it for every other cell in the range. On Mac, the steps are identical but the dialog is accessed via the same ribbon path; Excel for the web also supports formula-based rules, though the interface is slightly different (Format > Conditional formatting > Custom formula is for Google Sheets, not Excel).
Common Formula Patterns and Examples
Here are several practical “if then” formulas you can copy and adapt. Assume your data starts in row 2.
- Greater than a number:
=C2>500highlights sales over 500. - Text contains a word:
=ISNUMBER(SEARCH("urgent",A2))highlights cells containing “urgent” (case-insensitive). - Blank cells:
=ISBLANK(A2)highlights empty cells. - Entire row based on one column: With range A2:E20 selected, use
=$C2>100(note the mixed reference) to highlight rows where column C exceeds 100. - Date older than 30 days:
=A2highlights past-due dates. - Duplicate values:
=COUNTIF($A$2:$A$100,A2)>1highlights duplicates.
For the row example, the dollar sign before C locks the column but lets the row change. This is crucial — without the $, the formula would check each cell's own column, not column C.
Why Use a Formula Instead of Built-In Rules?
Excel's preset rules (Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales) cover common cases but are limited. Formulas give you complete control. You can combine multiple conditions with AND or OR, reference other sheets, use wildcards, and apply formatting based on values outside the selected range. For instance, to highlight a cell in B2 if its corresponding value in A2 is "Yes" and C2 is greater than 50, use =AND(A2="Yes",C2>50).
Formulas also let you use functions that aren't available in the preset rules, like ISERROR, MOD (for every nth row), or INDIRECT. This flexibility is why many advanced Excel users prefer formula-based rules for dashboards and reports.
Common Mistakes and How to Fix Them
Several errors trip up users new to formula-based formatting:
- Wrong anchor reference: If you select A2:A10 but type
=A1>5, the formatting will be off by one row. Always reference the first cell of your selection. - Using IF unnecessarily:
=IF(A2>5,"TRUE","FALSE")works but is longer than=A2>5. Excel treats any non-zero number or TRUE as true; text like "TRUE" also works, but it's messy. - Forgetting absolute references in row highlighting: As shown, use
$C2notC2when the range covers multiple columns. - Formula returns a number instead of TRUE/FALSE: Excel treats any positive number as TRUE, so
=A2*2will highlight cells where the result is positive, which may not be intended. - Applying rule to entire column: If you select A:A and use
=A1>100, it works, but it may slow down large sheets. Limit the range to your data.
To debug, select a cell and temporarily type your formula in a worksheet cell to see what it returns. If it shows TRUE, the rule should work.
Managing and Editing Conditional Formatting Rules
To see all rules applied to a selection, go to Home > Conditional Formatting > Manage Rules. From there you can edit, delete, or reorder rules. Order matters: Excel applies rules in the order listed, and if two rules conflict, the first one wins unless "Stop If True" is checked. For example, if you have a rule that highlights all values over 100 in red and another that highlights values over 200 in green, the red rule will override the green for values over 200 unless you check "Stop If True" on the red rule or reorder them.
You can also use the Format Painter to copy conditional formatting to other cells, but it copies the entire rule set. To copy only formatting, use Paste Special > Formats. On Excel for the web, the Manage Rules dialog is more limited but still allows editing formulas.
Performance and Large Data Sets
Formula-based conditional formatting recalculates every time the worksheet changes. On a small range (a few hundred cells), this is instant. But if you apply a volatile formula like =TODAY() to 100,000 cells, Excel may lag. To improve performance:
- Limit the range to the actual data area, not entire columns.
- Avoid volatile functions (TODAY, NOW, OFFSET, INDIRECT) unless necessary.
- Use simpler formulas; complex array formulas in conditional formatting can be slow.
- Consider using Excel Tables (Insert > Table) because they automatically adjust formatting ranges.
If you need to apply a rule to a large dataset, test on a small sample first to verify the formula logic before applying to the full range.
Frequently Asked Questions
Can I use an IF function directly in conditional formatting?
Yes, you can write =IF(A2>100,TRUE,FALSE), but it's redundant. The comparison =A2>100 already returns TRUE or FALSE. Using IF adds no benefit and makes the formula longer. Stick to the direct logical test.
Why is my conditional formatting formula not working?
The most common cause is a wrong cell reference. Ensure the formula's first reference matches the first cell of your selected range. Also check that the formula returns TRUE for at least one cell — test it in a worksheet cell. Another cause: the formula may be returning a number or text, not TRUE/FALSE. Finally, check for typos in function names or parentheses.
How do I highlight an entire row based on a cell value?
Select the entire row range (e.g., A2:E10), then create a formula rule with a mixed reference like =$C2>100. The dollar sign locks the column C, so every cell in the row checks the value in that row's column C. Without the $, each cell would check its own column, which is usually not what you want.
Can I use conditional formatting with text that contains a specific word?
Yes, use =ISNUMBER(SEARCH("word",A2)). SEARCH is case-insensitive. If you need case-sensitive, use FIND instead: =ISNUMBER(FIND("word",A2)). Both return TRUE if the text is found.
Does conditional formatting work on merged cells?
Conditional formatting generally works on merged cells, but the formula references can be tricky because a merged cell only has one value (the top-left cell). If you apply a rule to a merged range, the formula will only evaluate the top-left cell's value. It's better to avoid merging cells when using conditional formatting, or apply the rule to the merged cell's top-left reference.
How do I remove conditional formatting without deleting the data?
Select the range, go to Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This removes only the formatting rules, leaving your data intact. You can also use Manage Rules to delete individual rules.
Alternatives to Formula-Based Conditional Formatting
If formulas feel complex, Excel offers built-in rules that cover many needs:
- Highlight Cell Rules: Greater Than, Less Than, Between, Equal To, Text That Contains, A Date Occurring, Duplicate Values.
- Top/Bottom Rules: Top 10 Items, Bottom 10%, Above Average, etc.
- Data Bars, Color Scales, Icon Sets: Visual indicators without formulas.
These are easier to set up but less flexible. For example, to highlight cells that are both greater than 100 and less than 200, you'd need a formula (=AND(A2>100,A2<200)) because the built-in "Between" rule does exactly that, but for more complex logic (like referencing another sheet), formulas are the only way. Start with presets, then move to formulas when you hit their limits.
Related
- Excel Attendance Point Tracker Template: Setup & Use
- Excel and Numbers Compatibility: What Works
- Excel Align Cells Evenly: 4 Quick Ways
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Letter Grade: IF, IFS, VLOOKUPThe most direct Excel formula for a letter grade is a nested IF statement, for example =IF(A1>=90,"A",IF(A1>=80,"B",IF(A1>=70,"C",IF(A1>=60,"D","F")))) when…
- Excel Conditional Formatting Formula If Date: Full GuideThe core formula for conditional formatting based on a date in Excel is =A2=TODAY() (assuming your date is…
- Power Query IF Examples: Conditional Logic in ExcelIn Power Query, write an IF test in M as if condition then value_if_true else value_if_false. For example,…
- Excel INDEX Function: Syntax, Examples, and Common UsesYes. In the English-language version of Microsoft Excel, the function is called INDEX. It returns the value—or, in…
- Excel PMT Function for Loans: Formula and ExamplesExcel’s PMT function calculates the regular payment for a loan with a constant interest rate and equal payments.…
- Compare Data in Excel With VLOOKUP: A Step-by-Step GuideTo compare two lists in Excel, use an exact-match VLOOKUP to check whether each item in one list…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.