Getting selective in your page fields in pivot tables. We show you a trick.
We showed how to select only certain items in a pivot table field. However, if the same field is moved to a page field, you can't select only certain items in the page field.
There is a way to work around this. We show you a trick.
The exercise and result are in the Pivot Table Page Adjust file.
Here is a pivot table where we did a drop down on the Rep field, which is set up as a Page Field. Notice that unlike a row or column field, you do not get boxes where you can select which items in the page field you want to display.
There is however a way to work around this. Step one is move the page field (click on the name of the field- in this case, rep, and drag it so it becomes a row or column field.
Then click on the down arrow next to the name and select the values you want. In this example, we will select reps 1, 2 and 3.
That gives us a slimmed down pivot table report with results for just the 3 reps.
Finally, drag the rep field back to the page area to make it a page field again.
Here is the finished pivot table. If you clicked the down arrow by the rep, you would only see the 3 reps listed now. Notice how the description to the right of the page field name ahs changed to multiple items from All (see the top of this page). This tells you that some items have been filtered out fo the page field.
If you want to include other reps in the page field, you can repeat the process and add more reps when you have the field as a row or column field.
Note that we also put a title on the pivot table so it is clear that it now only includes certain reps.
Te recap, here is the process to filter items in or out of a page field:
Drag the page field so it becomes a row or column field
Click on the down arrow next to the field and select or unselect the items you want from the field
Drag the field back to the page field area above the pivot table body
Add or modify the title as you wish.
Filtering isn't just for row or column fields. Now you know how to do it for page fields also.