Conditional Formatting Formulas in Excel: Rules and Examples

A conditional formatting formula is a test that returns TRUE or FALSE for each cell in a range. When the answer is TRUE, Excel applies the color, font or border you picked. You write it once, for the top-left cell of your selection, and Excel adjusts it for every other cell. The hard part is not the formula itself. It is knowing which part of the reference to lock.

This guide walks through the setup, the reference rules that trip people up, ten formulas you can copy, data bars, rule order, and a checklist for rules that refuse to work.

TL;DR

Select the range first, then go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
Write the formula for the top-left cell of your selection. It must return TRUE or FALSE.
To color a whole row from one column, lock the column and free the row: =$D2="Complete".
Data bars are a built-in format, not a formula rule, but you can still control their scale and limits.
When a rule misbehaves, open Manage Rules and check the "Applies to" range first.

How a formula-based rule works
The reference rules that make or break your formula
Highlight a row based on a cell value
Conditional formatting based on another cell
Ten formulas worth keeping
Data bars: formatting without a formula
Rule order and conflicts
Why your rule is not working
Our perspective: practice the logic, not the menu
Practice these formulas for free
FAQ
Sources

How a formula-based rule works

Excel's built-in presets, such as "Greater Than" or "Duplicate Values", cover simple cases. A formula rule covers everything else: comparing two columns, checking dates, testing several conditions at once. According to Microsoft's conditional formatting guide, you create these rules from the Home tab, and the formula has to evaluate to a logical TRUE or FALSE (or 1 or 0) to trigger the format.

Here is the sequence:

Select the whole range you want formatted, for example A2:F200. Do this before opening the dialog. The active cell, usually the top-left one, is the one your formula refers to.
Go to Home > Conditional Formatting > New Rule.
Choose Use a formula to determine which cells to format.
Type the formula, starting with an equals sign, using the top-left cell's address.
Click Format, choose a fill or font, and confirm with OK.

The same dialog exists on the Mac version, and Microsoft's Mac guide shows the same pattern of adding a custom formula when presets are not enough.

A quick test before you commit: type your formula into a spare cell next to the data. If it returns TRUE or FALSE, it will work as a rule. If it returns an error or a number you did not expect, fix it there, where you can see the result.

The reference rules that make or break your formula

Most failed rules come down to one thing: how the cell reference moves as Excel applies the rule across your range. Excel treats your formula the way it treats a formula you copy and paste. A plain reference like A2 shifts both ways, while a dollar sign freezes whatever follows it. Microsoft's page on relative, absolute and mixed references describes the same behavior.

Reference	What stays fixed	Typical use in a rule
A2	Nothing	Test each cell against the cell in its own position, such as comparing two parallel columns
$A2	Column	Color a whole row based on one column
A$2	Row	Color a whole column based on a header or top row
$A$2	Column and row	Compare every cell to one fixed cell, such as a target or threshold

If you want a refresher on locking cells, our guide to the absolute reference ($A$1) covers the logic in detail, and it applies to rules exactly as it does to normal formulas.

The diagonal stripe problem

You have probably seen it: you meant to highlight whole rows, but the colors appear in a staircase pattern. That happens when the column is not locked. If you select A2:F200 and write =D2="Complete", then in column B Excel tests E2, in column C it tests F2, and so on. The test slides sideways. Write =$D2="Complete" and every cell in the row looks at column D.

The second classic mistake is writing the formula for the wrong cell. If your selection starts at A2 but your formula refers to row 1 or row 5, every result is offset. Always write the formula as if you were typing it into the top-left cell of the selection.

Highlight a row based on a cell value

This is the most searched use of conditional formatting, and it takes one formula.

Goal: shade the entire row green when the Status column (column D) says Complete.

Select A2:F200.
New Rule > Use a formula.
Enter =$D2="Complete".
Pick a green fill.

For a numeric condition, say highlight rows where Sales in column C exceeds 5,000, use =$C2>5000. For text that only needs to contain a word, use =ISNUMBER(SEARCH("late",$D2)). SEARCH is not case sensitive and returns a position number when it finds a match, so ISNUMBER turns that into TRUE.

To change the row color for several statuses, create one rule per status. Each rule has its own formula and its own fill, and they sit together in the manager (more on that below).

Conditional formatting based on another cell

Presets only look at the cell they format. Formula rules can look anywhere on the sheet. Here are the patterns readers ask for most.

Compare two columns in the same row. Highlight actual spend in column C when it exceeds budget in column B: =$C2>$B2

Apply it to C2:C200 only if you want just the actual cell colored, or to A2:C200 if you want the row.

Compare every cell to one fixed cell. Suppose your target sits in H1. To flag every value in B2:B200 that falls below it: =B2<$H$1

The target is locked with both dollar signs. The tested cell stays relative.

Reference a cell on another condition. Highlight a cell only if its neighbor is filled and it is empty. This answers the common "G is filled, so H should turn red if blank" request: =AND($G2<>"",$H2="")

Apply it to H2:H200. AND returns TRUE only when both parts are true.

Use another sheet. Current Excel versions let a rule refer to a different worksheet in the same workbook. References to other workbooks are the sticking point: Microsoft notes that formula rules cannot use direct array constants or links to other workbooks without a workaround such as a named range or INDIRECT. If a rule needs outside data, the simplest fix is to bring that data into your workbook.

We have a dedicated walkthrough on conditional formatting based on another cell if you want more variations on this theme.

Conditional formatting based on another cell — overview diagram

Ten formulas worth keeping

Each assumes your selection starts in row 2. Adjust the cell addresses to your layout.

#	What it does	Formula
1	Row where status is Complete	=$D2="Complete"
2	Value above a fixed target in H1	=B2>$H$1
3	Overdue dates, not yet paid	=AND($E2
4	Due in the next 7 days	=AND($E2>=TODAY(),$E2<=TODAY()+7)
5	Duplicates in a list	=COUNTIF($A$2:$A$400,A2)>1
6	First occurrence only of each value	=COUNTIF($A$2:$A2,$A2)=1
7	Either of two conditions	=OR($C2>5000,$D2="VIP")
8	Blank cell inside a filled row	=AND($A2<>"",B2="")
9	Every other row	=MOD(ROW(),2)=0
10	Value is the maximum in its column	=B2=MAX(B$2:B$200)

A few notes on these:

Formula 5 comes straight from Microsoft's formula guide, which uses COUNTIF to flag duplicates. Notice the range is locked and the tested cell is not. If COUNTIF is new to you, our COUNTIF guide explains the counting logic, and the COUNTIFS page covers multiple criteria.
Formula 6 uses an expanding range: the start is locked, the end is not. It colors only the first time a value appears, which is handy for spotting where repeats begin. If you need to count distinct entries rather than color them, see count unique values.
Formulas 3 and 4 depend on TODAY(), so the colors change every day without any edits from you.
Formula 9 gives you banding that survives sorting and filtering far better than manual fills. For simple tables, a preset table style does the same job, as Microsoft's alternate row page explains.

Percentages and thresholds

Rules that compare percentages need the cell to hold a real number, not text that looks like one. If a growth column displays 12%, the cell holds 0.12, so the rule is =$G2>0.1, not =$G2>10. Our percentage formulas guide covers how Excel stores them.

Data bars: formatting without a formula

Data bars are one of the most searched conditional formats, and they work differently from formula rules. Per Microsoft's page on data bars, color scales and icon sets, they are visual effects that make it easier to compare many cell values at once. A longer bar means a higher value, a shorter bar a lower one.

To add them:

Select the numbers.
Home > Conditional Formatting > Data Bars.
Choose a gradient or solid fill.

That is enough for a quick scan. A few things are worth knowing before you share the sheet:

Edit the rule through Manage Rules > Edit Rule if you want to set a fixed minimum and maximum instead of letting Excel pick them from your data. Fixed limits stop one extreme value from squashing every other bar.
Show bar only is an option in the same dialog. It hides the number and leaves just the bar, which suits dashboards.
Negative values have their own color and axis settings in the same editor.
Bars do not take a TRUE/FALSE formula. If you want bars only for certain rows, a common workaround is a helper column that returns the value when your condition holds and blank otherwise, then apply bars to the helper column.

A good pairing: data bars on a sales column, plus a formula rule that turns the product name red when sales fall under target. The bar shows scale, the color shows the exception.

Rule order and conflicts

When you stack several rules on one range, the order matters. Microsoft's documentation explains that rules touching different properties both apply when both are TRUE. If one rule makes the font bold and another sets the fill, you get both. When two rules set the same property to different values, the rule higher in the list wins.

Open Home > Conditional Formatting > Manage Rules, set "Show formatting rules for" to This Worksheet, and use the arrows to reorder. Put your most specific rule on top. For instance, a rule for "Overdue and unpaid" should sit above a general "Unpaid" rule, or the specific one never shows.

How conditional formatting rule priority works

We see people assume that every matching rule shows up in the end. Only non-conflicting formats combine. If two rules fight over fill color, one loses.

Why your rule is not working

Work through this list in order. It solves most problems in under two minutes.

Check the "Applies to" range. Microsoft's guidance points to Manage Rules as the first stop, since a mismatch between the range where the rule is active and the cells you meant to format is the most common cause. Copying and pasting rows often splits one rule into several fragments with different ranges.
Check the top-left logic. Click the rule, choose Edit Rule, and read the formula as if it sat in the first cell of the range. Is it pointing at the right row?
Check the dollar signs. Stripes mean the column is not locked. A single fixed color on every cell, or none at all, often means the row is locked when it should not be.
Check for text that looks like numbers. A value stored as text will not satisfy >5000. Convert it, or test with VALUE.
Check for errors. A formula that returns an error does not trigger the format. If your test divides by something that can be zero, wrap it: =IFERROR($C2/$B2>0.2,FALSE).
Check precedence. Another rule above yours may be setting the same property.
Check for manual fills. A cell filled by hand does not lose that fill because a rule says otherwise in every case, so clear the manual formatting first if colors look stuck.

If you edit a rule and nothing changes, press OK twice rather than once. The Manage Rules window has its own Apply button, and closing it without confirming discards changes.

Most conditional formatting tutorials show a finished sheet and ask you to repeat the clicks. People then forget the logic within a week. What sticks is writing the TRUE/FALSE test yourself, seeing the highlight appear or not, and understanding why.

That is why we build LogicExcel around typing formulas and getting instant feedback in an Excel-like grid. Reading about $D2 versus D$2 is easy to forget. Getting the stripes wrong once and fixing them yourself is not. If you write formulas for reports, make it a habit: test the condition in a spare cell first, then paste it into the rule. You will catch most mistakes before they reach the dialog.

A second habit: keep rules few and named in your head. Three clear rules on a sheet beat fifteen overlapping ones that nobody can maintain. When a colleague opens your workbook six months from now, the Manage Rules window is the only documentation they have.

> — Thomas Houza

Practice these formulas for free

Conditional formatting formulas rely on logical tests, references and COUNTIF-style counting, all of which you can drill without installing anything. Our free Excel exercises give you instant feedback and explain each mistake, with no account needed. If you want a structured path from basic formulas to reporting, the Excel courses start with the foundations, and the functions cheat sheet is handy while you write rules. If you are building up to a job test, try the free Excel test for interviews.

FAQ

Can I use any formula in conditional formatting?

Nearly any formula that returns TRUE or FALSE works, including IF, AND, OR, COUNTIF and date functions like TODAY. The formula must start with an equals sign. Some constructs are restricted, such as direct array constants and references to other workbooks, which Microsoft says need workarounds like named ranges.

How do I highlight a row based on one cell?

Select the full range of rows, create a formula rule, and lock the column of the cell you test, such as =$D2="Complete". Leave the row number unlocked so each row checks its own value. If only one cell colors instead of the whole row, check that your selection covers every column you want shaded.

Why do my colors form a diagonal pattern?

The column reference is not locked. Excel shifts a plain reference like D2 sideways as it moves across columns, so each cell tests a different neighbor. Change it to $D2 and the pattern resolves.

Do data bars use a formula?

No. Data bars are a built-in visual format, and you adjust them through the Edit Rule dialog, not with a TRUE/FALSE test. To show them only for certain rows, use a helper column that returns the value when your condition is met and a blank when it is not, then apply the bars to that column.

What happens when two rules conflict?

If both rules change different properties, such as font and fill, both apply. If they set the same property differently, the rule higher in the Manage Rules list wins. Use the Move Up and Move Down arrows to change that order.

Conditional Formatting Formulas in Excel: Rules and Examples