«

»

Pivot Table - Generate multi-worksheets

Pivot Table is a great tool to split a file into multiple worksheets

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, 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

Related articles


Have a look at these other articles that could help you in your work

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


Leave a Reply

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