by Mary Anne Poatsy Keith Mulbery Eric Cameron Jason Davidson Rebecca Lawson Linda Lau Jerri Williams Exploring Microsoft Office 2013 Volume 2 Chapter 5 Subtotals PivotTables and PivotCharts ID: 415030
Download Presentation The PPT/PDF document "1 Copyright © 2014 Pearson Education, I..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Microsoft® Excel® 2016
Exploring
Series Editor Mary Anne Poatsy
Mulbery|Davidson
Series Created by Dr. Robert T. GrauerSlide2
Chapter 5Subtotals, PivotTables, and PivotChartsSummarizing and Analyzing Data
Copyright © 2017 Pearson Education, Inc.Slide3
Subtotal DataGroup and Ungroup DataCreate a PivotTableModify a PivotTableFilter and Slice a PivotTableObjectivesCopyright © 2017 Pearson Education, Inc.Slide4
Create a Calculated FieldChange the PivotTable DesignCreate a Data ModelCreate a PivotChartObjectivesCopyright © 2017 Pearson Education, Inc.Slide5
Objective 1: Subtotal DataCopyright © 2017 Pearson Education, Inc.Skills: Sort Multiple Fields
Subtotal Data Add a Second Subtotal Collapse and Expand the SubtotalsSlide6
Subtotal DataCopyright © 2017 Pearson Education, Inc.
Click Subtotal
Select column(s)Units Sold Wholesale, Units Sold Retail
, etc.
Select function
Sum
Specify sorted column
DisciplineSlide7
Subtotal DataCopyright © 2017 Pearson Education, Inc.
Sums calculated forUnits Sold Wholesale, Units Sold Retail, etc.
Sum rows
addedSorted byDisciplineSlide8
Subtotal DataCopyright © 2017 Pearson Education, Inc.
Sum rowsaddedSlide9
Objective 2: Group and Ungroup DataCopyright © 2017 Pearson Education, Inc.Skills: Group Data Ungroup DataSlide10
Group and Ungroup DataCopyright © 2017 Pearson Education, Inc.
Group and Ungroup
Dataset grouped by columnsSlide11
Objective 3: Create a PivotTableCopyright © 2017 Pearson Education, Inc.Skills: Create a Recommended PivotTable
Create a Blank PivotTable Rename a PivotTableSlide12
Create a PivotTableCopyright © 2017 Pearson Education, Inc.Data mining—analyzes large volumes of dataPivotTable—an interactive table that:Uses calculations to consolidate and summarize dataEnables you to analyze data in a
datasetCan easily and quickly be rearranged to analyze data from different viewpointsSlide13
Create a PivotTableCopyright © 2017 Pearson Education, Inc.
Point to a PivotTable thumbnail
PivotTablesampleSlide14
Create a PivotTableCopyright © 2017 Pearson Education, Inc.
Click Recommended PivotTables
PivotTablethumbnails
PivotTable previewSlide15
Create a PivotTableCopyright © 2017 Pearson Education, Inc.
PivotTable
PivotTable Fields pane
List of fields
Drag fields here to add to the PivotTableSlide16
Create a PivotTableCopyright © 2017 Pearson Education, Inc.
PivotTable placeholder
Move to new worksheetSlide17
Objective 4: Modify a PivotTableCopyright © 2017 Pearson Education, Inc.Skills: Add Rows to a PivotTable
Add Columns to a PivotTable Remove Fields from a PivotTable Rearrange Fields in a PivotTable
Change Value Field Settings Refresh a PivotTableSlide18
Modify a PivotTableCopyright © 2017 Pearson Education, Inc.
Discipline field label
Area field labels
Discipline
first
Area
secondSlide19
Modify a PivotTableCopyright © 2017 Pearson Education, Inc.
Count of book titles
Sum of units sold
Sum of salesSlide20
Modify a PivotTableCopyright © 2017 Pearson Education, Inc.
One column for each copyrightyear, plus grand total column
Copyright in COLUMNSSlide21
Modify a PivotTableCopyright © 2017 Pearson Education, Inc.
Deselect check box
Drag Area from here
Drag Area
to hereSlide22
Modify a PivotTableCopyright © 2017 Pearson Education, Inc.
Enter name
Select function
Click for formatting optionsSlide23
Objective 5: Filter and Slice a PivotTableCopyright © 2017 Pearson Education, Inc.Skills: Set Filters
Insert a Timeline Insert a Slicer Customize a SlicerSlide24
Filter and Slice a PivotTableCopyright © 2017 Pearson Education, Inc.
Editions
Filter by EditionSlide25
Filter and Slice a PivotTableCopyright © 2017 Pearson Education, Inc.
Click Insert Slicer
Select fieldsSlide26
Filter and Slice a PivotTableCopyright © 2017 Pearson Education, Inc.
Slicer by Discipline
Three Disciplinesselected
Clear FilterSlide27
Filter and Slice a PivotTableCopyright © 2017 Pearson Education, Inc.
Blue and light blue
Two columns
One column
3.13”
highSlide28
Filter and Slice a PivotTableCopyright © 2017 Pearson Education, Inc.
PivotTable TimelineTiles to click to set filters
MONTHS selected time level
Timeline Tools Options tabSlide29
Objective 6: Create a Calculated FieldCopyright © 2017 Pearson Education, Inc.Skills: Create a Calculated Field
Show Values as CalculationsSlide30
Create a Calculated FieldCopyright © 2017 Pearson Education, Inc.
Type descriptive label
Build formula using field names
Select field namesSlide31
Create a Calculated FieldCopyright © 2017 Pearson Education, Inc.
Rows determined by Discipline and Area
Sum and percentage values
Sum and percentage columns
Discipline and Area rowsSlide32
Objective 7: Change the PivotTable DesignCopyright © 2017 Pearson Education, Inc.Skills: Change the PivotTable StyleSlide33
Change the PivotTable DesignCopyright © 2017 Pearson Education, Inc.
PivotTable Tools Design tab
More
PivotTable Styles gallerySlide34
Objective 8: Create a Data ModelCopyright © 2017 Pearson Education, Inc.Skills: Create Relationships
Create a PivotTable from Related TablesSlide35
Create a Data ModelCopyright © 2017 Pearson Education, Inc.
Primary table SALES
REPS table
Click Relationships
SALES table
Related table REPS
Related columnsSlide36
Create a Data ModelCopyright © 2017 Pearson Education, Inc.
Click PivotTable
Click in primary table
Primary table is displayed
Select check boxSlide37
Create a Data ModelCopyright © 2017 Pearson Education, Inc.
REPS table and fields
SALES table and fields
Dates from SALES table
Sales Reps from REPS tableSlide38
Objective 9: Create a PivotChartCopyright © 2017 Pearson Education, Inc.Skills: Create a PivotChart
Modify the PivotChartSlide39
Create a PivotChartCopyright © 2017 Pearson Education, Inc.
Filter by Date
Filter by Sales Rep
PivotChart
COLUMNS area is now
LEGEND (SERIES)
ROWS area
is now AXIS
(CATEGORY)Slide40
SUBTOTAL function:SUMAVERAGECOUNTPivotTable:Consolidates and summarizes dataEnables data analysis in a datasetRearranges data for analysis from different viewpointsPivotChart—interactive graphical
representation of PivotTable dataSummary
Copyright © 2017 Pearson Education, Inc.
COUNTAMAXMINSlide41
?Questions
Copyright © 2017 Pearson Education, Inc.Slide42
CopyrightCopyright © 2017 Pearson Education, Inc.