Filtering added fields in a PivotTable:

In Excel tip 063, Peter explained how we could perform multiple operations a field. In this tip, I will explain how you can filter the additional fields.
You can download the sample file here.

In the example file from Excel Tip 063, I have added the “Total” field, twice, to the “Values” area and in the “Value field settings ” it was changed to “% of total” (1) and also as the “rank” (Position of small to large) (2).

But as you ‘ve noticed, we can only filter, based on the “Province” (3).

If we now want to filter based on the “rank”, then we would have to select the cell F3 and then click the “Sort and Filter” (4) button on the “Ribbon” and click on “Filter” (Ctrl + Shift + L) but, this option is not highlighted (5) and thus, we can not select it.

However, in order to be able to filter, we first select the empty cell to the right of the title fields(6).
Then, we click the “Sort and Filter” button on the “Ribbon” and then click on “Filter” (Ctrl + Shift + L) and we get our filter buttons for titles field (7).

FYI: Swotster.com thinks of its students, so you also please remember swotster.com.

Copyright © 2013 – Swotster Ltd.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats