Locked lesson.

Upgrade

Quick reference

Compare Lists with Formatting

Learn how to compare two lists and highlight differences using formatting. 

When to use

We use formatting to compare lists whenever we want to highlight differences between two or more sets of data. 

Instructions

Comparing lists in Excel is an invaluable skill in Excel. We compare lists to find out the differences or similarities between two sets of data. 

There are several ways we can compare lists and one of the most popular methods is to use formatting to highlight differences, duplicates, or unique values. 

Compare Lists  - Row Differences

In the first example, we are going to compare two lists of employee names to find out who attended session 1 of a training course but didn't attend session 2. We can do this by highlighting row differences. 

  • Select the first list of names.
  • Hold down CTRL and select the second list of names. 
  • Press CTRL+G to open the GoTo dialog box. 
  • Click Special. 
  • In the Select area, choose Row differences. 
  • Click OK. 

The differences between the two lists will be selected. 

We can now apply a background fill to the selected cells to permanently highlight them. 

  • From the Home tab, in the Font group, click the drop-down next to Fill Color. 
  • Choose a color from the palette. 

Compare Lists - Highlight Duplicates/Unique Entries

We can perform a similar comparison using Conditional Formatting. 

  • Select the first list. 
  • Hold down CTRL and select the second list. 
  • From the Home tab, in the Styles group, click Conditional Formatting. 
  • Hover the mouse over Highlight Cells Rules. 
  • Choose Duplicate Values from the menu. 

By default, Excel will highlight the duplicate values in both lists using the fill color selected. 

To see the differences as opposed to the duplicates we need to change our selection. 

  • From the Duplicate Values dialog box, click the drop-down next to Duplicate. 
  • Select Unique. 
  • Click OK. 

Hints & tips

  • We can choose our own custom format when highlighting cells if we need to stick to company colors/branding. 
Login to download
  • 00:04 In this section of the course, we're going to start talking about Excel lists.
  • 00:08 And we're going to start out in the first couple of lessons just running through
  • 00:12 some options that we have when it comes to comparing different lists, because this is
  • 00:17 a task that people find themselves having to do on a fairly frequent basis.
  • 00:21 So let's take a look at all the different options that we have when it comes to
  • 00:25 comparing one list of data to another list of data.
  • 00:28 And we're going to start out in this lesson by taking a look at how we can
  • 00:32 compare two lists using formatting.
  • 00:34 So we're not going to use any formulas here,
  • 00:36 we are simply going to highlight differences between two different lists.
  • 00:41 And the two approaches that I've chosen to include in this lesson are the ones that I
  • 00:45 find easiest.
  • 00:46 So let's dive into our example.
  • 00:48 Now if we take a look at example number one,
  • 00:51 all I have here are two lists of let's say, employees.
  • 00:55 And maybe these are people who attended two different sessions of a training
  • 00:59 course.
  • 01:00 And I want to find out who attended session one but didn't attend session two.
  • 01:05 Now the first method I can use do this is Go To Special.
  • 01:10 And you may have used Go To Special in different context, but
  • 01:13 it's really good here for highlighting raw differences.
  • 01:16 So all we need to do is we need to select the first list of names,
  • 01:21 hold down Ctrl and select the second list of names,
  • 01:24 because these are the two ranges that we're comparing together.
  • 01:29 Now the shortcut key to jump to go to Special is Ctrl G.
  • 01:34 Alternatively, if you go up to the Home ribbon, all the way over and Find and
  • 01:38 Select we have Go To Special just here.
  • 01:41 Now I'm going to use the keyboard shortcut Ctrl G, and
  • 01:43 then I'm going to click on Special.
  • 01:45 And what you'll notice is at the top here in the Select area,
  • 01:50 we have an option to select row differences.
  • 01:53 So I'm going to select that and click on OK and
  • 01:56 straight away it highlights three names in that first list.
  • 02:00 Now it hasn't permanently highlighted them, it's basically just selected them.
  • 02:04 So what I'm going to do is I'm going to go up to the Home ribbon and
  • 02:07 I'm just going to add a background fill color to make those really stand out.
  • 02:11 So I can see here that Olivia, Sam And Cullum don't appear in the second list.
  • 02:18 Those are the row differences.
  • 02:20 And if I just do a quick visual check, because this list is reasonably short,
  • 02:24 I can see that, nope, I can't see Olivia, Sam, or Cullum in this list.
  • 02:28 So immediately I know that these three people attended session one, but
  • 02:33 they didn't attend session two.
  • 02:35 Now we can do a similar thing here, but using conditional formatting instead.
  • 02:39 So let's take a look at example two.
  • 02:42 Now, once again, I have a similar couple of lists.
  • 02:45 People who attended the first session and people who attended the second session and
  • 02:49 I want to find out the differences.
  • 02:51 So I'm going to select the first list, hold down Ctrl,
  • 02:54 select the second list, but this time we're going to jump up to the Home ribbon,
  • 03:00 go into Conditional Formatting, Highlight Cell Rules and
  • 03:04 we have a Duplicate Values option just here.
  • 03:07 Now if I click this, what we're going to find is that most of these names get
  • 03:11 colored in light red with a dark red text fill.
  • 03:14 And that's because that's what I have set just here.
  • 03:16 Now remember you can click the drop down, you can choose a different color, so
  • 03:21 maybe I want a green fill instead or you can choose your own custom format.
  • 03:24 Now I'm going to keep mine on, let's go for a yellow fill and
  • 03:28 currently it's highlighting all of the duplicate values in both lists.
  • 03:33 So currently everything that's highlighted are people that attended both
  • 03:38 of the sessions because their names occur in both lists.
  • 03:41 If I wanted to see the opposite of that, and
  • 03:44 I wanted to see the people who didn't attend the second session,
  • 03:48 I can simply click the drop down and format unique values instead.
  • 03:52 So when I click on OK I can see here that Olivia didn't attend session two,
  • 03:58 and Fleur didn't attend session one.
  • 04:01 So those are two very simple examples of how you can use
  • 04:05 formatting to compare two lists of data in Excel.

Lesson notes are only available for subscribers.

Gift this course