Excel 2024: Use a Pivot Table to Compare Lists

May 22, 2024 - by Bill Jelen

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

More Reading

Excel 2024: Replicate a Pivot Table for Each Rep »

May 20, 2024 - by Bill Jelen

Here is a great trick I learned from Southern California based Excel consultant Szilvia Juhasz.

Excel 2024: Make Pivot Tables Expandable Using Ctrl+T »

May 15, 2024 - by Bill Jelen

If you choose all of columns A:J and you later want to add more records below the data, it takes only a simple Refresh to add the new data instead of having to find the Change Data Source icon. In the past, this made sense. But today, Change Data Source is right next to the Refresh button and not hard to find.

Excel 2024: Specify Defaults for All Future Pivot Tables »

May 13, 2024 - by Bill Jelen

It took me six years, but I finally convinced the Excel team that a lot of people prefer Tabular layout for pivot tables to the Compact layout that became the default layout in Excel 2007. If you have Office 365, you now have the ability to specify pivot table defaults.

Master Your Data with Power Query in Excel and Power BI

August 2021

Despite the moniker "data monkey," we information workers are often more like data magicians. Our data seldom enters our world in a ready-to-consume format; it can take hours of cleaning, filtering, and reshaping to get things ready to go. Power Query will make this process faster the first time and reduce it to a single button click every subsequent time.

Guerrilla Data Analysis Using Microsoft Excel - 3rd Edition

May 2022

Two of the leading Excel channels on YouTube join forces to combat bad data. This book includes step-by-step examples and case studies that teach users the many power tricks for analyzing data in Excel. These are tips honed by Bill Jelen, "MrExcel," and Oz do Soleil during their careers run as financial analysts.

MrExcel 2024 Igniting Excel

February 2024

This is the 7th edition of MrExcel XL. Updates for 2024 include: Remove Filter Items Using Search Box, The Consolas Font Makes it Easy to Tell Zero from the Letter O, Display Online Pictures In a Cell Using a Formula, Place Pictures In Cell From Local Computer, Using Artificial Intelligence with Copilot for Excel, and more...

MrExcel 2022 Boosting Excel

April 2022

The 6th Edition of MrExcel XL, updated with new functions released for Microsoft 365.

see more products »

Excel 2024: Use a Pivot Table to Compare Lists

More Reading

Featured Products