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.

The pivot table below shows products across the top and customers down the side. The pivot table is sorted so the largest customers are at the top. The Sales Rep field is in the report filter.

This could be any pivot table, but the Rep field has been added to the Filter area.

If you open the Rep dropdown, you can filter the data to any one sales rep.

The typical way to use the Filter field is to open the dropdown in B1 and choose a sales rep from the list.

This is a great way to create a report for each sales rep. Each report summarizes the revenue from a particular salesperson's customers, with the biggest customers at the top. And you get to see the split between the various products.

More Reading

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.

Excel 2024: Find the True Top Five in a Pivot Table »

May 10, 2024 - by Bill Jelen

Pivot tables offer a Top 10 filter. It is cool. It is flexible. But I hate it, and I will tell you why.

Programming PowerPoint with VBA

February 2022

This book assumes you already use PowerPoint and want to automate or enhance your presentations using Visual Basic for Applications (VBA). This book includes VBA samples for working with layouts, themes & masters, creating tables, drawing objects, charting, animation effects and event programming.

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.

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!

Power BI for the Excel Data Analyst

July 2022

A step-by-step guide on how to use Power BI. Written with Excel fans in mind, but with every Power BI student at its heart.

see more products »

Excel 2024: Replicate a Pivot Table for Each Rep

More Reading

Featured Products