Pivot Table – Generate multi-worksheets

Pivot Table is a great tool to split a file into multiple worksheets with a simple action.

Presentation of the tutorial

If you are reading this article it's because you have a huge table and your have to split the contain in many worksheets.

Methodology

To make this work, we must create a pivot table. To see the method, we will create a simple pivot table in which we extracted totals for each client.

Then we focus on the top of our pivot table and we will drop the item State.

If you click on the arrow a drop-down menu displays the list of the states, like a simple filter 😉

Now we will create as many worksheets as there are State in our document. Select in the Pivot Table Tools menu, the Options tab menu and then the sub-menu PivotTable, and finally, the Options sub-sub-menu.

  1. There you click on the arrow just to the right of the word Options
  2. And you select the menu Show Report Filter Pages ...

A window appears asking you with which element you want to create your page. Well, you don't have the choice because the State is the single choice.

And here is the magic! Excel automatically creates a page for each state. 😍😍😍

Important note: you must activate this tool when the value All is selected

Related posts


Permanent link to this article: https://www.excel-exercise.com/generate-multi-worksheets/

10 comments

Skip to comment form

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

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

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

    • Judith on 13/09/2019 at 19:34
    • Reply

    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?

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

    • Robert on 22/07/2019 at 16:51
    • Reply

    Awesome. Thank you very much!

    • Kriti on 24/06/2019 at 04:32
    • Reply

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

    • Pam on 25/02/2019 at 19:37
    • Reply

    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?

      • m00ky on 26/03/2019 at 20:35
      • Reply

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

    • Faye Lowe on 14/08/2018 at 21:02
    • Reply

    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?

      • Afni on 26/12/2018 at 16:57
      • Reply

      hi.

      you can name your table first.

Leave a Reply

Your email address will not be published.