As data becomes more sophisticated, PivotTables can be used to analyze data and help make decisions. A PivotTable is a dynamic table that summarizes, organizes, and compares large amounts of data. Rows and columns may be rotated in a PivotTable to allow different views of the same data. The ability to “pivot” the dimensions of the table—for example, to transpose column headings to row positions, gives the PivotTable its name.
PivotTables help to answer questions about the data.
Here are some questions a PivotTable might answer:
- How many students took ACCT101 and averaged higher than a B?
- How much total salary is paid by each department?
- How many people aged 25-29 bought tickets to PG rated movies?
- What was the total income from units of PR2000 that were sold in the East region?
- What was the average number of calls to a customer service line by day, month, and year?
This is an example of a simple PivotTable:
This example shows the number of tickets sold by movie rating (PG, PG-13, and R) and age of the purchaser (13 years - 19 years). The Rating field is the row label and the Age field is the column label. The number of tickets sold by age and rating appears at the intersection of the fields.
Rows and columns can be pivoted, or rotated, to allow for different views of the same data. This is a safe way to manipulate data as the data itself remains unchanged. Only the view is altered.
In this example, the Age field was moved to the rows area and the Rating field to the columns area. This created a new way of looking at the same data.
The following video gives a short introduction into PivotTables.
Organizing Source Data
The data on which the PivotTable is based is called the source data. This source data is often contained in an Excel workbook, but data in external sources can also be used.
Before creating a PivotTable, the source data needs to be organized in such a way that Excel can manage it correctly. Putting forth a little extra time up front will save time later in helping to get the results you want from your PivotTables.
Some considerations are:
- All related data should be in the same column to make the analysis easier. For example, keep all of the related dollar amounts in the same column, regions in the same column, dates in the same column, etc.
- Each row should contain information about one record.
- Each column must contain a unique heading.
- A unique identifier, such as an ID number, is helpful for each row, especially when counting records.
- Do not include any blank rows or columns within the source data. It is best to have only the source data in a worksheet.
- Detailed source data should be separated into multiple columns. For example, you will want the month separated from the year, or the address separated into street, city, and state. Then you will be able to analyze the data by using these individual fields.
- It is easier to maintain source data by first converting the data into an Excel table. The table range will automatically adjust if rows or columns are added or removed, and you will not have to recreate the PivotTable.
The Financial_Report data was previously converted into an Excel table named Report. This Excel table will be used as your source data.
The following video gives a short introduction to organizing source data.