What pivot tables can do in spreadsheets

Wikipedia describes pivot table as a data summarization tool found in spreadsheets and business intelligence software. Although, the tool is used mainly for summarization, the tool has been put into other uses in various software. Pivot tables can do graphs, calculate values, percentage changes and so on and so forth.

Making a basic pivot table

To make a basic pivot table, there are few things that a spreadsheet user should

understand. The main objective is to aggregate the values in a numerical field or column. To illustrate, I'll use this web page that shows how to make a basic pivot table. In the data, the amount column has the numerical data and will be a vital component of the pivot table.

Every pivot table summarizes a numerical data from a column in a workbook. It uses filters to select data that fit a certain criteria whilst summarizing a numerical data. There are 3 components of pivot table and they are :

Rows labels: These are row filters applied to pivot tables whilst working on numerical data.

Column labels: These are column filters applied to pivot table whilst working on numerical data

Report Filters : This filter selects rows and columns that meet a set criteria whilst working on data.

Values: This is the numerical data to be worked on. Aggregation functions like average,count,sum, product can be performed on the data. Statistical functions like standard deviation can be performed as well.

Values

These values worked on can be taken directly from the data or derived from a calculation. The values can be formatted and displayed according to user requirements. They can also be displayed in special ways by using the "Show Values As' tab.

 

The diagram above shows the shows the 'Show Value As' tab. The columns or fields in the data can determine how values are displayed. There are are 15 different calculations that can determine data is displayed based on columns or fields in the data. This website explains the process in details. Using fields or columns as base field, it is possible to calculate percentage difference between fields, rankings between fields. Thorough planning is needed to pick a base field that will be able deliver what user intended.

Charts

This is basically the process of visualizing data from pivot tables. The advantage of this graph is the use of filters to visualize data that meets a given criteria. This website explains the process in details. Pivot chart is the quickest way of graphs that are supposed to meet multiple criteria. The diagram below is an example of a pivot chart. See the filter buttons that change the graphs depending on what filter is applied.

There's limit on how data can be manipulated with pivot tables, all is needed is planning to figure out what the end result should look like.

Enjoy working with pivot table.