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.
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.
If you open the Rep dropdown, you can filter the data to any one sales rep.
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.
The Excel team has hidden a feature called Show Report Filter Pages. Select any pivot table that has a field in the report filter. Go to the Analyze tab (or the Options tab in Excel 2007/2010). On the far left side is the large Options button. Next to the large Options button is a tiny dropdown arrow. Click this dropdown and choose Show Report Filter Pages....
Excel asks which field you want to use. Select the one you want (in this case the only one available) and click OK.
Over the next few seconds, Excel starts inserting new worksheets, one for each sales rep. Each sheet tab is named after the sales rep. Inside each worksheet, Excel replicates the pivot table but changes the name in the report filter to this sales rep.
You end up with a report for each sales rep.
This would work with any field. If you want a report for each customer, product, vendor, or something else, add it to the report filter and use Show Report Filter Pages.
Title Photo: Thijs Schouten at Unsplash.com