PivotTables are used to organize and summarize large amounts of data.
PivotTables also allow us to change how we summarize data to look at it in different ways—that’s where the name comes from. We can look at the data one way and then “Pivot” or change how we examine the data. This allows us to perform some fairly sophisticated analyses quickly and easily. Optimize Source Data
To effectively uncover patterns in data using a PivotTable, first ensure that the source data are organized correctly. The following bullet points discuss important elements of effective table design: Table headings should compose the first row of the table. Each column should contain different information. None of the columns (headings or data) should be duplicated. Each row should contain information about only one observation. Each observation should be constrained to only one row (not split across rows). Group Dataset by Qualitative Data PivotTables allow the user to choose the fields that will be used to group or classify the data. Technically, any field in the dataset can be used to create groups in a PivotTable. The PivotTable displays all the unique values for a field as the individual groups. PivotTables allow the user to choose the fields that will be used to group or classify the data. Technically, any field in the dataset can be used to create groups in a PivotTable. Summarize Quantitative Data within Groups Once the data in a PivotTable is classified into the groups, the values of the PivotTable are calculated. This calculation has two parts: the measure that will be used and the algorithm that will be applied to that measure. The calculation is performed on every non-labeled cell in the PivotTable. Each of these cells represents the intersection of the categories defined by the row and column labels. Only the data points that fit the intersecting categories are used in the calculation for each cell. Both the measure and algorithms used in a calculation are determined by the user. The measure below used in the calculation is Gas Price. The algorithm is to determine the average gas price. The groups for the PivotTable are the individual regions in the dataset. To create a PivotTable, we select the PivotTable icon from the "Insert" group of menu items. The Create PivotTable dialog window appears. The Create PivotTable dialog window () allows you to select the data source for the PivotTable. In this lesson, the data are located in a range within the open workbook
Pivot Table Options
The Elements of a Pivot Table
Before we start to define how the PivotTable will organize the data, it is important to describe each element of a PivotTable: As the name implies, a Filter is used to screen the data points as they are placed in a PivotTable. When a data field is used as a filter, we can select which elements of that data field will be displayed on the PivotTable. For example, if I were to use “Region” as a filter for the data used in this lesson, I could choose which regions to display on the PivotTable. Columns and Rows are used to define how the data will be summarized and arranged on the PivotTable. When a data field is defined as a column or row, each different value for that field will be displayed as a column or row heading on the PivotTable and the source data will be categorized by those values Values element of the PivotTable defines which data will be displayed on the PivotTable. More than one field can be displayed at the same time as the Values for the PivotTable. You can also perform calculations on the data in the PivotTable and define how the data will be formatted.
Section 2: Pivot Tables
