How Pivot Tables Work

Modified on Sun, 26 Jul at 4:04 AM

Overview

A PivotTable contains four primary areas:

Area	Purpose
Values	The numeric results being measured.
Rows	The categories used to group the measured values.
Columns	Additional categories used to split the values into side-by-side groups.
Filters	Fields used to filter the entire PivotTable without appearing in the report body.

What QQube Provides

The QQube Excel Add-In makes the available fields accessible to the PivotTable. After the fields are added, the remaining report design uses standard Excel PivotTable functionality.

Excel PivotTable report created with QQube fields

PivotTable Fields Pane

Fields listed under Choose fields to add to report can be dragged into the four PivotTable areas.

Excel PivotTable Fields pane showing Filters, Columns, Rows, and Values areas

The Four PivotTable Areas

Values

Purpose: Defines what the PivotTable measures.

Examples include:

Sum of Quantity.
Sum of Amount.
Average Rate.
Maximum Credit Limit.

In this example, Line Sales Amount is dragged into the Values area. The summarized value then appears in the PivotTable report.

Line Sales Amount placed in the PivotTable Values area

Rows

Purpose: Provides context for the measured values.

Fields placed in the Rows area group the values by a category such as Customer, Item, Account, or Transaction Type.

Field placed in the PivotTable Rows area to group the measured values

Columns

Purpose: Splits the measured values into additional side-by-side groups.

Common Column fields include Class, Sales Representative, Year, or Month.

In this example, Sales Rep Name is placed in the Columns area.

Sales Rep Name placed in the PivotTable Columns area

Filters

Purpose: Filters the complete PivotTable by a field that does not appear in the report body.

In this example, Class Name is placed in the Filters area.

Class Name placed in the PivotTable Filters area

How the Areas Work Together

A typical PivotTable may use:

Line Sales Amount in Values.
Customer Name in Rows.
Sales Rep Name in Columns.
Class Name in Filters.

This creates a report that summarizes sales by customer, separates the results by sales representative, and allows the entire report to be filtered by class.

Additional Filtering Options

Report Filters are only one PivotTable filtering method.

Learn more about other filtering methods in a PivotTable.

Expected Result

Use Values to define what is measured, Rows and Columns to organize the results, and Filters to limit the complete report.