Pivot Table

Split report to many sub-reports automatically

Reading Time: 2 minutes

If you have to split a report by products, cities, employees, ... you can do it in one action with a pivot table 😉

As many worksheets as there is items in the filter highlight

Presentation of the problem

Let's say you have this data

Organisation of the data to be use with a pivot table

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

Drag and Drop to create the calculation

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

Pivot table with no filter

When we drag-&-drop the field "Category" in the Filters area, automatically, a filter section is added above the pivot table.

Field as Filter for the pivot table

Split the report

And now, here is the magic! 😎 Go to the menu

  1. Pivot Table Analyse
  2. PivotTable
  3. Options
  4. Show Report Filter Pages
Menu Show Report Filter Pages

A new dialog appears and you select the field. Here there is only one filter 😉

Select the filter to split your report

AND THAT'S ALL! Automatically, your report is split for category. It's too easy 😎😍👍

Split Report Filter

And now, you have one worksheet for each item in the category field

As many worksheets as there is items in the filter

Related posts

Pivot Table Percentage

Frédéric LE GUEN

Add filter option for all your columns in a pivot table

Frédéric LE GUEN

Pivot Table – Presentation

Frédéric LE GUEN

13 comments

kalyani 28/09/2020 at 09:43

Thanks. it's amazing.

Reply
Uma Maheswari 23/02/2020 at 15:04

is there any way to update the multiple sheet when pivot sheet is updated

Reply
Frédéric LE GUEN 27/02/2020 at 09:59

Pivot Table > Refresh > All

Reply
Devendra Phalak 22/09/2019 at 16:06

Is there any way to extract this in Excel Workbooks & not in Excel Sheets?

Reply
Frédéric LE GUEN 08/10/2019 at 05:21

Hi, the only way is to do a macro if you want to do that

Reply
Judith 13/09/2019 at 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?

Reply
Frédéric LE GUEN 14/09/2019 at 16:03

Hmmm, no. No idea.
By default, a pivot table has filter. I don't think there is a way to block that.

Reply
Robert 22/07/2019 at 16:51

Awesome. Thank you very much!

Reply
Kriti 24/06/2019 at 04:32

how can i only show the filtered data in each tab created? currently its show all the data (hidden by filter selection)

Reply
Pam 25/02/2019 at 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?

Reply
m00ky 26/03/2019 at 20:35

Pretty sure I had this same problem due to the names containing characters that are not allowed in tab names.

Reply
Faye Lowe 14/08/2018 at 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?

Reply
Afni 26/12/2018 at 16:57

hi.

you can name your table first.

Reply

Leave a Comment