Excel - Replicate a Pivot Table for Every Region - Episode 1257

Microsoft Excel Tutorial: How to make a copy of a pivot table report for every region in a database. Episode #1257 shows you how, as well as a few other pivot table tricks such as rolling daily dates up to years and showing the top 5 customers.

Welcome to the MrExcel podcast. In this episode, we will be discussing how to auto-copy a pivot table. This is a question sent in by Diane from Alabama, and if you have a question for the podcast, feel free to drop me a note at bill@MrExcel.com. We are always looking for new questions to feature on future episodes.

Diane had seen one of my seminars where I was able to replicate a pivot table for every region, and she wanted to know how to do it herself. So, in this video, I will be showing you how to do just that. First, we will build the pivot table by inserting it and selecting the necessary fields. Then, we will group the dates by year and filter the top 10 or 5 customers. But, the real magic happens when we want to produce this report for each regional manager.

To do this, we will take the region field and drag it to the report filter. But, instead of just creating a report for one regional manager, we will go to the Pivot Table Options tab and select the drop-down next to "Options". From there, we will choose "Show Report Filter Pages" and watch as a brand new sheet is created for each region. This is a great way to replicate a report for every region, product, customer, or any other category you may need.

Of course, you may want to make some formatting changes before creating all these new sheets. No problem! Simply go back to the pivot table, delete the extra sheets, and make your desired changes. Then, go back to the Pivot Table Options tab and select "Show Report Filter Pages" again. This time, your changes will be reflected in all the new sheets. And just like that, you have a perfectly formatted report for each region. Thank you to Diane for sending in this question and thank you for tuning in to another netcast from MrExcel. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/
Table of Contents:
(00:00) Auto-Copying a Pivot Table
(00:24) Building the Pivot Table
(00:36) Grouping Dates
(00:51) Choosing Top Customers
(01:09) Creating a Report for Each Regional Manager
(01:35) Using the Pivot Table Options Tab
(02:00) Creating a New Sheet for Each Region
(02:20) Adjusting Formatting and Layout
(03:01) Finalizing the Report
(03:23) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
Auto-Copy a Pivot Table
Formatting pivot table
Grouping dates in pivot table
Learning self from MrExcel Podcast episode 1257
Pivot table replication
Replicating report for each region
Report filter in pivot table
Show Report Filter Pages option
Thanks for listening to MrExcel netcast
Top 10 customers in pivot table
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152938/

Видео Excel - Replicate a Pivot Table for Every Region - Episode 1257 канала MrExcel.com