Knowing how to create Pivot Table was a one of the best things I have ever learnt in Excel. I can analyze large amount of data, from a few hundred rows to a few million rows. And when you create a pivot table that is good, your boss will ask you to prepare the multiple copies, one for each category. That's when it becomes a pain.
I started the process by repeatedly go to the source, insert a Pivot Table, set up the format, and choose one of the categories. Then I discovered that I could simpley copy the worksheet containing the Pivot Table, change the report filter and change the worksheet name. It became faster. But that's wsn't the end of the story. I discover there is a function in Pivot Table tha very few people know about. It's called "Show Report Filter Pages". If you want to learn, contine reading or watch the video below.
Step 1: Select a cell within the Pivot Table
Step 2: Go to the Analyze tab which appears only when you select the Pivot Table.
Step 3: Click on the small dropdown button next to Options.
Step 4: Select "Show Report Filter Pages".
Step 5: Select the category containing the values to create multiple tables.
You should be able to see all the worksheet tabs, each labelled with one category.
Confused? Watch the video below.
Did the tip help you? Like, Comment below or Share with your friends.
Remember to sign up for our mailing list (right at the bottom of the page) if you wish to know more tips in the future.
If it helps you and would like to treat me to a cup of coffee, click here.
Share this page:
Would you prefer to share this page with others by linking to it?
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft