Excel has a great option to split your document into many sub-reports just by activating one option 👍😀. To use this incredible option, you must have your data in a Pivot Table (that's compulsory)
Presentation of the problem
Begin with a basic Excel workbook containing multiple columns such as Category, Sub-Category, Country, etc.
You aim to generate a report detailing sales by Sub-Category, segmented by each country.
Steps to split your workbook in multiple sub-reports
#1: Create a Pivot Table
- Begin by creating a pivot table
- Put the field 'Sub-Category' in the rows and 'Total' in the values section.
For those unfamiliar with pivot tables, a tutorial can be followed here to learn the process.
#2: Add a filter to your pivot Table
The secret lies with one option of the pivot table's filter. While filters may seem outdated compared to slicers, they play a crucial role when you need to divide your report into multiple sub-reports.
- Drag the field Country to the Filter area
- The field is visible above the Pivot Table
#3: Activate the secret pivot table option 😉
And now, here is the magic! 😎 From the menu PivotTable Analyse in the Ribbon
- PivotTable (on the left of the ribbon)
- Then, Options
- And finally Show Report Filter Pages. This option is ONLY enabled if there is a field in the Filter area
- A new dialog appears. Select the field for the splitting values
AND THAT'S ALL! Automatically, you split your report by countries. It's too easy 😎😍👍
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.