Excel PivotTables | Data Analytics Lesson 14

The PivotTable is a powerful tool of Excel that summarizes data in meaningful ways. It allows users to flexibly drag and drop the data they want to analyze, preview the presentation of the data, and make decisions. This blog provides an overview of PivotTables.

How to Create PivotTables

Assume a dataset as the following [you may paste it to Excel]:

To create an Excel table, follow the steps below:

1. Make sure the dataset has headers for each column.
2. Select any datum (cell) in the dataset.
3. Convert the data range into an Excel Table. (Overview of Excel Tables)
Note: This step is optional but recommended.
4. On the ribbons, select Insert > PivotTable.

5. Make sure the data range for the table is correct.

• If an Excel Table is NOT used: 

• If an Excel Table is used:

6. Choose where to place the PivotTable report. By default, the report will be placed in a new worksheet. For illustration purposes, the PivotTable report is placed in the existing worksheet on cell G3.

7. Select OK to create an empty PivotTable.

Summarizing and Displaying Data

1. Drag the Revenue field to the Values area.

The PivotTable automatically displays the sum of the Revenue field.

2. Drag the Gender field to the Rows area.

The PivotTable now segregates the revenue by the gender of the customer, with the genders displayed in the rows.

The PivotTable can also display genders in columns:

The above result shows that male customers spend more in total than female customers.

[For illustration purposes, all the examples in this blog primarily display the attributes in rows.]

3. Besides the sum of the revenue, we can change how the data is summarized.

To do so,

a) Select any cell in the Values column of the PivotTable.
b) Right click and select Summarize Values By to call the list of functions for data summary.

For example, select the count function to segregate the number of transactions by gender, or select the average function to segregate the average revenue by gender.

The above result shows that, despite a greater number of transactions by male customers, which may account for the higher total revenue, female customers on average spend more.

4. In addition to the summary statistics, we can also change how to display the data.

To do so,

a) Select any cell in the Values column of the PivotTable.
b) Right click and select “Show Values As” to call out the list of ways to display the data.

For example, use the “% of Grand Total” to display the sum of revenues by gender as percentages of the total revenues.

Or use the “Rank Largest to Smallest…” to display the average of revenues by gender as rankings (the gender with the largest average revenue is ranked as 1).

Changing the Attributes to Be Analyzed

To change the fields in the Rows/Columns areas, do the following steps:

1. Remove the fields by dragging the field names out of the areas.
2. Drag the new field into the area.

For example, instead of analyzing the total revenue by gender, we are interested in the total revenue by customer type. Drag the Gender field out of the Rows area and then drag in the Type field.

Filtering and Sorting

PivotTables are embedded with an Excel Auto Filter for filtering and basic sorting.

Consider the following PivotTable:

The Labels column displays the attributes and the Values column displays the results.

Filtering

To filter the Labels column, open the Auto Filter. The attributes displayed in the Labels column can be selected by checking the boxes (e.g., “Premium Member”). Depending on the type of data in the Labels column, text filters, value filters, and date filters can also be used. Because the Labels column now contains only text data, the Label Filters acts as a text filter.

If the Label Column contains datetime data (e.g., Transactions_Date), the Label Filters now act as a date filter and can be used to extract results falling within a certain time period.

Grouping Data

Grouping Text Data

Results in PivotTables can be presented as grouped (clustered) data.

Assume the following PivotTable:

To group the customers as either member or nonmember (i.e., grouping “Member” and “Premium Member” together), do the following steps:

1. Select the attributes to be grouped in the Labels column.
2. Right click and select Group.
3. Change the name of the group in the Formula Bar.

Grouping Datetime Data

If the Labels column contains only datetime data, the datetime data are automatically grouped by years, quarters, months, and then days, forming a hierarchy of groups.

To view the subgroups, click the plus button to expand the groups. For example, expanding the groups for Years shows the subgroups for Quarters; expanding the subgroups for Quarters shows the subgroups for Months.

You can select your own custom grouping based on your needs.

Example: If, instead of the default hierarchy, we are interested in the results for every quarter regardless of the years, follow these steps:

1. Click any cell in the Labels column.
2. Right click and select Group.
3. Choose only Quarters under the By option.

Multi-Dimensional PivotTable

Displaying total revenues by gender or by customer type are examples of a one-dimensional PivotTable. More dimensions can be added by using the following:

1. Columns area (or Rows area if the data are displayed primarily in columns)
2. Levels
3. Filters area

Using the Columns Area

Consider the following PivotTable:

If we would like to explore the revenue from different genders and different customers, we can drag the Type field to the Columns area. Excel will now display a PivotTable with Gender as the rows and Type as the columns.

From the above result, we can see that Nonmember customers in both genders contribute the most revenue. Also, for all the three types of customers, male customers spend more than female customers.

Adding Levels

Consider the following PivotTable:

To explore the revenue from different genders and different customers, drag the Type field to the second level of the Rows area. Excel will now display a PivotTable with Gender as the first level of rows and Type as the second level of rows. This leaves the Columns area for other dimensions such as the Transaction_Date field.

From the above results, we can see that all the revenue in 2019 was contributed by male nonmembers and premium members.

Using the Filters Area

The filters area is similar to an Auto Filter in that it filters the data and shows only the results for a particular aspect.

Consider the following PivotTable:

Dragging the Gender field into the Filters area creates a filter above the PivotTable.

By selecting a single attribute (e.g., Female), the PivotTable shows only the results for that attribute.

If we are interested in filtering out multiple aspects, check the box for Select Multiple Items and select the aspects in the filter above the PivotTable. 

Learning Excel Data Analytics with Gleim

At Gleim, we know learning data analytics is vital for future accounting professionals. That is why we are offering this series of data analytics blogs and continually updating all of our review materials for CPA, CMA, and CIA with the necessary information you need to pass your exams!

We’ll continue our weekly blog series. Check back regularly for all exam news and study tips!

Excel Lessons
Excel Basics
Excel Shortcuts
Excel Calculation Rules
Cell References
Excel Functions
Function Basics
Working with Numbers
Working with Datetime Data
Working with Text Data
Logical Testing
Summarizing a group of data
Filtering and Sorting
Lookup Functions
Tables
PivotTables