MrExcel Publishing
Your One Stop for Excel Tips & Solutions
Microsoft MVP

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

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.

continue reading »

More Reading

Excel Replicate a Pivot Table for Each Rep. Photo Credit: Thijs Schouten at Unsplash.com

Excel 2019: Replicate a Pivot Table for Each Rep »

May 22, 2019 - by Bill Jelen

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

Excel Use Ctrl+T with VLOOKUP and Charts. Photo Credit: Steve Johnson at Unsplash.com

Excel 2019: Use Ctrl+T with VLOOKUP and Charts »

May 20, 2019 - by Bill Jelen

It is fairly cool that you can use Ctrl+T after setting up the pivot table, VLOOKUP, or chart, and Excel still makes the range expand.

read more articles »

IMA Data Analytics Leveraging Excel

Featured Products

Excel Dynamic Arrays Straight to the Point

Excel Dynamic Arrays Straight to the Point »

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.

Cleaning Excel Data With Power Query Straight To The Point

Cleaning Excel Data With Power Query Straight To The Point »

This book provides a quick Straight-to-the-Point introduction to Power Query. There’s a lot here, and there’s also a lot that’s not here. Reading this book will be like sitting down at a café and striking up a conversation with the person at the next table, where you learn enough random stuff about that person to decide if you want to know more and keep in touch.

Power Excel With MrExcel - 2017 Edition

Power Excel With MrExcel - 2017 Edition »

This is the print book edition of "Power Excel with MrExcel - 2017 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.

100 Excel Simulations

100 Excel Simulations »

Covering a variety of Excel simulations, from gambling to genetics, this introduction is for people interested in modeling future events, without the cost of an expensive textbook.

Supercharge Power BI

Supercharge Power BI »

If you want to be able to supercharge Power BI, you need to learn to write DAX. Data Analysis Expressions (DAX) is the formula language of Power BI and Power Pivot for Excel. This book prompts the reader to put your new skills to the test. Written to give you hands-on practice using Power BI Desktop and writing DAX.

MrExcel LX – The Holy Grail of Excel Tips

MrExcel LX – The Holy Grail of Excel Tips »

A book for people who use Excel 40+ hours per week. Illustrated in full color.

see more products »