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.

Financial Modelling in Power BI

September 2022

The only book to produce all three financial statements in Power BI.

Your Excel Survival Kit - Second Edition

March 2021

This book helps you realize that Excel is on your side. It can be your friend, not your enemy. If and when you upgrade your Excel skills you will experience many positives: you will learn to love Excel, you will learn how to use Excel to become an invaluable asset in your workplace.

Microsoft 365 Excel: The Only App That Matters

June 2022

Excel Worksheet, Power Query, Power Pivot, Power BI. Calculations, Analytics, Modeling, Data Analysis and Dashboard Reporting for the New Era of Dynamic Data Driven Decision Making & Insight!

Microsoft Excel Inside Out (Office 2021 and Microsoft 365)

December 2021

Dive Into Microsoft Excel for Office 2021 and Microsoft 365 and really put your spreadsheet expertise to work. This supremely well-organized reference packs hundreds of timesaving solutions, tips, and workaroundsall you need to make the most of Excels most powerful tools for analyzing data and making better decisions.

see more products »

Excel 2024: Use a Pivot Table to Compare Lists

More Reading

Featured Products