Conditional formats

Hi Gemma

Here is the note I've put together re conditional formats that apply across multiple cells and rows. Let me know if you have any feedback on it as it involves lots of steps and I'm sure it can be improved.

The trick to making this work is to apply a formula based conditional format and then check to see if the absolute reference in the formula needs to be amended to allow you to apply the format across all rows of your list.

For example: In a row that has three cells as follows:

A1 B1 C1
.5 XYZ On Track

We might want the conditional formatting for A1 to A3 go green when the status is On Track.

Highlight Cells A1 to C1
From the format menu choose Conditional Formatting
Change the condition control from "Cell value is..." to "Formula is" then click into the field box to make it active and collapse the dialogue box using the button with the red flash. Click into the A3 cell as this contains the value that will trigger the formatting.

Expand the dialogue box by clicking the button with the red flash again and note the formula:


=$C$1


The formula assumes the references must be absolute. This is OK for this line will want this format to be applied to the remaining rows in our list.

Regardless of how many records we have the column containing the test will always C but row number will.

In this case remove the dollar symbol before the reference to row 1 to switch it from Absolute to a Relative reference.

Next we need to complete the formula with the test as follows:

=$C1="On Track"

Lastly click the format button to decide the formatting that should apply if the condition is true. You might decide to set the patter to be green and the Font to White and Bold so it stands out.

Click OK and you should now find that the cells A1 to C1 go green with white text when C1 contains the text On Track. With anything else in C1 the text reverts back to normal.

Now Click on one of the cells that contain this conditional formatting rule (either A1, B1 or C1) and click the format painter button on the top toolbar (looks like paint brush). You can now "paint" this conditional format over any other records in your database (e.g. A2 through to C2, A3 to C3 and so on).

Because we removed the dollar symbol from before the row number in the Conditional formula Excel will test the contents of the current row in column C, not just the first row.


I hope this helps. I realise this is quite a long answer - do let me know if you have any questions.

Kind regards,
Andrew