Create Multiple Pivot Table Reports with Show Report Filter Pages
Вставка
- Опубліковано 15 вер 2024
- Sign up for our Excel webinar, times added weekly: www.excelcampu...
Learn how to quickly create multiple pivot table reports with the Show Report Filter Pages feature.
Download the file to follow along: www.excelcampu...
Get the Tab Hound Add-in: www.excelcampu...
Pivot tables are an amazing tool for quickly summarizing data in Excel. They save us a TON of time with our everyday work. There is one "hidden" feature of pivot tables that can save us even more time.
Sometimes we need to replicate a pivot table for each unique item in a field. This could be a report for:
Each department in organization.
Each salesperson on the sales team.
Each account in the general ledger.
Each customer in the CRM system.
Each stock in the database.
Or, just about any other field (column) in your data set.
We could create one pivot table, filter it for a specific item, then copy the sheet and re-apply a filter for the next item. But this would take a lot of time if we have dozens or hundreds of unique items in the data set.
Fortunately, we don't have to do all this manual work. Pivot tables have a feature called Show Report Filter Pages that automates this entire process.
The Show Report Filter Pages Feature
The Show Report Filter Pages feature:
Creates a copy of an existing pivot table for each unique item in a field.
The new pivot tables are created on individual worksheets.
Each sheet is renamed to match the item name.
A filter is applied to the field in the Filters Area of each pivot table for the item.
All this is done with a click of a button. Your field can have 5 or 500 unique items. Show Report Filter Pages will create a sheet for each item and replicate the pivot table report.
I had no idea this functionality existed. This is a HUGE TIME-SAVER for providing multiple pivot tables for reporting.
VERY HELPFUL WITH SO LITTLE TIME IN THE DAY -YOU GO POINT BY POINT TO THE EXACT INFORMATION NEEDED TO CREATE MULTIPLE PIVOT TABLES ESPECIALLY FOR ME AS A ONE TIME USER...BEGINNER AGAIN~
I didn’t know this feature was available. Thanks! It will save me tons of time
Thank you! I often use this video every time I forget how to do this quick trick! I'm sure you know by now that you don't need "tab hound" to go to a specific worksheet. Right-click on the right arrow (that points to the worksheets) and a list of all the sheets will display.
Nice and concise content to solve this common problem scenario…thanks for sharing your knowledge!
What a great feature! Learning a lot. Thank you!
Very helpful. I was looking for this feature since long.
Awesome! Thanks for this invaluable show of humanity.
Thanks Joshua! :-)
This was great, easy to follow and very practical
Thank you finally i found which i was looking for.
Glad I could help. 😀
God this is life saving!! Thank you!.
I love this Ecel trick, always good to show colleagues this one.!
I only wish I had watched it sooner in my life :D Thanks!
Thanks for showing this function in Excel.
Wow I had no idea what an awesome feature
You make my day sir!!! Salute👏👏
Thanks sir very helpfull.
That's awesome, thanks for shairing
This was very helpful. Thank you so much for sharing!
Awesome Sir
You are amazing John!! Thank you for sharing, definately going to use it.
Thank you Mariska! :-)
Excel Campus - plz share your contact number
Love you brother!!! Superb!!!
Thank you!
You're welcome, Mat! :)
King. Thank you.
Excellent. Much helpful
Hi, Thanks that is useful. Can you also guide with the process to create a separate file for each sales person so that he will get his individual data
Really awesome feature --> thanks for showing Jon! I think you saved a lot of people a huge amount of time :)
This is sooo good thanks for sharing!
good information
Nice tutorial. Thanks
This is a very useful tip for me.
Thank you so much for this! I am guilty of having done the copy paste of pivot table to several tabs!
You're very welcome! 😀
Wonderful, thanks Man! Also, great add-in
It was new learning.
@Jon, you can just right-click the arrows that toggles through the sheets for a list and just select the sheet you want. No plug in needed.
You are awesome! thank you very much :)
What about having a back button on the new worksheets when using the TOC. Just makes it easier for the audience to navigate. Excellent video and thanks for sharing!
Thank you, it can be so easy
Thanku so much Jon for this extremely valuable function......
Hii
Can i do this in normal excel file to create many worksheets without pivot table
Good. I like it
Great. Thank you so much!
This is absolutely Fabulous! Great time saver!
thank you
This is totally amazing feature.
Thank you so much it is a fantastic and very helpful tip.
Really amazing...Many thanks Jon!
you saved my time by taking my time ;)
thanks- great time saver. Now that i have all worksheets created for each salesman is there a way to create individual work brooks for each salesman, that only contains their data, with the ability to drill into their data?
A good feature to add to your Addin would be to have a checkbox list where one can select to delete the worksheets instead of deleting one by one.
one of the best tools for pivot table reporting are show data by filter.
Awesome video. Thank you very much.
Extremely valuable function. Thanks!
Great function....Thank you so much :-)
This is super helpful excel trick, thank you so much for sharing this function.
OMG thank you ❤️❤️
Thanks for your help ! spot on.
thanks Jon, Awesome Video!!!
Thank you Muhammad! :-)
Supper amazing, thank you so much.
Thanks sir very helpful video
you saved my life :) thank you very much
Thanks for Sharing
Immensely cool tip, Jon! Thanks
Excellent tips
Glad you like them! 😀
great feature, but I have a problem, I am using excel 2016 and when I run the Pivot filter the newly created sheets doesn't take the name of the filtered column I am filtering., instead will give a progressive sheet number. thank you
Great Jon. Thanks for all...
Awesome Video, Thanks a lot 👍👍👍👍👍👍
Very helpful, keep posting
I love this functionality since I have to - I only have an issue; I would need to keep the Conditional Formatting created in the original Pivot, is it possible?
Thank you for sharing
Wow!!!. Great video. Keep it going 👌👌👌
How to make individual reports with data model? Thanks for the informative video.
Thank you for sharing this. I have to build a monthly report like this every month. This will save me lot of time. The only step I’m missing that will save me lot of time is that from each of the “tabs” I need to create a graph and make a dashboard. Do you if this can be done without multiple copy paste?
Thank you for your knowledge, John. It's help me to manage the database in huge amount. Keep productive.....
As always a great tip
Very nice
Great content!!
Great job!!
very helpful !!!
Glad it was helpful, @Diana.Rodriguez-pr8jz! 😀
Very useful option, thanks allot, yet how I can extract into separate sheet only the data related to a specific salesman?
hi thank you so much for this life saver..
I need to create bunch of reports for some of the data in the filter (for example -- for some specific salesperson).. can you please advise how can i do same?
Is there an easy way to then prevent other sales persons from still being shown in the data? For example, what if you wanted to send this report to each salesperson, but only wanted them to be able to see their OWN data - not have the ability to filter other salespeople from that sheet.
Any response to this question? While on the subject. Is there also a way to automate emailing each individual report to each recipient?
@@tatisalas84 If you only want them to see their own data, the best way is to copy the pivot table, then paste values into another sheet. Then email them that worksheet. I don't know of any easy way to mass email, without getting into VBA, and that is pretty advanced. If you have a ton of data to share, there are better tools than excel.
@@tel0004 yes, i had actually found the VBA code in another UA-cam video, but forgot to save it and have been unable to find it again to copy the code for my intended use. At this point I am simply creating the separate tabs, then using a VBA code to disable the filter in the pivots for each salesperson and manually saving each tab as a new file and emailing them individually, until i find a better way.
You can insert a slice to filter the data you want I'm pretty sure
Great!!! If I Have one chart and I need to get one of each sheet, this process will work? Thank you!
Love this feature but sometimes the show filter report pages features is grayed out. I have tried multiple fixes I found but for some reason I'm still having trouble.
I'm having this same issue. Any fixes identified for this?
are you instrumental in creating Qlikview, because you are a genius and know the missing parts of using Excel. Qlik is an awesome slicer and dicer and they would benefit from having you on their team!!!
HI Anna. You use Qlikview? I have just joined a company that uses Qlikview. Do you know how I can learn more about Qlikview or RPX?
very good, Thanks
This was helpful do you have such video for pivot charts too, it will be helpful, thanks
Thats awesome feature in excel... could u plz tell me about how to add xl campus in excel... thanks for the video
R u got any reply regarding for your same question? _pl reply
With the tab hound, if you create a table of contents and send the workbook to someone who does not have tab hound will it still work? Nice feature by the way! Thanks.
Thank you very useful function.
Nice, thanks! Can I create a report for 10 Salespersons rather than 50? Is it All or None Concept? Thanks
Wow thanks
nice explanation!
Thanks for helping. Can we have only data of every name and not pivot of each of them. Pl guide.
Hello Jon, thanks for uploading good videos, Nu I have some issue, i am using Windows 10 Dell Laptop ,i could not unhide My show report filter page, Pls help me
Hi John
If I want to see the list of each sales person how do I do that not just to see the list of sales persons
Thanks appreciate your help! :)
So so good!!!!;) thank you!!!!
Thank you for valuable knowledge sir.it is possible to create Report form in excel like User form.
Very helpful