Do you want to highlight the entire row in your Excel report based on the value of one of the cells in the row? There is an easy way to do this in Excel: Conditional Formatting. It’s easy, and it only takes 3 minutes!
The table below contains sales figures for several different sales reps, and we want to choose one of the sales reps and have all the rows for that person highlighted.
First, let’s create a field for the criterion. If you have 3 extra minutes to spend, you can make a drop-down menu with the names. Take a look at this post about drop-down menus to see how to do it (opens in a new tab). You don’t have to do this – an empty field with no data validation works fine too.
As we’ve seen in a previous post, Conditional Formatting requires a TRUE or FALSE. We want to check if the value in column A is equal to the value in our name field (G2).
=A2=G2 will return TRUE if we choose Robert in G2, and FALSE for any other name.
In order to make this work for the entire table we have to lock the column reference A (we are not interested in the values in B, C and D) and of course, the reference to the criterion in G2, so this is the formula we will use for the Conditional Formatting:
Select all the cells in the table, click on Conditional Formatting from the Home ribbon and choose New Rule (Shortcut: Alt => H => L => N). Choose “Use a formula to determine which cells to format” and type the formula in the formula field.
Done! Choose a name in G2, and all the right rows are highlighted!