Conditional Formatting Based on Another Cell

1. The two kinds of rule, and which one you need

Conditional Formatting's menu offers a pile of ready-made rules: Greater Than, Top 10 Items, Duplicate Values, colour scales, data bars, icon sets. All of them share one limitation, and it is the reason most people end up here. A built-in rule can only test the cell it is colouring.

So "colour the revenue red when it is under 500" is a built-in rule, and "colour the whole order row when its revenue is under 500" is not, because the cells being coloured are the order number, the date, the rep and the rest, none of which know anything about revenue.

The one entry that removes the limit is Use a formula to determine which cells to format. It lets the test and the target be different cells, which is the entire subject of this page. Everything else in the menu is a shortcut for a formula you could have written.

2. Where the formula is written from

Before the explanation: you select A2:H17 and type a rule. Say which single cell you think that formula is being written about.

The top-left one, A2. Excel evaluates your formula as if it lived in A2, then copies it to every other cell in the range exactly the way dragging a formula copies it: references shift by however far the cell is from the top-left corner.

That single sentence explains every strange result people get. The formula in the rule is not a description of the range; it is one formula, written once, filled across everything.

Which gives a reliable way to write these without guessing. Write the test in a spare cell on the same row as your first data row, get it returning TRUE and FALSE correctly, then copy the text of that formula into the rule dialog. If it works in the cell, it works in the rule, because it is the same mechanism.

One practical trap while typing in the dialog: the arrow keys insert cell references instead of moving the cursor. Press F2 to switch to editing mode, or click where you want to be.

3. The dollar sign that turns a cell into a row

Here is the same rule twice, applied to the same range A2:H17, both run in Excel with the coloured cells read back afterwards.

Without the dollar sign:

=H2>800

cells coloured:  A2, A3, A11, A16

Four single cells, all in column A. The rule shifted sideways with the range, so the copy sitting in column B tests I2, the copy in column C tests J2, and so on into empty space. Only the copy in column A ever looks at column H.

With the dollar sign:

=$H2>800

rows coloured:  1001, 1002, 1010, 1015     (four whole rows)

The $ in front of the H freezes the column, so every copy of the rule, in every column, still tests column H. The row number has no dollar sign, so it moves down as the rule fills down, which is exactly what you want: row 3 tests H3, row 4 tests H4.

That is the whole rule for whole-row highlighting, and it is worth saying as a sentence you can repeat: lock the column, leave the row free. $H2. The four orders it found, 1001, 1002, 1010 and 1015, are the four with revenue over 800, which you can check against the data: 880, 850, 1100 and 880.

4. Put the threshold in a cell, not in the dialog

Typing 800 into the rule buries the most important number in the whole exercise inside a dialog nobody will open. Put it in a cell instead, lock it completely, and point the rule at it.

J1:  800                      a labelled cell somebody can type into

rule:  =$H2 > $J$1

Both dollar signs this time, because that cell must not move for anybody. Now the cut-off is visible, it is documented by whatever label you put beside it, and it is adjustable without touching the rule at all. Changing the cell from 800 to 500 was run, and the highlight went from four rows to ten, with nothing edited.

J1 = 800     4 rows highlighted
J1 = 500    10 rows highlighted

This also turns a static sheet into something a reader can interrogate. Somebody who wants to know what "large order" means can look at the cell, and somebody who disagrees can change it and watch the page respond, which is a far better conversation than one about your formatting.

It is worth saying that choosing the number is the hard part, not wiring it up. A threshold picked because it is round is a decision dressed as an observation, and picking a cut-off from the data is a page of its own.

5. Applies To, and why it is the setting that goes wrong

Every rule owns a range, shown as Applies to in Manage Rules. It is set from whatever you had selected when you created the rule, and it is the most common reason a correct-looking rule colours the wrong things.

Three habits that prevent nearly all of it.

Select the data before you open the dialog, starting at the first data cell. If your selection includes the header row, the rule is written for the header and everything is off by one row.

Check Applies to afterwards. Home, Conditional Formatting, Manage Rules. The box is editable, so a wrong range is a fifteen-second fix rather than a rebuild.

Use a table. A rule applied to a range stops at the last row it knew about, so rows added later are unformatted. A rule applied inside a real Excel table extends itself as the table grows, which is the same argument as for every other formula in the workbook.

6. Rule order and Stop If True

Before the explanation: a cell qualifies for two rules, one that makes it red and one that makes it bold. Say what happens.

Both apply, unless they conflict. Excel walks the rule list from the top and applies each rule that matches, so red and bold combine happily. Where two rules set the same property, the one higher in the list wins, and that ordering is something you control with the arrows in Manage Rules rather than something Excel decides.

That gives the standard pattern for banding. Write the rules in the order most extreme first:

1.  =$H2>=1000     dark
2.  =$H2>=800      medium
3.  =$H2>=500      light

An order worth 1,100 matches all three, and because the dark rule is first, dark wins the fill. If you had written them the other way round, everything above 500 would be light and the other two rules would never show.

Stop If True is the checkbox beside each rule that says "if this rule matches, do not consider any rule below it". You need it when a lower rule would add something you do not want combined, and the classic use is an exclusion: put a rule at the top that matches the rows you want left alone, give it no formatting at all, and tick Stop If True. Everything it catches is now immune to every rule underneath.

7. What conditional formatting does not do

Before the explanation: a rule has coloured four rows. Say whether the cells in those rows are now coloured.

They are not. Conditional formatting paints the display and leaves the cell alone. Checked in Excel on a highlighted cell that had no fill of its own:

the cell's own fill        none
what is on the screen      the rule's colour

Two consequences follow, and they are the two most common frustrations with the feature.

You cannot count the colour. There is no COUNTCOLOUR, and the workarounds that read a cell's fill see the cell's own fill, which is empty. Count the condition instead, with the same test the rule uses:

=COUNTIF(H2:H17, ">800")      4

That 4 matches the four rows the rule coloured, because it is the same question asked of the data rather than of the paint. Anything you want to count, total or filter needs to exist as a formula somewhere. The colour is for the eye only.

It does not change any number. The revenue column still totals 9,890 whatever the rule does, which is a relief rather than a limitation: formatting cannot corrupt data. It is also the reason a highlight is not a finding. Colouring the four big orders red does not put "four orders above 800" anywhere a reader can quote, and a dashboard needs the sentence as well as the paint.

Picture your own most-used sheet for a moment. If somebody printed it in black and white, how much of what it currently communicates would survive? Everything that would not is something you should also be writing down as a number.

8. Rules that breed

This is the failure that turns a tidy workbook into a slow one, and it happens without anybody doing anything unusual.

A sheet was set up with exactly one rule, applied to A1:A10. Two ordinary copy-and-pastes of single cells to new locations were performed. Afterwards, Excel reported:

rules before   1
rules after    3

rule 1  applies to $E$6         =E6>0
rule 2  applies to $C$3         =C3>0
rule 3  applies to $A$1:$A$10   =A1>0

Copying a cell copies its conditional formatting, and the copy cannot join the original rule, so it becomes a new one with its own range. Do that on a real sheet a few hundred times over a year, which is what normal editing looks like, and Manage Rules fills with near-identical entries covering fragments of ranges. The symptoms are a file that opens slowly, scrolls badly, and formats inconsistently in places nobody can explain.

Two fixes. To prevent it, paste values only, Ctrl+Alt+V then V, when moving data around inside a formatted sheet. To repair it, open Manage Rules, set the scope dropdown to This Worksheet so you can see all of them, delete the fragments, and rebuild one rule over the correct range. It is a five-minute job that people put off for years.

Edge cases that break a rule quietly

Six that get past a first build.

The header row was in the selection. The rule is then written for the header, so every test is one row out and the highlighting looks almost right. Select from the first data row.

Blank cells count as zero. A rule like =$H2<500 lights up every empty row below your data, because blank is treated as 0. Guard it: =AND($H2<>"", $H2<500).

Text compared with a number. If the tested column contains numbers stored as text, the comparison quietly returns FALSE for those rows, and they never highlight. This is the same class of problem as a total coming up short, and it is worth checking the column type before blaming the rule.

Inserting a row splits the range. Inserting inside a formatted range usually extends it, but inserting at the edges often does not, and the new row is simply outside. Manage Rules shows the truth in the Applies to box.

Colour that is the only carrier of the message. Roughly one man in twelve of northern European descent has a red-green colour vision deficiency, so a red-against-green rule communicates nothing to a real fraction of any audience. Use a fill difference plus something non-colour, bold or an added symbol, and it costs nothing.

Volatile formulas inside rules. Rules containing functions that recalculate constantly, or rules applied to whole columns, make every scroll and edit slow. Apply rules to the used range, not to A:A.