How Pivot Tables Work

Overview

There are four components to a pivot table:

1. Values (Things you measure)
2. Rows (Things you measure by)
3. Columns (Split Values into Buckets, like Sales Rep)
4. Report Filter (Filter Report by items not in the report body)

The Excel Add-In makes available fields for your Pivot Table Report. The rest is just Excel

The fields in the ‘Choose fields to add to report' area can be dragged into the following four main Pivot Table areas:

Values

Values are the engine of any analysis, because they represent MEASURES, e.g. Sum of Quantity, Sum of amount, Avg Rate, Maximum Credit Limit, etc.

Here we drag the field 'Line Sales Amount' into the Values Quadrant. The value appears in the Upper Left-Hand side, in the Pivot Table Report area.

Rows

Row Labels provide CONTEXT for the items you place in the Values box. In other words you GROUP BY the items you place in the Rows area.

Columns

This is a powerful feature that lets you ‘split’ the values column into different buckets. e.g. class, sales rep, etc.  In this example, we dragged the field ‘Sales Rep Name' in the Columns area to get this result:

Report Filters

This is one of three available filter options and allows you to filter the whole block of data.  Here we dragged the field 'Class Name' into the Filters Quadrant.

Learn more about other filtering methods in a Pivot Table.