Highlight an Entire Row in Excel Based on One Cell Value

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.

EasyExcel_15_1_Highlight entire row


EasyExcel_15_2_Highlight entire row

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:

=$A3=$F$3

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.

EasyExcel_15_3_Highlight entire row

Done! Choose a name in F3, and all the rows with that name will be highlighted!

EasyExcel_15_1_Highlight entire row

More on Conditional Formatting:

Are you using a non-English version of Excel? Click here for translations of the 140 most common functions in 17 different languages:

Catalan
Czech
Danish
Dutch
Finnish
French
Galician
German
Hungarian
Italian
Norwegian
Polish
Portuguese (Brazilian)
Portuguese (European)
Russian
Spanish
Swedish
Turkish

4 thoughts on “Highlight an Entire Row in Excel Based on One Cell Value

  1. Hi.
    Great article.
    maybe you can help with my problem, that is similar to your explanation.
    I have a matrix that each cell is composed by a function of two parameters (two columns). The formula is “=COUNTIFS(T2:T99,”15″,V2:V99,”14″)”, so it’s counts when in one column we receive 15 and in the other 14.
    For instance, I receive the number 3 – so I have three rows that match.

    I want, when I select the cell with the number 3 (that I receive from the formula) it will highlight the relevant rows..
    could you help me??

    Thank you

Leave a Reply to Audun Danielsen Cancel reply

Your email address will not be published. Required fields are marked *


× 4 = twelve