For a long time I’ve been aware of – and often used – Microsoft Excel’s conditional formatting feature. It is in easy reach on the Home ribbon and allows you to do things like highlight cells that contain a particular value or even to apply a graduated colour scale, allowing you to visually interpret the balance of high, medium and low scores. What I didn’t know how to do, until today, was to highlight whole rows based on the value in a given column.

You start by selecting the overall range you are applying the formatting to. In my case, it was a large table with about 15 columns and approaching 3,000 rows, structured as an Excel table. I don’t know if the formatting is clever enough to work out that it is aimed at a table but, in this case, the data set is now fixed so I’ll leave that for future investigation.

You then pick the option where you highlight rows using a formula. A suitable formula would be something like =$C2="Upgraded". That would look for any row where the content of column C matches the value. I think the number is irrelevant as long as it falls within the specified range. You can then apply a suitable set of formatting commands – for my upgraded machines, I gave them a green background.

I’m expecting that this will keep the formatting consistent. Previously, the colours I had been using often got mixed up as a updated sections of the data (for example filling a consistent department name across a filtered range) and, although the data itself was fine, the colours weren’t. Since the colouration is now derived directly from the data, this should keep things in order and I can think of a number of other situations where this will be very handy.