Applying filters to a Pivot Table – Excel 2007+

August 8, 2011

This blog post provides readers with a basic introduction to a pivot table and using the field list and filtering features in pivot tables.

A pivot table is an Excel object which is embedded within an Excel worksheet and affords the user the opportunity to use the mouse to move and filter fields from the underlying data source in a very quick and easy way. Below is an image of a typical pivot table, in this case a list of customers with associated information. This post assumes that you have been presented with a pre-built pivot table and are interested in augmenting it.

The field list contains a list of fields available in the data set that the pivot table is based on. The ‘Pivot Table Tools’ tab becomes active in the ribbon when the pivot table is selected and in the sub-tab ‘Options’, the ‘Field list’ button toggles the visibility of the field list. Once this list is visible, the fields in this list can be used in the pivot table by simply selecting the checkbox beside each field or dragging the field into the appropriate section.

The image below provides a closer look at the field list toggle button and field list.

Each field in the pivot table exposes a filter button which allows basic checkbox filtering.

The filter button exposed by a field also provides more advanced filtering options. The example below filters the report by all phone numbers beginning with ‘*604*’.

By selecting the filter on the first field in the row section of the pivot table, the report can be filtered by any of the value fields. In this example the report is filtered to display only customers having a last sale exceeding $23,000.

Hopefully this introduction to using pivot tables provides you with a starting point to apply your creativity to interrogating and augmenting your Sage Intelligence reports.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: