Excel Tip: Conditional Formatting based on other cells

Excel's conditional formatting feature typically allows you to set the format of a cell based upon its contents, and allows you to define all sorts of rules upon which to do this.

This is great, but what if you want the cell's format to be based upon the contents of a different cell (or cells)?

A typical use for this might be for a traffic light graphic giving a quick visual aid to understanding a set of numbers.

Fortunately Excel provides a relatively straight-forward way of doing this.

The answer is to use the formula option in Conditional Formatting.

Highlight the cell, or range of cells, that the conditional formatting is to be applied to.

Select Conditional Formatting from the Home ribbon and select "New rule". Then select the bottom option from the list - "Use a formula to determine which cells to format":

You can then type a formula into the box that will be used as the criteria to determine whether each cell is to be formatted, or not.

The criteria should start with an "=" sign and then follow the same rules as the condition argument of an IF function.

Where you are applying the conditional formatting to more than one cell, the criteria should be written from the perspective of the top left cell of the range being formatted. This becomes important in how it will treat relative cell references. Cell references can be made absolute using the dollar sign, as in a regular Excel function.

Once you are happy with your criteria, click the Format button and set the formatting that you want to apply should the criteria be met.

Some examples should make this a little clearer...

Let us take a simple one first. Say we wished to fill cell C3 with a red background, if cell A1 was negative.

We would click in cell C3 and open the dialog box as above. We then enter the following into the criteria box:

=A1<0

We then click the format button and select a red fill.

As we are only formatting one cell, it doesn't matter whether we use dollar signs or not.

Now, let's say we want the whole range C1:G10 to be coloured red if A1 is negative.

We would select the range C1:G10, and then do the same as before, however now we need to consider how we want cells other than the top left cell of the range to be treated.

In this case we want them to still look at the contents of A1 so we will need to fix the reference to A1 by adding the $ sign in front of both the row and column reference. Our criteria should therefore read:

=$A$1<0

If we didn't add the $ signs, then the criteria would be looking at a different cell each time.

C1 would be looking at A1 (as C1 is the top left cell in the range), however C2 would be looking at A2, D1 would be looking at B1 and D2 would be looking at B2, etc.

We may want the reference to be relative in other cases. Say we wanted to apply the conditional formatting to the same range, C1:G10, but this time wanted to fill the row (columns C to G) red when the contents of column A is negative.

We would do exactly as above but this time apply the dollars as follows:

=$A1<0

We are still fixing the column but not the row, so now C1 will look at A1 (as before), and D1, E1, F1 and G1 will also look at A1 as we have fixed the column to always be A. However C2 to G2 will all look at A2 as the row reference is still relative.

The criteria you use can refer to more than one or a range of cells, so it could be any of the following:

=SUM(A1:A10)>=10,000
=A1-B1=0
=A1>B1
=SUM(A1:A10)>B1

Basically anything you could have entered as the criteria in an IF function (with an "=" sign in front of it).

You can also apply more than one rule to the same cell, so to apply our traffic light system, for example, we might have the following rules:

• The ordinary format of the cell set to an amber (or orange fill) so that the fill is this colour if the other rules don't apply. You don't need Conditional Formatting for this;
• New Rule =A1>=500  to give a fill of green
• New Rule =A1<=-500 to give a fill of red

This will colour the cell red if A1 is less than or equal to -500, amber if it is greater than -500 but less than 500, and green if it is 500 or more.

There, you can now do pretty much anything with Conditional Formatting!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".