A pivot table is a statistics tool that summarizes and rearranges selected columns and rows of data in a spreadsheet or database table to achieve a desired report. The tool does not actually modify the spreadsheet or database itself, it simply “rotates” or transforms the data to view it from different perspectives.
Pivot tables are especially useful with large amounts of data that would take time to calculate by hand. A few data processing functions that a PivotTable can perform include identifying sums, averages, ranges, or outliers. The table then organizes this information into a simple and meaningful presentation that draws attention to key values.
PivotTable is a generic term, but it is sometimes confused with the Microsoft brand term, PivotTable. This is an Excel-specific tool for creating pivot tables.
How Pivot Tables Work
When users create a pivot table, there are four main components:
- Columns – When a field is chosen for the column area, only the unique values of the field are listed at the top.
- Rows – When a field is chosen for the row area, it populates as the first column. As with columns, all row labels are unique values and duplicates are removed.
- Values - Each value is kept in a cell of the pivot table and displays summary information. The most common values are sum, average, minimum, and maximum.
- 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 items in an Excel spreadsheet. If they wanted to know which items were selling the best in a particular financial quarter, they could use a pivot table. The sales quarters would be listed at the top as column labels and the products would be listed in the first column as rows. The values in the spreadsheet would show the sum of each product’s sales in each quarter. A filter could then be applied to show only specific quarters, specific products or averages.
Uses of a pivot table
A pivot table helps users answer business questions with minimal effort. Common uses of pivot tables include:
- For calculating sums or averages in business situations. For example, counting sales by department or by region.
- To display totals as a percentage of a whole. For example, comparing sales of a specific product to total sales.
- To generate a list of unique values. For example, showing which states or countries ordered a product.
- To create a 2×2 summary table 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.
This was last updated in August 2019
Continue Reading About Pivot Table