How to Use IF-Then Formulas to Color Cells in Excel

To color cells with an if-then condition in Excel, create a conditional formatting rule, enter a formula that returns TRUE or FALSE, and choose a fill color. A worksheet IF formula returns a value; the formatting rule supplies the color.

For example, select B2:B7 and use =IF(B2>=80,TRUE,FALSE) to highlight scores of 80 or more. The shorter =B2>=80 works too. Put either formula in the conditional formatting dialog, rather than replacing the scores in your worksheet.

The main steps apply to desktop Excel for Windows in Microsoft 365, Excel 2024, Excel 2021, Excel 2019 and Excel 2016. Separate Mac and web directions follow. You need permission to edit the workbook. Try the example in an empty sheet first if your workbook already has formatting rules.

Color Cells With an IF Formula in Excel for Windows

Use a small score list to make the result easy to check: enter 79, 80, and 91 in B2, B3, and B4. Leave the header in B1 outside the rule.

1. Select B2:B7, starting at B2. The first cell you select is the one the formula must describe.
2. Choose Home > Conditional Formatting > New Rule. The New Formatting Rule dialog opens.
3. Under Select a Rule Type, choose Use a formula to determine which cells to format. A box labeled Format values where this formula is true appears.
4. Type =IF(B2>=80,TRUE,FALSE) in that box. Type the cell address rather than clicking cell B2, because clicking inserts a locked reference ($B$2) that tests only one cell.
5. Click Format, select the Fill tab, and choose a green background. The Preview area shows the color.

6. Click OK in the format dialog, then OK to create the rule. Cells with 80 or more turn green right away.

In this example, 79 does not qualify; 80 and 91 do. Change 79 to 85 and check that its cell becomes green. Change it back to 79 and check that this rule no longer supplies green. Other rules or an existing manual fill may still color that cell.

A tip for editing the formula box: if you press an arrow key to move through the text, Excel may insert a cell reference instead. Press F2 first to switch the box to edit mode, then use the arrow keys.

Microsoft explains the Windows and web rule controls in its conditional formatting guide. To apply a color by hand instead, follow how to color cells in Excel.

Why IF Does Not Set the Fill Color

The IF function evaluates a condition and returns one of two values. For example, this worksheet formula displays a label:

=IF(B2>=80,"Pass","Review")

With a score of 91, it returns Pass; with 79, it returns Review. Those labels can help readers understand a score without depending on color alone. Likewise, =IF(B2>=80,"Green","Red") returns the words Green or Red. It does not paint the background, and no worksheet formula can change a cell’s fill.

For a formatting rule, use a logical result such as =B2>=80. TRUE activates the format you chose; FALSE leaves that rule inactive. With the formula option, you can leave out IF and use AND, OR and NOT on their own. Keeping IF is harmless, but the short form is easier to read and edit.

Add a Second Color for the Else Part

One rule handles only the “then” part of if-then-else. To color scores under 80 red as well, add a second rule to the same range:

1. Select B2:B7 again.
2. Choose Home > Conditional Formatting > New Rule and pick Use a formula to determine which cells to format.
3. Enter =AND(ISNUMBER(B2),B2<80). The ISNUMBER check keeps empty cells from turning red, because Excel treats a blank cell as 0.
4. Click Format, choose a light red fill on the Fill tab, and click OK twice.

Now 79 is red, and 80 and 91 stay green. Add a third rule for a third band, such as =AND(B2>=70,B2<80) for yellow. When two rules could both be true for the same cell, the one higher in the list wins, so put the most specific rule at the top (see the rule manager section below).

Color a Whole Row Based on One Cell

Suppose columns A through D contain a record, and column B holds its score. Select A2:D7 and create a formula rule using:

=$B2>=80

The dollar sign locks the test to column B while the row number changes. Each row is evaluated using its own score, so a qualifying B3 colors A3:D3. Without the dollar sign, the tested column shifts across the row, so C2 would be checked for the cell in column B, and so on.

Match the formula to the first row of the selected range: a range starting at row 2 needs a row-2 reference. Using =$B3>=80 for A2:D7 tests the following row and makes the colors appear one row off.

The Christmas list spreadsheet in Excel is a good place to practice this: its Bought checkboxes return TRUE when checked, so a whole-row rule such as =$D2=TRUE can shade every gift you have already bought.

Keep the Threshold in a Cell

To change the cutoff without editing the rule, type the number in a spare cell, such as F1, and refer to it:

=$B2>=$F$1

Both dollar signs on F1 keep every cell pointed at the same threshold. Type 90 in F1 and the colors update at once. Microsoft’s guide to relative, absolute, and mixed references goes deeper into these distinctions.

Reference	What stays fixed	Typical use in a rule
B2	Nothing	Color only the tested cells
$B2	Column B	Color a whole row from one column
B$2	Row 2	Compare each column with a header row
$B$2	Cell B2	A single threshold or target cell

Use Text or More Than One Condition

To color A2:D7 when column C contains the status Ready, use:

=$C2="Ready"

Put text in quotation marks; do not put quotation marks around the entire formula. Use straight quotes. Curly quotes pasted from a web page or a document can stop the rule from working. Text comparisons with = ignore case, so ready and READY also match. If people type the status, a drop-down list in Excel keeps the spelling consistent so the rule always finds it.

For a stricter rule requiring both a numeric score of at least 80 and that status, use:

=AND(ISNUMBER($B2),$B2>=80,$C2="Ready")

Our example requires all three checks. A blank or text score fails the numeric check, while a score of 91 with status Review fails the status check. AND requires every condition to be true; OR colors the cell when any condition is true, as in =OR($B2<50,$C2="Late"). NOT reverses a test, so =NOT($C2="Done") colors every row that isn’t marked Done.

Formula Examples You Can Copy

Each formula below assumes the rule applies to a range starting in row 2. Change the column letters to match your sheet.

Color the cell or row when	Formula
Column C contains “late” anywhere, any case	=ISNUMBER(SEARCH("late",$C2))
Column C contains “Late” with that exact case	=ISNUMBER(FIND("Late",$C2))
Column C does not contain “Complete”	=NOT(ISNUMBER(SEARCH("Complete",$C2)))
Column B is empty	=$B2=""
Column B is not empty	=$B2<>""
Column B is between 50 and 79	=AND($B2>=50,$B2<=79)
Column B is greater than column C	=$B2>$C2
Column B is above the column average	=$B2>AVERAGE($B$2:$B$100)
The value in column A appears more than once	=COUNTIF($A$2:$A$100,$A2)>1
The date in column D has passed	=AND($D2<>"",$D2<TODAY())
The date in column D falls in the current month	=AND(MONTH($D2)=MONTH(TODAY()),YEAR($D2)=YEAR(TODAY()))

SEARCH ignores capitalization and FIND respects it. Both return an error when the text isn’t found, and ISNUMBER turns the result into TRUE or FALSE. The date formulas use TODAY, so the colors change on their own as days pass. The COUNTIF formula marks every copy of a repeated value, including the first one; to clean those entries out instead, remove duplicates in Excel.

Use a Built-In Rule or a Helper Column Instead

For a single threshold, you may not need a formula at all. Select the cells and choose Home > Conditional Formatting > Highlight Cells Rules, then pick Greater Than, Less Than, Between, Equal To, Text that Contains, or Duplicate Values. Enter the value, choose a preset color, and click OK.

A formula rule is the better choice when the test depends on another cell or column, or combines conditions.

If you already have an IF formula in the sheet, you can color from its result. Put =IF(B2>=80,"Pass","Review") in a helper column, such as E, and fill it down. Then select A2:D7 and create the formula rule =$E2="Review". The helper column also gives readers a label that doesn’t rely on color, and you can hide the column if you prefer.

Excel for Mac and Excel for the Web

On a Mac, select the range and choose Home > Conditional Formatting > New Rule. Set Style to Classic, then choose Use a formula to determine which cells to format. Enter the formula. Next to Format with, choose the custom format option at the bottom of the list, select a color on the Fill tab, and click OK until the dialogs close. These controls are covered in Microsoft’s Mac formula-formatting instructions. The formulas in this article work the same way on a Mac.

The same Classic rule type can also hide zero values in Excel for Mac by giving cells that equal 0 a white font.

In Excel for the web, select the range, then choose Home > Conditional Formatting > New Rule. A pane opens on the right. Check the range at the top, choose Formula as the rule type, enter your formula, pick a format, and confirm the rule. To change or delete rules later, choose Home > Conditional Formatting > Manage Rules. You need edit access to the workbook; files shared as view only show the colors but not the commands.

Copy Conditional Formatting With Format Painter

In desktop Excel, select a cell with the conditional formatting you want to copy, choose Home > Format Painter, then drag the paintbrush across the destination cells. Press Esc to stop. Double-click Format Painter if you want to apply it to several ranges. Microsoft’s conditional formatting guide documents this sequence. It copies formatting without changing the destination values, but it copies other cell formatting, such as number formats and borders, too.

Select the destination and open Home > Conditional Formatting > Manage Rules. Check the copied formula, its Applies to range, the rule order and any Stop If True setting. Relative references can shift when copied; adjust them to match the first destination cell and keep dollar signs where a row or column must stay fixed.

Test with values that should and shouldn’t trigger the rule, without overwriting real data, then save. A color showing up doesn’t mean the rule is right. To undo, remove only the copied rule or press Undo right away, leaving other rules in place.

Edit, Reorder, or Remove a Rule

All rules live in the Conditional Formatting Rules Manager. Choose Home > Conditional Formatting > Manage Rules, then set Show formatting rules for to This Worksheet to see every rule on the sheet, not only the ones on the selected cells.

• Edit Rule changes the formula or the color.
• Applies to changes the range. Type a new range, such as =$A$2:$D$200, or use the button at the end of the box to select it on the sheet.
• Move Up and Move Down set priority. Rules higher in the list win when two conflicting rules are both true, and Excel adds each new rule at the top.
• Stop If True stops Excel from checking lower rules for a cell once that rule is true.
• Delete Rule removes only the selected rule.

To remove every rule at once, choose Home > Conditional Formatting > Clear Rules, then Clear Rules from Selected Cells or Clear Rules from Entire Sheet. Clearing from selected cells also removes other rules affecting those cells, so check the manager first. Choosing No Fill alone does not remove a conditional formatting rule, because a true rule overrides a manual fill.

If the Color Is Missing or Applied to the Wrong Cells

Open Home > Conditional Formatting > Manage Rules and check the rule against this list:

• The colors are off by a row or a column. The formula must describe the first cell of the Applies to range. For B2:B7, start with B2; for A2:D7 with a row rule, use $B2.
• Only one cell, or every cell, is colored. The formula probably contains $B$2 because you clicked the cell while building it. Change it to B2 or $B2.
• Nothing is colored. Start the formula with = and make sure it returns TRUE or FALSE. A formula like =IF(B2>=80,"Green") returns text, which doesn’t trigger the format.
• Numbers don’t match. Numbers stored as text (often marked with a small green triangle) fail comparisons such as >=80. Convert them to numbers with the warning icon’s Convert to Number option.
• Cells with errors stay plain. Conditional formatting skips cells that show a formula error. Fix the error, or wrap the source formula in IFERROR.
• A different color appears. A higher rule may be winning, or Stop If True may block a lower rule. Reorder the rules.
• A cell looks blank but isn’t treated as blank. A formula that returns “” looks empty. =$B2="" treats it as blank, while ISBLANK does not.
• The rule points to another workbook. Excel can’t use references to other workbooks in conditional formatting. Copy the values into the same workbook first.
• Conditional Formatting is grayed out. The sheet may be protected. Ask the workbook’s owner to unprotect it or to make the change.

To undo your test example, select the rule in Manage Rules and click Delete Rule.

<p>For more on rule order, Stop If True, and the built-in rules that need no formula, read our guide to <a href=”https://www.solveyourtech.com/conditional-formatting-excel/”>using conditional formatting in Excel</a>.</p>

Frequently Asked Questions

Can an IF formula return a color directly?

No. IF can return text, numbers or TRUE and FALSE, but it can’t change the fill or font. Use a conditional formatting rule for the color, and use IF in a cell if you also want a visible label.

Can a formula check or count a cell’s color?

Not with standard worksheet functions. Count the condition behind the color instead, such as =COUNTIF(B2:B7,">=80") for the green cells in the first example. The article on how to count colored cells in Excel covers the other options.

Will the color update when the numbers change?

Yes. Excel re-evaluates the rule whenever the cells it refers to change, and rules that use TODAY update each day. Sorting also keeps the colors correct, because each cell is tested again in its new position.

Can the rule refer to a cell on another sheet?

In current versions of Excel, yes, as long as the sheet is in the same workbook, such as =B2>=Settings!$B$1. References to a different workbook don’t work.

How many rules can one range have?

Current versions of Excel allow many rules on the same cells, and all of them apply unless they conflict. Fewer, clearer rules are easier to troubleshoot, so combine conditions with AND or OR where you can.

Next, try the whole-row rule on a real list, and keep a label column alongside the color so the meaning is clear when the sheet is printed in black and white.