If you have to split a report by products, cities, employees, ... you can do it in one action with a pivot table 😉
Presentation of the problem
Let's say you have this data
I want to create a report of the sales by countries and then split this report by category of product.
Insert your data in a pivot table
So, let's create a pivot table with the countries and the sales
Create a filter
The secret lies with one option of the pivot table's filter.
At this step, we have no filter in our pivot table
When we drag-&-drop the field "Category" in the Filters area, automatically, a filter section is added above the pivot table.
Split the report
And now, here is the magic! 😎 Go to the menu
- Pivot Table Analyse
- PivotTable
- Options
- Show Report Filter Pages
A new dialog appears and you select the field. Here there is only one filter 😉
AND THAT'S ALL! Automatically, your report is split for category. It's too easy 😎😍👍
And now, you have one worksheet for each item in the category field
Don
Frédéric LE GUEN
Blake
Shavkatbek Uzbekistan
A Mac
kalyani
Uma Maheswari
Devendra Phalak
Judith
Robert
Kriti
Pam
m00ky
Faye Lowe
Afni
15/06/2023 @ 20:26
Is there a way to get all the split data into one sheet instead of multiple sheets?
19/06/2023 @ 16:13
Possible. Can you explain more what you want to do?
21/03/2023 @ 21:10
Excellent information, thank you. I would like to use this to split a long list into individual clients, then print off a quote sheet. Is there a way to set up the sheets as a sort of letterhead? Or at least to copy the headers from the original sheet?
14/10/2021 @ 11:55
Thank you. The skill is handy!
07/07/2021 @ 20:24
Do the tabs have to be created in alphabetical order or can it be filtered before the sheets are created? For example, The total cost by vendor number greatest to least compared to the vendor name in alphabetical order.
28/09/2020 @ 09:43
Thanks. it's amazing.
23/02/2020 @ 15:04
is there any way to update the multiple sheet when pivot sheet is updated
27/02/2020 @ 09:59
Pivot Table > Refresh > All
22/09/2019 @ 16:06
Is there any way to extract this in Excel Workbooks & not in Excel Sheets?
08/10/2019 @ 05:21
Hi, the only way is to do a macro if you want to do that
13/09/2019 @ 19:34
I have created separate sheets for everyone but the sheets, when shared out, allow the filter to be used (allowing other users access to other data). How can I shut that off? If I protect the worksheet, the user can not use the excel file to manipulate the data on their own worksheet.
Any ideas?
14/09/2019 @ 16:03
Hmmm, no. No idea.
By default, a pivot table has filter. I don't think there is a way to block that.
22/07/2019 @ 16:51
Awesome. Thank you very much!
24/06/2019 @ 04:32
how can i only show the filtered data in each tab created? currently its show all the data (hidden by filter selection)
25/02/2019 @ 19:37
I have completed the process, but not all tabs are showing the "name" on the tab. Some names are showing in tabs, and some are not (instead, they are just listed as sheet #.) When looking at that tab data, it DOES reflect the name of the filtered data, but just did not carry the name to the tab. Why would some work and not all?
26/03/2019 @ 20:35
Pretty sure I had this same problem due to the names containing characters that are not allowed in tab names.
14/08/2018 @ 21:02
I have been trying this but my tabs are not naming. All it is giving me is Sheet1, Sheet 2, Etc. What am I doing wrong?
26/12/2018 @ 16:57
hi.
you can name your table first.