# 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.

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?

## 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.

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.

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.

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 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.

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

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.

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.

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.

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.

• Jeff Weir says:

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.

• Thanks for sharing Jeff! That’s a great tip for quickly filtering on a pivot table.

• John says:

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.

• Pedro J. Davila says:

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!

• Eileen Morrison says:

Thanks so much for this short video. I look forward to your webinar.

• Thanks Eileen! I am glad you enjoyed it.

Are you referring to the PivotTable Webinar by John Michaloudis? If so that’s a different John, but the webinar is great and you will learn a lot of awesome pivot table techniques. You can click the link above to register for the webinar if you haven’t done so already. Thanks again!

• Hesham says:

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!

• karolina says:

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”?

• Trish Noakes says:

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.

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.

Thank you for the quick refresher.

• Garyn says:

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.

• chris says:

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

• Isaiah Odhiambo says:

Thanks Jon,
Just learnt something new. . . Excel is really exciting. . .Way exciting. . .

• Josef Whitmyre says:

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!

• mkt says:

Thank you it is very good explanation.

• XAVIER says:

incredible.. thanks soo much.. TEACH..!

• Thidar says:

Thank you very much. It was very helpful for me.

With many thanks,
Thidar

• Troy Havard says:

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.

• Reem Baiazid says:

• Lainie says:

Awesome!

• Tracy Barker says:

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.

• Jamie says:

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

• finance101 says:

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?

• Jess Fowler says:

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!

• Matt says:

Thanks Jon! This is my favorite way for comparing historical data.

Generic filters
Exact matches only

#### Excel Shortcuts List

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

Excel Shortcuts List