How To Compare Multiple Lists of Names with a Pivot Table

If you have ever had to create a report that compares two or more lists in Excel, then this tutorial will really help you. You will learn a simple technique to create this report using a Pivot Table. It is very easy to implement and there are NO formulas required.

In this post I will explain how to compare two or more lists of names using a Pivot Table.

How To Compare Lists of Names with Pivot Table

In this example we have lists of names for people that signed up to volunteer for a beach cleanup event.  The event has been running for three years and we have one list for each of the last three years.

We want to compare these lists and answer some of the following questions about our volunteers.

  • Who has signed up all three years?
  • Who signed up last year, but did not sign up this year?
  • Who is new to the event this year?

Video

The Pivot Table List Comparison Technique

We can answer all of these questions with one very simple Pivot Table.  This technique is very easy to implement and does not require any formulas.

It should also help you understand how Pivot Tables work to consolidate and summarize data.

In three simple steps we are going to create the pivot table and answer our questions.

You can download the sample file I'm using to follow along.

Don't forget to sign-up for my free email newsletter to get more tips and downloads like this.

Step #1 – Combine the Lists

The first step is to prepare our lists.  In the file we have three worksheets that contain a list of names for volunteers that signed up each year.

We need to combine these three lists into one list that contains all the data for all three years.

Combine Lists for Pivot Table Name Comparison

The following are the steps for combining the lists.

  1. Make a copy of the ‘2012' sheet and rename it to ‘Combined Data'.
  2. In cell E1 add the text “Year”.  This new column will represent the year for each list of data.
  3. Enter “2012” in cell B2 and copy it down to the end of the list.
  4. Copy the 2013 data to the bottom of the list on the ‘Combined Data' sheet.
  5. Enter “2013” in column B in the first row of the 2013 data (cell B12), and copy it down to the end of the list.
  6. Repeat steps 4 & 5 for the 2014 data.

You should now have a long list of data that contains all the data rows for all three years, with a new column (B) that specifies which year the data row belongs to.

Please see my article on structuring source data for pivot tables for detailed explanation on why the data is organized like this.

Step #2 – Create the Pivot Table

We can now create a Pivot Table based on our ‘Combined Data' list to start making comparisons.

Create Pivot Table Name Comparison

Here are the steps to creating the Pivot Table.

  1. Select a cell in the Combined List and press the Pivot Table button on the Insert tab of the Ribbon.  Press OK on the prompt window to create a Pivot Table on a new worksheet.
  2. Add the Name field to the Rows area of the Pivot Table.
  3. Add the Year field to the Columns area of the Pivot Table.
  4. Add the Name field to the Values area of the Pivot Table.  This will create a Count of Names measure.

You should now have a list of names of all the volunteers that signed up over the last three years.  The “1's” in columns B:D tell us that the person was a volunteer in that year.

The Pivot Table Explained

When we add the Name field to the Rows area of the pivot table (step 2 above), Excel automatically consolidates the list for us and creates a row for each unique name from the combined list.  This means that names that appear in multiple years will only be listed once.

For example, the name Asher Mays appears three times in the combined list (source data of the pivot table).  But the pivot table only displays the name once in row 6 (image below).  This is a consolidated list of unique values.

The Pivot Table Explained - Compare Lists

The same thing happens when we add the Years field to the Columns area (step 3 above).  The years 2012, 2013, and 2014 are listed many times in column B of the combined data list.  But each of the years only appears once in columns B:D of the pivot table.

When we add the Names field to the Values area (step 4 above), Excel calculates how many times the name and year combination appear for each intersection in the pivot table.  For example, cell C6 of the pivot table is the intersection of the name Asher Mays and the year 2013.  This combination appears once in the data source, so a “1” is displayed in cell C6.

If a cell in the pivot table is blank then that name and year combination does not exist in the source data.  That means the person did not volunteer that year.

Step #3 – Answer Our Questions (Analyze the Results)

We can now use this one Pivot Table to answer all of our questions.

Question #1 – Who has signed up all three years?

Column E of the Pivot Table contains the Grand Total (sum of columns B:D).   People that volunteered all three years will have a “3” in column E.

We should sort the pivot table so all the people with a “3” in column E appear at the top of the list.  This will make it easier to find the names.

Sort Pivot Table Name Comparison

These people will have a “1” in each column from B:D and the sum of these 1's = 3.

Names That Appeared in All 3 Lists Pivot Table Name Comparison

If your list is longer, you can copy the Pivot Table data to another sheet and use Excel's Filter features to sort and filter the data further.  You can also use an Excel Table to automatically summarize your filtered results.  Checkout my video on Excel Tables to learn about tables.

Question #2 – Who signed up last year, but did not sign up this year?

To answer this question we need to know who had a “1” in the 2013 column and a “blank” in the 2014 column.

The filter features of the Pivot Table can be somewhat limited for this exercise, so I will copy and paste the pivot table data to another sheet and apply Filters to do the analysis.

Volunteer This Year NOT Last Year Pivot Table Name Comparison

These are people we might want to contact to see if they can make it this year.

Question #3 – Who is new to the event this year?

To answer this question we just need to know who has a “1” in the 2014 column, and blanks for 2012 and 2013.

Who Is New This Year Table Name Comparison

This tells us that the person has never volunteered before, and we want to be extra nice to them so they come back next year. 🙂

Checkout my article on 7 Keyboard Shortcuts for the Drop-down Filters for some quick tips on using the Filters.

What's Next?

This technique to compare multiple lists of data using a Pivot Table is “must-know” for any analyst.  It is very easy once you get the hang of it, and it will save you a lot of time.

The alternative is to use a lot of VLOOKUP, SUMIF, or COUNTIF formulas, and you will spend a lot of time manually creating a report.

The Pivot Table technique can also let you quickly pivot to answer other questions about your data.  For example, if we wanted to know what city our volunteers are coming from, we could easily add the City field to the Rows area of the Pivot Table.

This allows us to further analyze our data  and see that we might be continually attracting volunteers from some cities and not others.

City-Name Pivot Table Name Comparison

I've really just scratched the surface of how you can use this technique.

In an upcoming post I will show you how you can use this same technique to reconcile transaction data.  Do you have a list of accounts or customer (CRM) data, where you want to see what changed from last month to this month?

I will show you how to use this technique to create some very powerful reports that will give you a lot of insight into your data.

Please subscribe below to get an email when this new post is available.  And leave a comment below with any questions. 🙂

31 comments

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

  • Hi Jon. Great treatment of the subject. Couple of comments:
    If your list is longer, you can copy the Pivot Table data to another sheet and use Excel’s Filter features to sort and filter the data further. Or you could apply the autofilter to the PivotTable as per Mike’s trick at http://datapigtechnologies.com/blog/index.php/autofilter-a-pivottable/

    To answer this question we need to know who had a “1″ in the 2013 column and a “blank” in the 2014 column.
    Again, you can use Mike’s Autofilter trick.

    You’ve just got to remember to clear the filter when you’re done, otherwise the pivot behind the scenes moves around and falls into the ‘cracks’ of the hidden rows. Hard to explain.

  • A real life case solved by a Pivot Table! Their power is almost unlimited.

    You could also use Conditional Formatting to highlight certain values as well to make them stand out, you can even highlight blank cells.

    Great post!

    • Another great idea! Thanks John! The conditional formatting could be very useful if you wanted to visually identify and display trends with these types of comparisons.

  • Jon:

    Thanks for providing these Excel Tutorials with such excellent presentation. I have a suggestion for your How to Compare Lists of Names with a Pivot Table to reduce the effort of cutting and pasting using RDBmerge, see link: http://www.rondebruin.nl/mac/addins/mergemac.htm for Macs, or http://www.rondebruin.nl/win/addins/rdbmerge.htm for windows. RDBMerge is a user friendly way to Merge Data from Multiple Excel Workbooks, csv and xml files into a Summary Workbook. It has been used at my work to combine several worksheets into one summary sheet, which in turn is fed into a pivot table.

    Your tips on how to “fill down” formulas and values in the video was very helpful. I usually usually convert my data into Tables, in that way I get a formatted layout and fill down occurs automatically when I add a formula to any empty column, not so when it is a value. Lamentably, Tables don’t work as data source for pivot tables.

    Have a great day, and keep up your good work!

    • Hi Pedro,

      Thank you for your comment and suggestion! The RDBMerge tool would be very useful if this is a process you are doing frequently.

      I find myself using this technique all the time, as it is a great alternative to using lookup formulas.

      I also use Tables and the autofill feature is definitely a time saver. Here is a video tutorial I did on Tables for anyone that wants to learn more about this great tool in Excel.

      Thanks again Pedro!

  • This video was amazing and works very well with me. I have three big lists to compare (57973 items) and i followed what exactly mentioned here, however, if I want to get the total numbers, for example how many volunteers participated only in the first year, or how many volunteers participated in the three years.

    • Thank you Hesham! The total numbers will be displayed in the Total Row at the bottom of the pivot table. If you want to sum all three columns then you can add a Total Column.

      To turn the Total Row or Columns on/off:
      1. Select any cell in the pivot table.
      2. Go to the Design tab on the ribbon.
      3. Click the Grand Totals button on the left side of the ribbon.
      4. Select the option that says “On for Rows and Columns”.

      That will turn the totals on and you will see the Grand Total of all volunteers for the three years in the bottom right corner of the pivot table.

      Please let me know if you have any questions. Thanks again!

  • Great Article! But where is the link to see the next video? The video about “how to use this technique to create some very powerful reports that will give you a lot of insight into your data”?

  • Our membership database stores names in 2 fields first name and surname – so when I run a pivot table I have to put both into rows – but then very annoyingly Excel recognises the same surname and groups 2 different people (albeit with the same surname) together – how do i avoid this?

    • Hi Trish,
      Great question! There are a few ways to go about it.

      1. You can change the pivot table layout to Tabular format and Repeat the Labels. This is done from the Design tab in the ribbon with a cell in the pivot table selected. Here is a screenshot.

      Pivot Table Compact Form Layout Repeat Item Labels

      2. Another option is to concatenate/join the First Name and Last Name in a new column called Full Name. Then add this name to the pivot table. This can be done with a simple formula.

      =A2 &” “& B2

      Assuming A2 contains the first name and B2 contains the last name.

      If this is something you do often, my PivotPal add-in has a feature called My Pivot Layouts. This feature allows you to create custom profiles for your pivot table layouts and apply them with a click of a button. So, if you find yourself applying the compact format and repeating labels (solution #1) often, then this feature will save you some time.

      I hope that helps.

  • I need to know how to do this in reverse. I already have a pivot table sheet, but needing to break the data down into individual sheets, to look just as how your individual lists looked to start out with.

    • Hi Garyn,
      You could use the Show Report Filter Pages feature for this. I don’t have a tutorial, but you would drag the Year field into the Filters area, then run the Show Report Filter Pages feature. It’s on the Option/Analyze tab in the Options drop-down menu. I will write a post on this in the future.

  • Joe,
    Thanks for the pivot post. How would I structure data in a spread sheet where I can represent w/in a pivot table for the following fields: type of work, date (week and year), name. I would also be interested in weighting the work (difficulty, stress) in addition to just quantifying weeks worked. Thanks in advance for any help you can offer. v/r, chris

    • Wonderful site. Lots of useful info here. I am sending it to a few friends ans also sharing in delicious. And naturally, thanks for your effort!

  • This is very helpful but I’d like to take it a step further and create a bar graph showing the people and how many times they’ve volunteered, then on the chart next to their name, I’d like to be able to put a calculated value in parenthesis.

  • Have I missed something here? This advise is just to combine (via copy/paste) multiple lists into one list. That is only helpful until the next data update comes along… then where do you post it? To the combined list, the individual lists or both.

    Folks are really looking for a method to work with the multiple lists in place; they don’t want to double their future update effort. Excel does provide methods to do this, but as far as I can understand, they are way more complex than should be for identical/similar lists/tables in one/more workbooks.

    Believe me, I am on a years long quest to find excellent documentation on these techniques that actually works… and apologies if I have not understood your intent.

  • This is a good write up for finding multiple entries in a single list, but does not solve the actual problem of comparing multiple lists stated in the title

  • Hi Jon,
    Where can I find the next post you mentioned about how to use this technique to create some very powerful reports that will give you a lot of insight into your data?

  • This is probably the most useful tip I have seen (and learned) in the past few years. It has a direct application in evaluating donors to fundraising campaigns over multiple years . . . just use dollar amounts as an additional column with the name and year and you have a very clear picture of your donors. Thanks!

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter