Highlight Rows in a List with Conditional Formatting and Data Validation

Locked lesson.

Upgrade

Quick reference

Highlight Rows in a List with Conditional Formatting and Data Validation

Use Conditional Formatting and Data Validation to highlight duplicate rows in a dataset. 

When to use

This technique is useful when we want to make a selection in a data validation drop-down list and have it highlight all matches in a table. 

Instructions

In this example, we want to select a pizza name from a drop-down list and highlight all orders that match the pizza name in the table below. 

We are going to do this using a combination of data validation and conditional formatting with formulas. 

Format as an Excel Table

To ensure our formulas are dynamic and update when new data is added, we need to format our data as an Excel table. 

  • Click anywhere in the dataset. 
  • Press CTRL+T. 
  • Ensure My table has headers is selected. 
  • Click OK. 
  • From the Table Design tab, in the Properties group, name the table 'PizzaOrders'. 

Extract a Unique List

Next, we need to extract a unique list of the pizza names from the orders table. 

  • Click in an empty cell in the worksheet. 
  • Type =UNQUE(PizzaOrders[Pizza Name])

Create a Data Validation List

Now that we have a unique list of values, we can use them to create our data validation drop-down list. 

  • Click in cell C6. 
  • From the Data tab, in the Data Tools group, click Data Validation. 
  • Click the drop-down arrow in the Allow section and choose List. 
  • Click in Source.
  • Select the first cell of the unique list and add the # symbol. 

  • Click OK. 

Setup a Conditional Formatting Rule

Now we need to add some functionality so that when a pizza is selected from the list, all orders that match the pizza name are highlighted in the orders table. 

  • Click in the 'PizzaOrders' table and press CTRL+A to select all. 
  • From the Home tab, in the Styles group, click Conditional Formatting. 
  • Click New Rule. 
  • From Select a Rule Type, choose Use a formula to determine which cells to format. 

Our formula needs to look in column C for the pizza name that's been selected in cell C6 and if it finds a match, apply cell formatting. 

By default, Excel will apply absolute referencing to cell C11. If we were to leave it like this, the cell formatting would only apply to cell C11. 

  • Press F4 twice to lock the column but not the row. 

We are checking if the pizza name in column C matches the pizza name in cell C6. 

  • Click Format. 
  • Choose how to format the cells. 
  • Click OK. 

Hints & tips

  • If you are using a version of Excel that doesn't have the UNIQUE function, you can copy and paste the pizza names and then use the Remove Duplicates tool (Data > Remove Duplicates) to achieve the same result. 
  • To hide the list of unique values, select the column, right-click, and choose Hide from the menu. 
Login to download
  • 00:04 In this lesson, we're going to take a look at how we can use conditional formatting
  • 00:09 with a formula and data validation to highlight duplicate rows in a data set.
  • 00:14 And this can be a really useful little thing to know how to do.
  • 00:18 And you can apply it in many different scenarios and
  • 00:21 to many different types of data set.
  • 00:23 Now, once again, we are working with our Pizza Orders data.
  • 00:27 Now maybe what I want to do is to be able to highlight certain pizza
  • 00:31 names within this list.
  • 00:33 And what I want to have is at the top here where we have Pizza Name, I
  • 00:37 want to have a little drop-down list where I can select, for example, Hawaiian pizza,
  • 00:42 and it's going to highlight all the Hawaiian pizza orders in the table below.
  • 00:46 And this is a pretty simple effect to achieve.
  • 00:49 Now again, if we want to make this completely dynamic,
  • 00:52 as in if we add new pizzas onto the end of this list,
  • 00:55 everything's going to update and our data validation is still going to work,
  • 01:00 then we want to make sure that we put our data into a table first.
  • 01:04 So let's do that, Ctrl+T.
  • 01:07 Yes, my table has headers, let's click on OK.
  • 01:11 And I'm going to give my table a name.
  • 01:13 So let's call this Pizza Orders, and hit Enter.
  • 01:20 So now that we've done that,
  • 01:22 the first thing we need to do is set up our data validation dropdown list.
  • 01:26 And remember, your data validation needs to contain a list of unique values,
  • 01:31 i.e we don't want the same pizza repeated multiple times in the list.
  • 01:36 So in order to do that,
  • 01:38 we need to get a unique list of all of the pizza names in column C.
  • 01:43 Now I'm going to use the UNIQUE function to do this.
  • 01:45 If you don't have access to the UNIQUE function, so maybe you're using a slightly
  • 01:50 older version of Excel, you can simply copy and paste this into another area
  • 01:54 of the spreadsheet and use the Remove Duplicates tool.
  • 01:57 So over here, I'm going to type in =UNIQUE, and
  • 02:02 let's just select our pizza names.
  • 02:06 Now remember, because I'm using a table, I have table references there.
  • 02:10 So it's going to pull out a unique list of the pizza names from the Pizza Orders
  • 02:15 table.
  • 02:15 Let's close the bracket, hit Enter, and we now have our unique list of pizzas.
  • 02:20 So now that I have this list, I can use it to build my data validation dropdown.
  • 02:25 So let's click in cell C6, we're going to go up to the Data tab and
  • 02:30 into Data Tools, and we're going to choose Data Validation.
  • 02:35 Now we're creating a list, and the source for our list is my unique list just here.
  • 02:42 Now if you have used the UNIQUE function,
  • 02:45 you don't necessarily have to select the entire range,
  • 02:48 you can just click on the first one and then press the hash or the pound symbol.
  • 02:53 Because this is a dynamic array function,
  • 02:56 that hash symbol basically says, include everything in this list.
  • 03:01 So let's click on OK.
  • 03:03 Now immediately you see that I have a dropdown arrow just here, and
  • 03:07 I have all of those pizza names, and I can now happily select them from this menu.
  • 03:12 So now, if I go through and I start selecting different pizzas,
  • 03:15 there's nothing happening.
  • 03:17 So this doesn't really have too much of a use at the moment.
  • 03:21 Now what I ideally want this to do is that when I select a pizza name from this
  • 03:26 dropdown list, I want it to highlight
  • 03:28 the entire row wherever that pizza name occurs in the table below.
  • 03:33 So for this we're going to add some conditional formatting and
  • 03:36 we're going to use a formula.
  • 03:38 Now, the first thing here that's very important is you need to make your
  • 03:41 selection.
  • 03:41 So we want our conditional formatting to apply to all of our table data.
  • 03:47 So I'm going to select all of the columns.
  • 03:50 Let's go up to Home.
  • 03:51 Let's go to Conditional Formatting,
  • 03:54 and we're going to create ourselves a new rule.
  • 03:57 Now we want to use a formula to determine which cells to format.
  • 04:03 So, what formula do we need in here?
  • 04:05 Well, we're going to say, =.
  • 04:08 Now, the thing that we're matching is the pizza name.
  • 04:10 So I'm going to select the first pizza name in cell C11.
  • 04:14 Now notice that when you add a formula in this way into this conditional formatting
  • 04:19 pane, it automatically makes that cell reference absolute,
  • 04:22 we have those dollars in front of the row and the column.
  • 04:26 Now if I left this as absolute,
  • 04:27 this conditional formatting is not going to work.
  • 04:31 What we need to do is change this to mixed referencing.
  • 04:34 So I'm going to press the F4 key once, twice, so
  • 04:37 that we only have dollar symbol in front of the column letter.
  • 04:42 Now what that means is that when we select the formatting that we want to apply, it's
  • 04:47 going to be able to travel across the row, but we're always going to be looking for
  • 04:52 the pizza name in column C, because column C is locked and the row isn't.
  • 04:56 So we're looking in column C effectively for whatever we have in cell C6.
  • 05:03 Now I am going to keep this locked, I am going to keep it absolute because this is
  • 05:08 never going to change, we're always using the value from cell C6.
  • 05:12 So now that we have our formula,
  • 05:14 we can simply choose the formatting that we want to apply to the entire row.
  • 05:18 So you could go really fancy here, start adding nice borders, I'm
  • 05:22 just going to select a yellow background fill, and I'm going to make the font Bold.
  • 05:27 Let's click on, OK and OK again.
  • 05:30 So now if we change this to, let's go for Hawaiian Pizza,
  • 05:34 notice it highlights that first row.
  • 05:36 And if we scroll down, we should have some other Hawaiian Pizzas in here.
  • 05:40 Yes, we do, so it's highlighting every occurrence of that pizza,
  • 05:45 making them stand out from our list.
  • 05:48 And as I mentioned, this is all completely dynamic.
  • 05:51 So if I jump down to the bottom, Ctrl+Down Arrow,
  • 05:55 and let's add another pizza, I'm just going to add The Vegan Pizza.
  • 06:01 Now I'm not going to add any other information,
  • 06:03 this is just to show you what happens when you add something new.
  • 06:06 If I go and check out my unique list of values,
  • 06:10 notice that it's added the vegan pizza onto the bottom.
  • 06:14 And so because I've built this data validation based off of that list,
  • 06:18 I should find that at the bottom here, we also have The Vegan Pizza.
  • 06:22 And if I click on this and Ctrl+Down Arrow to jump to the bottom,
  • 06:27 when I select it, it highlights in the list.
  • 06:31 So everything here is completely dynamic.

Lesson notes are only available for subscribers.

Gift this course