Creating pivot tables by the filter

When working with pivot tables, you will sometimes use filters to change how the data is presented in report based on the selected criteria. Using filters or using slicers are completely legitimate methods. But what if you want to create a separate report for each criterion you used in the filter. Excel has a simple option that allows you to do this…

We have created a pivot table. We added the NAZIV ARTIKLA (ITEM NAME) to the rows, the values ​​contain the sum of the IZNOS (AMOUNT) column, and we used the GRAD (CITY) reporting dimension for the filter.

To split this pivot table into several worksheets, where you have one filter value in each of them, go to the PivotTable Analyze ribbon, open the Options menu, and start the Show Report Filter Pages option.

After this action, a window will appear in which you need to select the dimension on the basis of which the filtering is performed. We’ll choose GRAD (CITY), because it’s the only one we added as a pivot table filter. Excel will create as many new worksheets as there are values ​​in the filtered dimension. In each worksheet, there will be a pivot table with a filter value corresponding to one of them. For example in the worksheet “Beograd” there will be a pivot table where we have filtered the pivot table based on the dimension GRAD which contains the value “Beograd”, etc. This option can be especially useful if you want to print pivot tables based on the selected filter criteria!