Here’s a great feature in Excel that automatically generates individual worksheets for each filter option you select:
You can download the file here to follow along. If you see a preview, look for the download arrow in the upper right hand corner.
Here is a table of data from which I want to create a PivotTable:
Clicking on the table, selecting PivotTable from the Insert tab, and opting for it to be on a new sheet generates this:
After dragging the fields to the areas I want, my PivotTable has taken shape:
Notice that chose to have both Region and New/Used as Filters for my table. Under Regions I have East, West, North, and South:
The challenge is this: what if I want to create a report for each of the 4 regions, have them on separate sheets, and be able to print them out? That could take a significant amount of time, especially if there were a dozen or more items in a Filter category.
The answer? It’s simple in Excel! Just choose the “Show Report Filter Pages…” option from the Options drop down in the PivotTable group of the Analyze tab:
Once chosen, a dialog box will appear asking you which filter you want to select. You can only choose one:
As soon as I select “Region” and click OK, four new tabs appear in my workbook:
One for each of the items in the Region filter! Now I have a separate PivotTable for each of the Regions of my data on a separate worksheet (here’s the East, for example):
Now that’s a time-saver!