Pivot Table - Filtering

Label Filter
By clicking the filter drop-down menu of any text field, users can always see there is an option of "Label Filter", by clicking this "Label Filter", there is a fly out menu, which offers an abundant choices of Filtering options:

Following is an example we are using "Does Not Equal" option of the Label Filter:

By entering "iPhone" to the box next to "does not equal", we select all items except iPhone, as we want to see how our sales perform on all other items. Once we click "OK", pivot table picks all items without iPhone:

Alternatively, we can also select only showing our sales on "iPhone" by using the "equals" option:

Pivot table changes accordingly:

Other options like "Begins With", "Ends With" offer very specific choices that user can select items "begins" or "ends" with certain character(s). There are also options to allow users to select items "contains" or "does not contain" specific character(s):

 

As an example, we select all items that "does not contain" character "p", and pivot table shows all items as accordingly:

Value Filters

When open the filter drop-down, besides the Label Filters, we will also see the "Value Filters". By clicking the Value Filters, user will activate it's fly out menu, which offers a range of value related options as shown above, such as "Equals", "Does Not Equal", "Greater Than", "Between", etc.

 

We first walk through using the "Greater Than" option:

Once we click "Greater Than" in the fly-out menu, the Value Filter dialog box will pop up, in our example, we enter 10,000 to the box next to "is greater than", which let Excel know that we are looking for all items with values over 10,000:

 

 As shown above, pivot table only showing those items with yearly grand total over 10,000.

 

Next, we select the "Top 10" option of the Value Filter:

The "Top 10 Filter" dialog box will pop up as shown below:

We select "Total Sales" from the drop-down of the box next to the "Top 10 items" which let Excel know that we are looking for the Top 10 based on Total Sales:

Date Filters

By clicking the drop-down menu of the Date field, users will get access to the Date Filters. From its fly-out menu, we can see that it offers a huge amount of options on date filtering, besides those week, month, quarter, year options, users can also combine with "Equals", "Before", "After", "Between" to specify a date or a range of dates.

In the following, we walk through an example by using "Quarter 1" as our option:

 

 

 Pivot table first gives the output in summary form:

 

 Users can click the +sign besides the respective month to express the detailed transactions: