MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Use a Pivot Table to Compare Lists

May 23, 2019 - by Bill Jelen

Excel Use a Pivot Table to Compare Lists. Photo Credit: Element5 Digital at

When you think of comparing lists, you probably think of VLOOKUP. If you have two lists to compare, you need to add two columns of VLOOKUP. In the figure below, you are trying to compare Tuesday to Monday and Wednesday to Tuesday and maybe even Wednesday to Monday. It is going to take a lot of VLOOKUP columns to figure out who was added to and dropped from each list.

Three lists are shown in Excel. Each list has a name and a number. There are lists from Monday, Tuesday, and Wednesday.

You can use pivot tables to make this job far easier. Combine all of your lists into a single list with a new column called Source. In the Source column, identify which list the data came from.

Combine the three lists into one super list. There is a third column, called Source. The Source column indicates if this record came from Monday, Tuesday, or Wednesday list.

Build a pivot table from the combined list, with Name in rows, RSVP in values, and Source in columns. Turn off the Grand Total row, and you have a neat list showing a superset from day to day, as shown below.

A pivot table with Name along the left column and source across the top. It is easy to see for each name if they were blank on Monday, appeared on Tuesday, or the number changed on Wednesday.

Title Photo: Element5 Digital at

Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.