13 PivotTables
A PivotTable is a powerful data summarization tool. It’s an interactive, dynamic table that allows you to quickly and easily summarize, combine, and compare large amounts of data. For a PivotTable to function correctly, the data must be well-organized in a worksheet.
PivotTables are particularly useful when you need to analyze variables in relation to other variables, helping you answer questions such as:
- Which product contributes the most to profit?
- Which salesperson generates the highest revenue?
- Which department incurs the most costs?
- …
Example 13.1 Sales by Category
The PivotTable in Figure 13.1 allows for an easy comparison of revenue across three product categories.
A PivotTable plots at least two types of data against each other. One data type is placed in the column field (here, productcategory), and the other in the row field (here, town). Additionally, a data type for which you want to see results must be placed in the value field (here, Sales).
Since these results are summarized, you also need to specify the calculation method; in this case, Sum is the default choice.
A PivotTable has four areas where you can place fields:
| Area | Description |
|---|---|
| Values | Fields with numeric information you want to summarize, typically for calculating totals and averages. |
| Rows | Fields used to create distinct groups, with information for each group displayed in a separate row. |
| Columns | Fields also used to create distinct groups, often for subdividing data within rows. These groups are displayed in separate columns. Choose fields with a manageable number of groups to prevent the table from becoming too wide. |
| Filters | Fields used to filter the data. |
13.1 Creating PivotTables
The file for this task contains the monthly sales in 2016 for three supermarkets across different products, as shown in Figure 13.2. These products are categorized into three groups: PVF (potatoes, vegetables, fruit), meat, and dairy. This data will be analyzed using a PivotTable.
Information needed: What are the total sales per town per product category?.
Task 13.1 File: Supermarket.xlsx
Open the file.
Select any cell within the table. Choose tab Insert > PivotTable (Tables group) > From Table/Range.
The Create PivotTable dialog box will appear, with the table range already populated.
- For this task, the PivotTable should appear in the existing worksheet. Select Existing Worksheet and click on an empty cell in the worksheet, for example, cell G3.
- Click OK.
An empty PivotTable will be created on the worksheet, and a task pane will appear on the right side with the PivotTable Fields, where you can add fields to the report.
The task pane with the field list is shown when a cell in the PivotTable is active. If you click on a cell outside the PivotTable, the task pane disappears. It reappears when you click on any cell within the PivotTable.
- In the task pane, drag the town field to the Rows area, the productcategory field to the Columns area, and the sales field to the Values area. When the sales field is added to Values, the
Sumcalculation method is applied automatically.
You can remove a field from the PivotTable by dragging it out of its area.
The PivotTable in the worksheet will be populated with the data.
Task 13.2 Formatting Values as Accounting Numbers
- Select any numeric value in the PivotTable. Choose tab PivotTable Analyze > Field Settings (Active Field group.
The Value Field Settings dialog box is crucial when creating PivotTable reports. Here, you can configure various options, such as:
- The name to be displayed, using the Custom Name box.
- The calculation method (default is
Sum). Other options include:Count,Average,Min,Max,Product,Stdev,Stdevp,Var,Varp. - How you want to display the values, for example, as percentages of the row or column total.
Click the Number Format button. The Format Cells dialog box will be displayed.
Choose the category Accounting > Decimal places: 0 > Symbol: $.
- Click OK > OK.
The value fields in the PivotTable will now appear in the specified format.
Task 13.3 Changing Column Labels and Row Labels to Field Names
Select any cell in the PivotTable. Choose tab Design > Report Layout (Layout group) > Show in Outline Form.
Task 13.4 Analyzing Products Within Each Product Category
- Drag the productcategory field from Columns to Rows and the town field from Rows to Columns. Then, drag the product field to Rows, placing it below the productcategory field.
Instead of total sales, you can also calculate other values, such as the average monthly sales.
- Select any numeric value in the PivotTable and, using Field Settings (see Figure 13.8), change the calculation type to
Average.
Task 13.5 Top 3 products
Now try to answer the following question using PivotTables:
Which three products generate the most sales?
Use product as the row variabele. There is no column variable in this case. In the PivotTable report, sort revenue from highest to lowest.
13.2 Finding Data Sources
A PivotTable contains summarized data. In this task, you will learn how to find the source of this data.
A worksheet contains employee data such as sales, expenses, and commission per month and per region. A PivotTable showing sales per region per employee has been created in another worksheet.
If you want to see the individual values that make up a result, Excel can show you this quickly and easily. By double-clicking on a result, Excel creates a new worksheet containing a table with the source data.
Task 13.6 File: Sales.xlsx
Open the file.
Select the PivotTable worksheet and double-click on the results for Jansen in the East region.
A new worksheet with a list of all information about Jansen in the East region will be created.
13.3 Grouping Data
You can make a cluttered list of data more suitable for analysis by grouping it. This is especially true when the data contains dates. Dates can often be grouped into years, quarters, or months. In a PivotTable, you can view data by year, quarter, or month.
Example 13.2 Revenue per Salesperson
Figure 13.15 shows part of a PivotTable displaying revenue per salesperson. In the left image, the revenue is in date order (ungrouped). In the right image, the revenue is grouped by quarter and by month.
Starting with version 2016, Excel includes a time grouping feature that automatically discovers and groups time-related fields. AutomaticallyWhen you add such a field to a PivotTable, new relevant fields like Years, Quarters and Months are automatically created..
Which date/time fields are added depends on the level of detail of the date/time field in the data table. For example, if the date data is in days and spans more than a year, additional fields are created for months, quarters, and years.
Once these fields have been added, you can begin analyzing the data across different time periods using zoom-in options, which can sometimes reveal additional insights.
Example 13.3 Quarterly Comparison by Year
For example, to get a quarterly comparison over several years, you can place the Years field in the Columns box and the Quarters field in the Rows box.
The + button in the PivotTable indicates a collapsed level. Clicking this button expands all elements in the PivotTable to the next level (here, Months). The + button then changes to a - button, allowing you to collapse the group again. This is also known as zooming in or drill-down.
- Custom grouping
-
You can adjust the grouping by right-clicking on a date/time field in the PivotTable and then choosing Group. In the Grouping dialog box that appears, you can add or remove other time levels.
- Ungroup
-
You can cancel a group by right-clicking on a grouped field in the PivotTable and then choosing Ungroup.
13.4 Grouping Example
This is an example of grouping data based on a date field.
Task 13.7 File: Invoices.xlsx
- Open the file.
The file contains a data table named SalesData with columns Region, Salesman, InvoiceNr, InvoiceDate, ProductCategory, and InvoiceAmount.
First, a PivotTable with sales per salesman per product category will be created.
- Select any cell in the table. Choose tab Insert > PivotTable (Tables group).
Since the data area here is a table named SalesData, this name is automatically filled in instead of cell addresses.
To have the PivotTable appear on a new worksheet, accept the default option New Worksheet.
Click OK.
Create a PivotTable according to the design in Figure 13.18 and format the amounts as currency. The result can be seen in Figure 13.19.
- Drag the InvoiceDate field to the Rows area, placing it below the Salesman field. Excel’s automatic time grouping feature will create two calculated fields, Years and Quarters, in the Rows area.
In the PivotTable (Figure 13.21), sales are now grouped by year. This also shows that Brown started as a seller in 2011, not 2010, which could explain the lower amounts.
Click the + (plus) button next to any seller for the year 2010. This will expand the year to quarters for all sellers.
Click the + (plus) button for Q1 next to any seller. As a result, the quarter will unfold to months for all sellers.
Experiment with unfolding and folding levels again.
Drag the ProductCategory field out of the Columns box. Drag the Quarters field from Rows to Columns, and drag the Years and InvoiceDate fields out of Rows. The design will now look like Figure 13.23. The result can be seen in Figure 13.24.
To easily compare values, you can also choose to display percentages (of row total, column total, or grand total).
- Select a numeric value in the PivotTable and choose tab PivotTable Analyze > Field Settings (Active Field group). In the dialog box, select the Show Values As tab and choose % of Row Total.
- Click OK.
12, Then display the values as % of Column Total.
Questions
Use the PivotTable capabilities to answer the following questions. There are multiple ways to find the answers.
- Which seller sold the most in December 2010?
Seller
- In which month of which year were software sales the highest?
Year Month
- View hardware and software sales percentages by region. What is the percentage for software in the South region?
Rounded to a whole number:
- In what quarter of what year were Anderson’s sales lowest?
Year Quarter
- Check out Brown’s sales in Q2 2011. Do you notice anything special?
Only revenue in the month of June. Nothing in April and May.
13.5 Filtering
To filter data in a PivotTable, you can place one or more fields in the Filters box in the task pane. However, when filtering on multiple items, it can be difficult to see what you are filtering on.
A more user-friendly way is to use slicers. These contain several buttons that allow you to quickly filter data in a PivotTable. For filtering time data specifically, you can also use timelines, which are similar to slicers.
Slicers and timelines are often used in dashboards because they can be linked to multiple PivotTables and PivotCharts.
Three subtasks discuss creating report filters, slicers, and timelines. The file Supermarket.xlsx is used for this, the same file as in Section 13.1, which contains the monthly sales data in 2016 for several products, classified into three product groups: PVF (potatoes, vegetables, and fruits), meat, and dairy.
13.5.1 Report filters
Task 13.8 File: Supermarket.xlsx
Open the file.
Insert a PivotTable for the data on a new worksheet.
Place the productcategory field in Columns, the sales field in Values, and the date field in Rows.
Excel’s automatic time grouping adds the calculated field Months to Rows. No other fields like quarters and years are added because all dates are only the last day of the month within a single year.
Drag the date field from Rows so that only the Months field is shown.
Place the town field in the Filters box. This will create a report filter in the PivotTable.
- Click the drop-down arrow and select “Amsterdam”.
- Click the drop-down arrow, check the Select Multiple Items box, and then select “Amsterdam” and “Rotterdam”.
The data for both towns will be displayed. Unfortunately, the filter only indicates that multiple items are selected, but not which specific items. For this scenario, slicers are a better alternative.
Click the drop-down arrow and re-select the (All) option.
Remove the town field from the Filters box.
13.5.2 Slicers
Task 13.9 Continue with the file from Task 13.8.
- Right-click on the town field in the PivotTable field list and select Add as Slicer.
A slicer will be created in the worksheet.
- Experiment with the slicer by selecting an item. You can select multiple items by holding down the Ctrl key or by using the
button.
Another way to create slicers is through the menu. This method allows you to add multiple slicers at once.
- Click anywhere in the PivotTable report and choose tab PivotTable Analyze > Insert Slicer (Filter group).
- Select Months and click OK.
With the Slicer tab on the ribbon, you can format a slicer, such as changing its appearance, settings, colors, etc.
Experiment with filtering the data using the two slicers.
Delete all slicers by first selecting a slicer and then pressing the Delete key.
13.5.3 Timelines
Timelines are similar to slicers. They also allow you to filter data, but they are specifically designed for use with date/time fields. The dates appear in a horizontal line from oldest to newest as you move from left to right on the timeline.
Task 13.10 Continue with the file from Task 13.9.
- Click anywhere in the PivotTable report and choose tab PivotTable Analyze > Insert Timeline (Filter group).
The Insert Timeline dialog box only shows the date field because it is the only date/time field.
- Select date and click OK.
- A scroll bar for the visible period.
- Handles to select the period to filter on.
- Choice of the time period to be displayed (Years, Quarters, Months, Days).
- When you select a timeline, handles appear on the edges, allowing you to resize the area.
Select some months to see the results.
Click on the period selector in the upper right corner and select Quarters.
Select some quarters to see the results.
Delete the timeline by selecting it and then pressing the Delete key.
13.6 Pivot Charts
A PivotChart displays data series, categories, and chart axes in the same way a standard chart does. However, it also provides interactive filtering, allowing you to quickly analyze a subset of your data. You can also use slicers for filtering data in a PivotChart.
Task 13.11 File: Sales.xlsx
Open the file.
Select the PivotTable worksheet and then select any cell in the PivotTable on this worksheet.
Choose tab Insert > PivotChart (Charts group) > Clustered Column > OK.
The PivotChart has filters for Region and Salesman. When you use a filter in the chart, the data in the PivotTable will also be filtered.
Experiment with filtering by a salesman and observe the results. End by displaying the data for all salesmen.
Add a slicer for Month and experiment with it.
13.7 PivotTables Options
There are various setting options for PivotTables. To access them, select any cell in a PivotTable and then choose tab PivotTable Analyze > Options (PivotTable group). You will find several setting options organized into tabs (see below).
13.8 Exercises
Exercise 13.1 Salary Averages (pivo001)
File: Personnel.xlsx
Create a PivotTable to calculate the average salary per department and division. The table should look as follows:
Exercise 13.2 Salary Totals (pivo002)
File: Personnel.xlsx
Create a PivotTable to calculate the total salary per function and per division. Furthermore, it should be possible to filter by department. The table should look as follows:
Exercise 13.3 Revenue, Travel Expenses, and Commission (pivo003)
File: Pivo003.xlsx
For each salesman, the following information is stored in the worksheet: Month, Region, Salesman, Sales, Expenses, and Commission.
Create one PivotTable to get an overview of the total sales, expenses, and commission per month and per salesman, with the ability to filter by region. All values should have a currency format with no decimals.
Exercise 13.4 Average Rating (pivo004)
File: Pivo004.xlsx
The following table shows the Information Technology (IT) marks for some students from two different classes.
Using a PivotTable, determine the average mark by class and by gender.
Exercise 13.5 Frequency Distribution Figures(pivo005)
File: Pivo005.xlsx
The following table shows the marks of some students.
Create a frequency distribution of the grades using a PivotTable.
Exercise 13.6 Clothing Sales by Region (pivo006)
File: Pivo006.xlsx
The following table shows menswear and ladieswear sales in four regions for the years 2009 and 2010.
Enter and format the data in a worksheet. Using a PivotTable, determine the total annual sales per region and per type.
Exercise 13.7 Shop Opening Hours (pivo007)
File: Pivo007.xlsx
By order of the shopkeepers’ association, a survey was conducted on the evening opening hours of shops. The results from the interviewees can be seen in the following table:
Enter the data in a worksheet. Using a PivotTable, determine the number of supporters and opponents by age and by sex.
Exercise 13.8 Own Home and Average Home Value (pivo008)
File: Pivo008.xlsx
The following table shows, for some people, how many years of study they have, whether or not they own their own home, and, if applicable, the value of the house.
Enter the data in a worksheet. Using a PivotTable, determine the number of people who own their own house and the average value of the house as a function of the number of years of study.
Exercise 13.9 Transport Research (pivo009)
File: Pivo009.xlsx
Market research was performed among visitors to the city center. The following table shows data from respondents who were questioned about their means of transport to the center for shopping. The table indicates whether they went by car or by bus, how far they live from the center (in km), and their travel time (in min).
Enter the data in a worksheet. Using a PivotTable, determine the average travel time by car for residents living more than 15 km from the city center.
Create an extra column to determine if the distance to the center is more than 15 km.
28.5 min.
Exercise 13.10 Coffee Yield per Region (pivo010)
File: Coffee.xlsx
A worksheet contains the yields of some types of coffee per month and per area. Management would like to determine the total yield of the products for each region. Create a PivotTable for the monthly yields per coffee type. The region should be selectable through a report filter.
Exercise 13.11 Fuel Consumption Cars (pivo011)
File: Car.xlsx
Using a PivotTable, determine the minimum and maximum fuel consumption per car brand and fuel type.
Exercise 13.12 Hobby Club (pivo012)
File: Pivo012.xls
In a worksheet, the last two columns show the number of visited club meetings in 2009 and 2010. Create the following reports using PivotTables:
- The number of club members by domicile and by gender.
- The percentage of members by hobby and by gender.
- An overview of attendance in 2010 by gender, with the number of visited club meetings divided into three groups as shown in the following figure:
- The number of members by domicile and by gender
- The percentage of members by hobby and by gender
- Attendance in 2010 by gender