Conditional Formatting

Hi Benjamin

Thank you for your question. It's very useful to highlight the whole row if a condition is met (rather than just the cell triggering the condition). There is a way of achieving this result in Excel. Here is a way to do this. It is straightforward to set up and the steps are below. Let me know if this answers your question.

To make this work we need 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
100 XYZ On Track

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

Highlight Cells A1 to C1

From the Home ribbon click the Conditional Formatting button and choose New rule
Select the option - Use a formula to determine which cells to format
Click into the box to start building your formula then click into your spreadsheet onto the A3 cell - this contains the value that will trigger the formatting.

The formula in your dialogue box is being built and looks something like

=$C$1


The formula assumes the references must be absolute. The column certainlyl needs to be locked but the row should be unlockd.

In this case remove the dollar symbol before the reference to row 1
It should look like this

=$C1

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 Fill 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 Home Ribbon (looks like paint brush). You can now "paint" this conditional format over any other records in your table (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