↑ Return to Pivot Table

Generate multi-worksheets

PivotTables have a great tool to split a file into multiple worksheets.

Presentation

Often you would like to split your main workbook in several worksheet, like in this example where the main data sheet is cut by state.

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, much like a data 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.

There you click on the arrow just to the right of the word Options 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 there’s magic! Excel automatically creates a page for each state.

Important note: you must filter your page or on (All) to forget the creation of any spreadsheet

23 comments

Skip to comment form

  1. Ramakrishna

    Good Tip Thank You

  2. Anonymous

    Is there a way to create a tab with just the raw data? each tab appear to be simply a filter of the master pivot which uses a lot of resource. By flattening the data the tab can simply be dragged to a blank spreadsheet and emailed.

  3. Anonymous

    Thank u.. This was really helpful

  4. SNOOP HITLER

    HIHIHIHIHIHIHIHHIHIHIHIHIHHIHIHIHIHIH

  5. porongas jugosas

    holaaaaaaaaaa

  6. SNOOP HITLER

    HI

  7. El PrinciPITO

    yo mama, tenes organo masculino?

  8. SNOOP HITLER

    HITLER

  9. Donald Duck

    Sup?

  10. El PrinciPITO

    sup danald

  11. Donald Duck

    Sup homies!

  12. yo moma

    arriba la megan zorra

  13. El PrinciPITO

    yo desde esta glaxia a la mas lejana

  14. yo moma

    yo la amo con todo el organo sexual masculino mio

  15. El PrinciPITO

    i luv her

  16. yo moma

    arriba la miss karina

  17. El PrinciPITO

    jaime cuanto es 1 +1?

  18. El PrinciPITO

    holaaaaaaaaaa

  19. yo moma

    alo

    como le va babies

  20. yo moma

    daniel deja de jugar

  21. Gee Johnson

    Great…great tip!

  22. prashanth

    this website is helpful us……………………. thank u………………………..

  23. Venkat

    This is really helpful, thank you so much…:)

Leave a Reply

%d bloggers like this: