Transcript Excel Lesson 12

Excel Lesson 12
Creating PivotTables and
PivotCharts
Microsoft Office 2010
Advanced
1
Cable / Morrison
Objectives

Excel Lesson 12

Create a PivotTable.
Sort and filter PivotTable data.
Modify a PivotTable.
Change value calculations and formats.
Update data and refresh the PivotTable.

2
Cable / Morrison


Microsoft Office 2010 Advanced
Objectives (continued)

Excel Lesson 12

Create a PivotChart.
Filter a PivotChart.
Modify a PivotChart.
Add a slicer to a PivotTable.

3
Cable / Morrison

Microsoft Office 2010 Advanced
Vocabulary

Excel Lesson 12

child
parent
pivot
PivotChart
PivotTable
slicer

4
Cable / Morrison



Microsoft Office 2010 Advanced
Introduction
Excel Lesson 12



A PivotTable lets you rearrange worksheet
data so you can analyze it in a variety of
ways.
A PivotChart can be created to graphically
display the PivotTable.
The slicer includes column headings that you
click to filter data in a PivotTable.
–
5
Looks like a free-floating note pad
Cable / Morrison
Microsoft Office 2010 Advanced
Creating a PivotTable
Excel Lesson 12

6
The data in a PivotTable can be rearranged
and summarized in different ways.
–


Can be viewed from various perspectives
A PivotTable lets you pivot or rearrange the
data.
When you create a PivotTable, you do not
need to use all of the data in the worksheet.
Cable / Morrison
Microsoft Office 2010 Advanced
Creating a PivotTable (continued)
PivotTable Field List dialog box and PivotTable pane
Excel Lesson 12

7
Cable / Morrison
Microsoft Office 2010 Advanced
Sorting and Filtering PivotTable
Data
Excel Lesson 12

8
The sort and filter options are displayed
when you click:
–
–

The Column Labels down arrow or
The Row Labels down arrow
You can select a sort option to sort the data
in ascending or descending order.
Cable / Morrison
Microsoft Office 2010 Advanced
Sorting and Filtering PivotTable
Data (continued)
Sort & Filter Options list
Excel Lesson 12

9
Cable / Morrison
Microsoft Office 2010 Advanced
Modifying the PivotTable
Excel Lesson 12

10

After you create the PivotTable, you can
continue to rearrange the data.
To change the location of rows and columns:
–
–

Redisplay the PivotTable Field List dialog box.
Shift the order of the fields in the dialog box.
You can add additional levels of detail.
–
When you add levels of fields, Outline buttons
appear in the PivotTable.
Cable / Morrison
Microsoft Office 2010 Advanced
Modifying the PivotTable
(continued)
PivotTable with two levels of Row Label
detail
Excel Lesson 12

11
Cable / Morrison
Microsoft Office 2010 Advanced
Changing Formats and Value
Calculation
Excel Lesson 12

12
You can change the format of the PivotTable
data.
–

For example: display numbers as dollar amounts,
percentages, or add commas, etc.
You can calculate the data differently.
Cable / Morrison
Microsoft Office 2010 Advanced
Changing Formats and Value
Calculation (continued)

Show Values As list
Excel Lesson 12
–
13
Allows you to change how
the values are displayed
and calculated
Cable / Morrison
Microsoft Office 2010 Advanced
Changing Formats and Value
Calculation (continued)
Excel Lesson 12

14

You can create your own calculation using
any of the options that end with an ellipsis
(…)
When there is more than one level of detail:
–
–
The main row heading is referred to as the
parent.
The level of detail below this level is the child.
Cable / Morrison
Microsoft Office 2010 Advanced
Updating Data and Refreshing the
PivotTable
Excel Lesson 12

15
To update PivotTable data to reflect the
changes to the worksheet data:
–
Click the Refresh button, located in the Data
group on the Options tab.
Cable / Morrison
Microsoft Office 2010 Advanced
Updating Data and Refreshing the
PivotTable (continued)
PivotTable with
refreshed data
Excel Lesson 12

16
Cable / Morrison
Microsoft Office 2010 Advanced
Creating a PivotChart
Excel Lesson 12

17


A PivotChart is a visual representation of the
data in a PivotTable.
When you create a PivotChart with
worksheet data, a PivotTable is created at
the same time.
The PivotTable and PivotChart are linked.
Cable / Morrison
Microsoft Office 2010 Advanced
Creating a PivotChart (continued)
PivotTable and PivotChart
Excel Lesson 12

18
Cable / Morrison
Microsoft Office 2010 Advanced
Filtering the PivotChart
Excel Lesson 12


When you filter the PivotChart, the same
filtered data appears in the PivotTable.
Chart filters are located in the chart itself.
Filtered PivotChart and PivotTable
19
Cable / Morrison
Microsoft Office 2010 Advanced
Modifying the PivotChart
Excel Lesson 12

20
PivotCharts can be modified just like ordinary
Excel charts.
–

For example: you can change the chart type and
add chart styles.
When a PivotChart is selected, additional
tabs appear on the Ribbon
–
These tabs are for making changes to the
PivotChart.
Cable / Morrison
Microsoft Office 2010 Advanced
Modifying the PivotChart
(continued)
Change Chart Type dialog box
Excel Lesson 12

21
Cable / Morrison
Microsoft Office 2010 Advanced
Using the Slicer
Excel Lesson 12


The slicer is a visual
control that looks like a
note pad.
Clicking selections on the
slicer lets you filter your
data in a PivotTable.
Insert Slicers dialog box
22
Cable / Morrison
Microsoft Office 2010 Advanced
Using the Slicer (continued)
Slicer with new style
Excel Lesson 12

23
Cable / Morrison
Microsoft Office 2010 Advanced
Excel Lesson 12
Summary
24
In this lesson, you learned:
 You can create PivotTables based on
worksheet data.
 You can sort and filter PivotTable data.
 After a PivotTable is created, it can be
modified.
 You can change formats and value
calculations in a PivotTable.
Cable / Morrison
Microsoft Office 2010 Advanced
Summary (continued)
Excel Lesson 12

25


After you update worksheet data that was
used to create a PivotTable, you need to
refresh the PivotTable to display the
changes.
PivotCharts can be created with PivotTables.
PivotCharts can be filtered.
Cable / Morrison
Microsoft Office 2010 Advanced
Summary (continued)
Excel Lesson 12

26

After a PivotChart is created, it can be
modified.
Adding a slicer to a PivotTable lets you filter
data.
Cable / Morrison
Microsoft Office 2010 Advanced