Chapter 14: PivotTables and PivotCharts

A PivotTable summarizes a large list in a few clicks. Given thousands of sales records, a PivotTable can total sales by region, then by product, then by quarter, and rearrange the summary as fast as you can drag a field. A PivotChart is a chart built on a PivotTable that changes along with it. Together they're about a quarter of MO-211, in the Manage advanced charts and tables domain. Lesson 11 of Introduction to Excel covers PivotTables, but it's outside the course's Lessons 1–10, so this chapter starts from the beginning.

14.1 Creating a PivotTable

14.1.1 Preparing the data

A PivotTable works best from a list that:

• has one row of headings, each column with a unique name
• has one record per row, with no blank rows or columns and no subtotal rows
• keeps each column one kind of data: all dates, all numbers or all text

Formatting the list as a table (Lesson 7) is the best start. When the table grows, the PivotTable's source grows with it.

14.1.2 Inserting the PivotTable

1. Click any cell in the data.
2. Choose Insert › PivotTable (in some versions, Insert › PivotTable › From Table/Range).
3. Check the Table/Range. For a table, it's the table's name.
4. Choose where to put the PivotTable: New Worksheet, or Existing Worksheet with a starting cell.
5. Click OK.

Excel creates an empty PivotTable and opens the PivotTable Fields pane. Insert › Recommended PivotTables shows ready-made summaries instead, if you'd like a starting point.

14.1.3 The four areas

The top of the PivotTable Fields pane lists every column of the source data as a field. The bottom has four areas. Drag fields into them to build the summary:

Area	What it does	Example
Rows	Each different value becomes a row label.	Region lists North, South, East, West down the side.
Columns	Each different value becomes a column heading.	Quarter puts Q1–Q4 across the top.
Values	Numbers summarized for each row and column.	Sales summed for each region and quarter.
Filters	A drop-down above the PivotTable that filters the whole report.	Year to show one year at a time.

Checking a field's box also adds it: text and date fields go to Rows, and number fields go to Values. Drag a field out of an area, or uncheck it, to remove it. Drag fields within an area to change their order. With two fields in Rows, such as Region then Product, the second is nested under the first.

14.2 Field selections and options

Click inside a PivotTable to see two tabs: PivotTable Analyze and Design. If the field list closes, choose PivotTable Analyze › Field List to reopen it.

14.2.1 Sorting and filtering

• Sort row or column labels with the arrow on the Row Labels or Column Labels heading, or right-click a value and choose Sort › Sort Largest to Smallest to order rows by their totals.
• Filter with the same arrow: check or uncheck items, or use Label Filters (such as begins with) and Value Filters (such as Top 10 or greater than).
• Report filters in the Filters area filter the whole PivotTable. Choose an item from the drop-down above it, or check Select Multiple Items.

14.2.2 Refreshing

A PivotTable doesn't update automatically when the source data changes. Choose PivotTable Analyze › Refresh (Alt+F5), or Refresh All (Ctrl+Alt+F5) for every PivotTable in the workbook. If the source range itself has changed, for example if rows were added below a plain range, choose Change Data Source and select the new range. A source that's a table grows on its own.

14.2.3 Layout and totals

The Design tab controls the PivotTable's layout and look:

Command	Choices
Subtotals	Do Not Show Subtotals, Show All Subtotals at Bottom of Group, Show All Subtotals at Top of Group
Grand Totals	Off for Rows and Columns, On for Rows and Columns, On for Rows Only, On for Columns Only
Report Layout	Compact Form (nested fields in one column), Outline Form and Tabular Form (each field in its own column), Repeat All Item Labels
Blank Rows	Insert or remove a blank line after each item
PivotTable Style Options	Row Headers, Column Headers, Banded Rows, Banded Columns
PivotTable Styles	A gallery of styles, like table styles

14.2.4 PivotTable options

PivotTable Analyze › Options (or right-click › PivotTable Options) has more settings. The ones you're most likely to need:

• Name: the PivotTable's name, also editable in the PivotTable Name box at the left of the PivotTable Analyze tab.
• Layout & Format › For empty cells show: what to show where there's no data, such as 0.
• Layout & Format › Autofit column widths on update: uncheck it to keep your column widths when the PivotTable refreshes.
• Totals & Filters: show or hide grand totals, and allow multiple filters per field.
• Display › Show expand/collapse buttons and Classic PivotTable layout.
• Data › Refresh data when opening the file.

14.3 Value field settings

Each field in the Values area has its own settings. Click it in the Values area and choose Value Field Settings, or right-click a value in the PivotTable and choose Value Field Settings.

14.3.1 Summarize Values By

The Summarize Values By tab chooses the calculation: Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, Var and others. Numbers default to Sum and text defaults to Count. If a numeric field shows Count of Sales, the column probably contains a blank or text cell. Change it to Sum here.

Custom Name at the top changes the heading, such as Sum of Sales to Total Sales. The name can't be exactly the same as a field name, so add a space or use different words.

14.3.2 Show Values As

The Show Values As tab shows each value in relation to others instead of as a plain number:

Show Values As	Each value becomes
% of Grand Total	Its share of the overall total
% of Column Total / % of Row Total	Its share of its column's or row's total
% of Parent Row Total	Its share of the group it's nested in
Difference From / % Difference From	Its change from a base item, such as the previous month
Running Total In	A cumulative total down a field, such as month by month
Rank Largest to Smallest	Its rank among the others

Some choices need a Base field and Base item. For month-over-month change, choose % Difference From, base field Month and base item (previous).

You can also add the same field to Values twice, once as a Sum and once as % of Column Total, to see both the amount and the share.

14.3.3 Number format

Click Number Format in the Value Field Settings dialog box to format the whole field, such as Currency with no decimals. This format stays when the PivotTable is rearranged or refreshed. Formatting the cells with the Home tab doesn't always stay.

14.4 Grouping and calculated fields

14.4.1 Grouping dates

When you put a date field in Rows or Columns, Excel usually groups it automatically into Years, Quarters and Months. To change the grouping, right-click a date in the PivotTable and choose Group. In the Grouping dialog box, select one or more units in By: Seconds, Minutes, Hours, Days, Months, Quarters or Years. You can also set Starting at and Ending at. To group by weeks, choose only Days and set Number of days to 7.

14.4.2 Grouping numbers

Right-click a number in a row or column field and choose Group. Set Starting at, Ending at and By (the size of each group). For example, ages grouped starting at 20, ending at 69, by 10 give 20-29, 30-39 and so on.

14.4.3 Grouping text items

To combine items into your own groups, such as states into sales territories, select the item labels (Ctrl+click them), right-click and choose Group. Excel creates a group named Group1 and a new field with 2 added to its name (State2). Click the group label and type a new name. Repeat for the other groups.

To remove a grouping, right-click it and choose Ungroup. The Group Selection, Ungroup and Group Field commands are also on the PivotTable Analyze tab.

14.4.4 Calculated fields

A calculated field is a new field whose values come from a formula using other fields. For example, Profit is Revenue minus Cost.

1. Click in the PivotTable and choose PivotTable Analyze › Fields, Items, & Sets › Calculated Field.
2. Type a Name, such as Profit.
3. Build the Formula. Double-click fields in the list (or click Insert Field) and type operators: =Revenue-Cost.
4. Click Add, then OK.

The new field appears in the field list and is added to Values, usually as Sum of Profit.

To change or delete a calculated field, open the same dialog box, choose the field from the Name list, and click Modify or Delete.

14.5 Slicers

A slicer is a set of buttons that filters a PivotTable. It works like a filter but shows which items are selected, so readers can see and change the filter with one click.

14.5.1 Inserting slicers

Click in the PivotTable and choose PivotTable Analyze › Insert Slicer. Check one or more fields and click OK. Each field gets its own slicer.

• Click a button to show only that item.
• Ctrl+click, or turn on Multi-Select (the check-list icon at the top of the slicer), to choose several.
• Click Clear Filter (the funnel with an X) to show all items again.

For a date field, Insert Timeline adds a timeline, a slider that filters by days, months, quarters or years.

14.5.2 Formatting and connecting slicers

Select a slicer to see the Slicer tab:

Command	Use it to
Slicer Caption	Change the title at the top of the slicer
Slicer Settings	Change the caption, sort order and how items with no data appear
Report Connections	Choose which PivotTables the slicer filters. One slicer can control several PivotTables built from the same data.
Slicer Styles	Apply a color style
Columns, Height, Width	Arrange the buttons in several columns and set their size

14.6 PivotCharts

A PivotChart is a chart linked to a PivotTable. When you rearrange, filter or refresh the PivotTable, the chart changes too, and the other way around.

14.6.1 Creating a PivotChart

• From an existing PivotTable: click in it and choose PivotTable Analyze › PivotChart. Choose a chart type and click OK.
• From the data: click in the source data and choose Insert › PivotChart. Excel creates a PivotTable and a PivotChart together.

The PivotTable's Rows become the chart's categories (axis), its Columns become the series (legend), and its Values are plotted. With a PivotChart selected, the field list shows Axis (Categories) and Legend (Series) instead of Rows and Columns.

14.6.2 Changing PivotChart options

A PivotChart has the same Design and Format tabs as any chart (chapter 6), so you can change its type, layout, style, colors, titles and alt text the usual way. The PivotChart Analyze tab adds:

• Field Buttons: the gray buttons on the chart that let readers filter and sort. Use the arrow under Field Buttons to hide some or all of them (Hide All).
• Insert Slicer and Insert Timeline, which filter the chart and its PivotTable.
• Refresh and Change Data Source.
• Move Chart, to put the chart on its own sheet.

Click a field button's arrow to sort or filter right on the chart. The PivotTable filters too.

14.6.3 Applying styles

Select the PivotChart and choose a style from Design › Chart Styles, a color set from Change Colors, or a layout from Quick Layout, the same as for any chart. A PivotChart can be any standard chart type, but not the special types from chapter 13, such as histogram or waterfall.

14.6.4 Drilling down into details

When a PivotChart has more than one field on its axis, such as Category and then Product, you can show more or less detail:

• Expand or collapse a field. Click the + (Expand Entire Field) or – (Collapse Entire Field) button at the lower right of the chart, or right-click an axis label and choose Expand/Collapse. Collapsing shows only categories, and expanding shows each product within its category.
• Expand one item. Right-click a category label and choose Expand/Collapse › Expand, or Expand to a particular field.
• See the underlying records. In the PivotTable, double-click a value (or right-click it and choose Show Details). Excel creates a new worksheet listing every source row that makes up that number.

14.7 Practice