If you want to highlight a cell in Excel based on its value, it’s pretty straight forward: Just choose Conditional Formatting from the Home ribbon. But what if you want to highlight the entire row based on the value in just one of the cells? We’ll use Conditional Formatting here too, but with a slightly different approach than we’re used to.
This is what we want: Choose one of the names in the table below and highlight the entire row for each occurence of that name.
First, let’s look at the criterion field. This is where we choose which rows we want to highlight. In my example, I’ve chosen Robert, so every row that has the name Robert in column A will be highlighted. You might want to create a drop-down menu in this field, but it’s not necessary. Check out this post to learn how to create a drop-down list in 3 minutes (opens in a new tab).
Conditional Formatting checks for a TRUE or FALSE, and formats the cells that are TRUE. We want to check if the value in column A is equal to the value in our name field (F3).
The statement =A2=F3 will return TRUE if we choose Robert in F3, and FALSE for any other name.
In order to make this work for the entire row 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 F3. This is the formula we will use for the Conditional Formatting:
Let’s do it: 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 F3, and all the rows with that name will be highlighted!
More on Conditional Formatting:
- Hide Future Dates in Excel with Conditional Formatting
- How to Find Duplicates and Triplicates in Excel
- The Easiest Way to Hide Zeros in Excel
- Create a Search Box in Excel without VBA
Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.