Split report to many sub-reports automatically

Split report to many sub-reports automatically
Last Updated on 13/09/2020
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

18 Comments

  1. Don
    15/06/2023 @ 20:26

    Is there a way to get all the split data into one sheet instead of multiple sheets?

    Reply

    • Frédéric LE GUEN
      19/06/2023 @ 16:13

      Possible. Can you explain more what you want to do?

      Reply

  2. Blake
    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?

    Reply

  3. Shavkatbek Uzbekistan
    14/10/2021 @ 11:55

    Thank you. The skill is handy!

    Reply

  4. A Mac
    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.

    Reply

  5. kalyani
    28/09/2020 @ 09:43

    Thanks. it's amazing.

    Reply

  6. Uma Maheswari
    23/02/2020 @ 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 @ 09:59

      Pivot Table > Refresh > All

      Reply

  7. Devendra Phalak
    22/09/2019 @ 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 @ 05:21

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

      Reply

  8. Judith
    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?

    Reply

    • Frédéric LE GUEN
      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.

      Reply

  9. Robert
    22/07/2019 @ 16:51

    Awesome. Thank you very much!

    Reply

  10. Kriti
    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)

    Reply

  11. Pam
    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?

    Reply

    • m00ky
      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.

      Reply

  12. Faye Lowe
    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?

    Reply

    • Afni
      26/12/2018 @ 16:57

      hi.

      you can name your table first.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Split report to many sub-reports automatically

Reading time: 2 minutes
Last Updated on 13/09/2020

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

18 Comments

  1. Don
    15/06/2023 @ 20:26

    Is there a way to get all the split data into one sheet instead of multiple sheets?

    Reply

    • Frédéric LE GUEN
      19/06/2023 @ 16:13

      Possible. Can you explain more what you want to do?

      Reply

  2. Blake
    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?

    Reply

  3. Shavkatbek Uzbekistan
    14/10/2021 @ 11:55

    Thank you. The skill is handy!

    Reply

  4. A Mac
    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.

    Reply

  5. kalyani
    28/09/2020 @ 09:43

    Thanks. it's amazing.

    Reply

  6. Uma Maheswari
    23/02/2020 @ 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 @ 09:59

      Pivot Table > Refresh > All

      Reply

  7. Devendra Phalak
    22/09/2019 @ 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 @ 05:21

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

      Reply

  8. Judith
    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?

    Reply

    • Frédéric LE GUEN
      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.

      Reply

  9. Robert
    22/07/2019 @ 16:51

    Awesome. Thank you very much!

    Reply

  10. Kriti
    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)

    Reply

  11. Pam
    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?

    Reply

    • m00ky
      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.

      Reply

  12. Faye Lowe
    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?

    Reply

    • Afni
      26/12/2018 @ 16:57

      hi.

      you can name your table first.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *