Whilst changing the colour of single cells in a spreadsheet using conditional formatting is straightforward, how do we highlight an entire row?
Well it’s actually quite straightforward, we just use a simple formula to achieve the desired effect. Here’s how to do it in 3 east steps!
In the following training example, we want to identify any invoices which have exceeded our 30 day payment terms. You can download the spreadsheet Highlighting Rows here.
STEP 1: First select the range of cells to be formatted, then click the Conditional Formatting button and choose New Rule.
STEP 2: Now select Use a formula to determine which cells to format and enter the following: =$F4>30
STEP 3: Next click the Format button and choose an appropriate fill colour, then click OK – that’s it!
So how does it work? Well, notice the $ in front of the reference to the column in $F4? This tells Excel that it should hunt down the whole of column F looking for values that are greater than (>) 30. However, the dollar effectively ‘locks’ the column, preventing Excel from looking to the left or right. Because we selected the whole table of data, Excel will apply the conditional formatting to the entire row.
I hope you’ve find this short Excel tutorial useful and if so, a share would be greatly appreciated. And do please leave your feedback!