Data Management.com

pivot table

By Sarah Lewis

A pivot table is a statistics tool that summarizes and reorganizes selected columns and rows of data in a spreadsheet or database table to obtain a desired report. The tool does not actually change the spreadsheet or database itself, it simply “pivots” or turns the data to view it from different perspectives.

Pivot tables are especially useful with large amounts of data that would be time-consuming to calculate by hand. A few data processing functions a pivot table can perform include identifying sums, averages, ranges or outliers. The table then arranges this information in a simple, meaningful layout that draws attention to key values.

Pivot table is a generic term, but is sometimes confused with the Microsoft trademarked term, PivotTable. This refers to a tool specific to Excel for creating pivot tables.

How pivot tables work

When users create a pivot table, there are four main components:

1. Columns- When a field is chosen for the column area, only the unique values of the field are listed across the top.
2. Rows- When a field is chosen for the row area, it populates as the first column. Similar to the columns, all row labels are the unique values and duplicates are removed.
3. Values- Each value is kept in a pivot table cell and display the summarized information. The most common values are sum, average, minimum and maximum.
4. Filters- Filters apply a calculation or restriction to the entire table.

For example, a store owner might list monthly sales totals for a large number of merchandise items in an Excel spreadsheet. If they wanted to know which items sold better in a particular financial quarter, they could use a pivot table. The sales quarters would be listed across the top as column labels and the products would be listed in the first column as rows. The values in the worksheet would show the sum of sales for each product in each quarter. A filter could then be applied to only show specific quarters, specific products or averages.

Uses of a pivot table

A pivot table helps users answer business questions with minimal effort. Common pivot table uses include:

• To calculate sums or averages in business situations. For example, counting sales by department or region.
• To show totals as a percentage of a whole. For example, comparing sales for a specific product to total sales.
• To generate a list of unique values. For example, showing which states or countries have ordered a product.
• To create a 2x2 table summary of a complex report.
• To identify the maximum and minimum values of a dataset.
• To query information directly from an online analytical processing (OLAP) server.