Advanced Pivot Tables and Slicers

We are going to continue with pivot tables in this chapter to explore some advanced features. An identical data set to the previous pivot table chapter will be used with a couple of additions: Money Spent and Quantity Purchased columns.

Figure 26.1

Practice Spreadsheet

Use this workbook for the chapter.

The first question we want to answer in this chapter will reuse the pivot table from the previous chapter, and we want to know the percentages of products purchased in three locations. We will begin by naming the data set for quick reference and inserting a pivot table in a new spreadsheet with Type of Fishing Preferred in the columns field, Store in the rows field, and Store in the values field.

Figure 26.2

Value Field Settings

The initial pivot table in Figure 26.2 does not present the percentage data we need in this scenario. Instead of manually producing percentages using functions, we can have the pivot table do it automatically by modifying the value settings.

  1. Select a cell in the pivot table.
  2. Right-click the Count of Store in the Values field of the PivotTable Fields menu.
  3. Select Value Field Settings to open the Value Field Settings menu.
  4. Select the Show Values As tab.
  5. Select the % of Grand Total option from the Show values as drop-down list. (See Figure 26.3)
  6. Press OK to apply the change to the pivot table.
Figure 26.3

The pivot table will now show the percentage values across the table instead of the previous count totals. The percentages are based on the grand total count of 1,500 from Figure 26.2. The percent of grand total is useful to understand the company's total sales, but what if we want to know the percentages of sales by store or by-product? We could change the Show values as setting to % of Column Total to view sales by store for a geographic perspective, or % of Row Total to view sales by preferred bait for a product perspective.

Pivot Charts

If we want to view the data of our new pivot table in a chart, we can create a pivot chart based on the table. For this scenario, we'll insert a simple clustered column chart.

  1. Select a cell in the pivot table.
  2. Go to the PivotTable Analyze tab of the ribbon toolbar.
  3. Select PivotChart to open an Insert Chart menu.
  4. Select Clustered Column from the Column list.
  5. Press OK to insert the chart. (See Figure 26.4)

A notable difference with this chart is that it includes a button for selecting filters. Filters can be added by dropping additional chart items to a relevant field. For example, we could add Type of Fishing Preferred to the Legend (Series) field to include a filter for types of fishing methods.

Figure 26.4

Filter Slicers

If we have three pivot charts to present percentages of grand total, column total, and row total individually, we may also want the ability to filter the data for all three at once. Slicers can be created to do just that.

  1. Select any one of the pivot tables.
  2. Go to the PivotChart Analyze tab of the ribbon toolbar.
  3. Press the Insert Slicer button to open a selection menu. (See Figure 26.5)
    Figure 26.5
  4. Select the categories to be used for slicer filters. (See Figure 26.6)
    Figure 26.6
  5. Press OK to create the slicer(s).

The newly created slicer(s) will only work on the initially selected chart, but we can modify the slicer to work for multiple charts that use the same data.

  1. Select the Slicer box.
  2. Press the Report Connections button in the Slicer tab of the ribbon toolbar.
  3. Select the charts to be included in the slicer's control. (See Figure 26.7)
  4. Press OK to apply the change.
    Figure 26.7

Due to slicers working as filters, the built-in filters for each chart become redundant. These filters can be removed by selecting the chart, going to the PivotChart Analyze tab, pressing Field Buttons, and selecting Hide All.

Additionally, to select multiple options of a slicer filter, pressing the CTRL key before selecting a second or more options as necessary.

Calculated Fields in a Pivot Table

In another scenario, we need to know the average cost customers paid per unit. To calculate the cost, we'll create a new pivot table using Money Spent and Quantity Purchased in the Values field. Additionally, Store will be added to the Rows field. The resulting pivot table will display the total money spent at each store and the amount of product purchased.

Note: The values need to be formatted appropriately with a comma separator and relevant currency symbol by using the Number Format button in the Value Field Settings menu.

Now we need to find the average cost per product unit. We can do this manually by dividing the money spent by the quantity purchased, but we want to include it automatically in the pivot table. We'll do this by adding a field.

  1. Select a cell in the pivot table.
  2. Go to the PivotTable Analyze tab of the ribbon toolbar.
  3. Press the Fields, Items, & Sets button.
  4. Select Calculate Field… to open a new menu. (See Figure 26.8)
    Figure 26.8
  5. Name the field Average Price.
  6. Edit the formula by inserting Money Spent and typing a forward slash ( / ) to indicate division.
  7. Insert Quantity Purchased to complete the calculation (shown as ='Money Spent'/'Quantity Purchased' in Figure 26.9).
  8. Press OK to add the new field.

Note: The new field's settings will need to be adjusted to format the value correctly as currency with two decimal places.

Figure 26.9

We can extend the results into types of fishing preferred and gender to identify average costs by product and gender. In Figure 26.10, we can see female customers bought fewer spinners for a higher price per unit cost in Ammon, Idaho.

Figure 26.10

Supplemental Resource

This content is provided to you freely by EdTech Books.

Access it online or download it at