A normal Excel formula can’t change a cell’s color, but conditional formatting can use a formula to do it. Select the cells, go to Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, type a formula that returns TRUE when the cell should change color, such as =B2>C2, pick a fill under Format, and select OK. The color updates automatically whenever the values change.
Applies to: Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016 on Windows and Mac, and Excel for the web. Last reviewed October 8, 2026.
Step-by-step
- Select the cells to color, for example B2:B100. Start from the top cell.
- Go to Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Type the formula for the first selected cell (B2). Excel adjusts it for the other cells automatically.
- Select Format, choose a color on the Fill tab, and select OK twice.
The formula must start with = and return TRUE or FALSE. Use $ to lock a column or row that shouldn’t shift, such as =$C2 to always check column C.
Formula examples
- Compare two cells: =B2>C2 colors B2 when it’s bigger than C2, for example actual vs budget.
- Above a target in another cell: =B2>$F$1 colors values above the target typed in F1.
- Overdue dates: =AND(B2<>””,B2<TODAY()). More date rules are in how to color cells based on date.
- Contains a word: =ISNUMBER(SEARCH(“urgent”,B2)). See how to highlight cells based on text.
- Duplicates: =COUNTIF($B$2:$B$100,B2)>1.
- Blank cells: =ISBLANK(B2), handy for spotting missing entries.
- Errors: =ISERROR(B2) colors #N/A, #DIV/0! and other errors.
- Weekends: =WEEKDAY(B2,2)>5 colors Saturdays and Sundays.
- Every other row: =MOD(ROW(),2)=0.
- Different from the other sheet: =B2<>Sheet2!B2. See how to highlight differences in two columns.
Color a whole row from one cell
Select the full table (A2:F100) and use a formula with the column locked, such as =$D2=”Late”. Every cell in the row checks column D, so the whole row turns color. More examples are in how to color cells in Excel based on value.
Several colors from one column
Add one rule per color, for example =B2>=90 green, =AND(B2>=70,B2<90) yellow and =B2<70 red. Manage their order in Conditional Formatting > Manage Rules; higher rules win when two apply.
Example: a simple budget tracker
Column B holds the budget for each category and column C the amount spent. Select C2:C30 and add two rules: =C2>B2 with a red fill for overspending, and =C2>=B2*0.9 with an orange fill for categories within 10% of their limit. Put the red rule first in Manage Rules and tick Stop If True so a category that’s over budget shows only red. As you enter spending through the month, the colors update on their own and show where to cut back.
Test the formula first
If a rule isn’t working, type the same formula in an empty cell next to your data, for example in D2, and copy it down. Where it shows TRUE, the rule should color the cell. This makes it easy to spot a wrong reference or a missing dollar sign before you build the rule.
Troubleshooting
Nothing changes color
- The formula was written for the wrong starting cell. It must match the top-left cell of the selection.
- Numbers are stored as text, so comparisons fail. Convert them to numbers.
- The formula returns a value rather than TRUE or FALSE. Wrap it in a comparison.
The wrong cells change color
Check the dollar signs. =$B$2>C2 always compares with B2; =B2>C2 moves with each row.
Copying cells copies the rules too
That’s expected. To copy only rules, use Format Painter; see how to copy conditional formatting rules.
Frequently asked questions
Can an IF formula return a color?
No. IF can return text or numbers only. Use IF’s logic inside a conditional formatting rule instead.
Can I count cells by color afterwards?
Count by the same condition with COUNTIF instead; it’s more reliable than counting colors.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.