Exploring Microsoft Excel 2016 Series Editor Mary Anne

Exploring Microsoft® Excel® 2016 Series Editor Mary Anne Poatsy Mulbery|Davidson Series Created by Dr. Robert T. Grauer

Chapter 5 Subtotals, Pivot. Tables, and Pivot. Charts Summarizing and Analyzing Data Copyright © 2017 Pearson Education, Inc.

Objectives • Subtotal Data • Group and Ungroup Data • Create a Pivot. Table • Modify a Pivot. Table • Filter and Slice a Pivot. Table Copyright © 2017 Pearson Education, Inc.

Objectives • Create a Calculated Field • Change the Pivot. Table Design • Create a Data Model • Create a Pivot. Chart Copyright © 2017 Pearson Education, Inc.

Objective 1: Subtotal Data Skills: Sort Multiple Fields Subtotal Data Add a Second Subtotal Collapse and Expand the Subtotals Copyright © 2017 Pearson Education, Inc.

Subtotal Data Specify sorted column Discipline Click Subtotal Select function Sum Select column(s) Units Sold Wholesale, Units Sold Retail, etc. Copyright © 2017 Pearson Education, Inc.

Subtotal Data Sorted by Discipline Sums calculated for Units Sold Wholesale, Units Sold Retail, etc. Sum rows added Copyright © 2017 Pearson Education, Inc.

Subtotal Data Sum rows added Copyright © 2017 Pearson Education, Inc.

Objective 2: Group and Ungroup Data Skills: Group Data Ungroup Data Copyright © 2017 Pearson Education, Inc.

Group and Ungroup Dataset grouped by columns Copyright © 2017 Pearson Education, Inc.

Objective 3: Create a Pivot. Table Skills: Create a Recommended Pivot. Table Create a Blank Pivot. Table Rename a Pivot. Table Copyright © 2017 Pearson Education, Inc.

Create a Pivot. Table • Data mining—analyzes large volumes of data • Pivot. Table—an interactive table that: §Uses calculations to consolidate and summarize data §Enables you to analyze data in a dataset §Can easily and quickly be rearranged to analyze data from different viewpoints Copyright © 2017 Pearson Education, Inc.

Create a Pivot. Table sample Point to a Pivot. Table thumbnail Copyright © 2017 Pearson Education, Inc.

Create a Pivot. Table Click Recommended Pivot. Tables Pivot. Table thumbnails Pivot. Table preview Copyright © 2017 Pearson Education, Inc.

Create a Pivot. Table Fields pane List of fields Pivot. Table Drag fields here to add to the Pivot. Table Copyright © 2017 Pearson Education, Inc.

Create a Pivot. Table Move to new worksheet Pivot. Table placeholder Copyright © 2017 Pearson Education, Inc.

Objective 4: Modify a Pivot. Table Skills: Add Rows to a Pivot. Table Add Columns to a Pivot. Table Remove Fields from a Pivot. Table Rearrange Fields in a Pivot. Table Change Value Field Settings Refresh a Pivot. Table Copyright © 2017 Pearson Education, Inc.

Modify a Pivot. Table Discipline field label Discipline first Area field labels Area second Copyright © 2017 Pearson Education, Inc.

Modify a Pivot. Table Count of book titles Sum of units sold Sum of sales Copyright © 2017 Pearson Education, Inc.

Modify a Pivot. Table One column for each copyright year, plus grand total column Copyright in COLUMNS Copyright © 2017 Pearson Education, Inc.

Modify a Pivot. Table Deselect check box Drag Area to here Drag Area from here Copyright © 2017 Pearson Education, Inc.

Modify a Pivot. Table Enter name Select function Click formatting options Copyright © 2017 Pearson Education, Inc.

Objective 5: Filter and Slice a Pivot. Table Skills: Set Filters Insert a Timeline Insert a Slicer Customize a Slicer Copyright © 2017 Pearson Education, Inc.

Filter and Slice a Pivot. Table Editions Filter by Edition Copyright © 2017 Pearson Education, Inc.

Filter and Slice a Pivot. Table Click Insert Slicer Select fields Copyright © 2017 Pearson Education, Inc.

Filter and Slice a Pivot. Table Slicer by Discipline Clear Filter Three Disciplines selected Copyright © 2017 Pearson Education, Inc.

Filter and Slice a Pivot. Table 3. 13” high Blue and light blue Two columns One column Copyright © 2017 Pearson Education, Inc.

Filter and Slice a Pivot. Table Timeline Tools Options tab Tiles to click to set filters MONTHS selected time level Copyright © 2017 Pearson Education, Inc.

Objective 6: Create a Calculated Field Skills: Create a Calculated Field Show Values as Calculations Copyright © 2017 Pearson Education, Inc.

Create a Calculated Field Type descriptive label Build formula using field names Select field names Copyright © 2017 Pearson Education, Inc.

Create a Calculated Field Sum and percentage columns Rows determined by Discipline and Area rows Sum and percentage values Copyright © 2017 Pearson Education, Inc.

Objective 7: Change the Pivot. Table Design Skills: Change the Pivot. Table Style Copyright © 2017 Pearson Education, Inc.

Change the Pivot. Table Design Pivot. Table Tools Design tab More Pivot. Table Styles gallery Copyright © 2017 Pearson Education, Inc.

Objective 8: Create a Data Model Skills: Create Relationships Create a Pivot. Table from Related Tables Copyright © 2017 Pearson Education, Inc.

Create a Data Model REPS table Click Relationships SALES table Primary table SALES Related columns Related table REPS Copyright © 2017 Pearson Education, Inc.

Create a Data Model Click Pivot. Table Click in primary table Primary table is displayed Select check box Copyright © 2017 Pearson Education, Inc.

Create a Data Model REPS table and fields SALES table and fields Dates from SALES table Sales Reps from REPS table Copyright © 2017 Pearson Education, Inc.

Objective 9: Create a Pivot. Chart Skills: Create a Pivot. Chart Modify the Pivot. Chart Copyright © 2017 Pearson Education, Inc.

Create a Pivot. Chart COLUMNS area is now LEGEND (SERIES) Pivot. Chart Filter by Date ROWS area is now AXIS (CATEGORY) Filter by Sales Rep Copyright © 2017 Pearson Education, Inc.

Summary • SUBTOTAL function: § SUM § AVERAGE § COUNTA § MAX § MIN • Pivot. Table: §Consolidates and summarizes data §Enables data analysis in a dataset §Rearranges data for analysis from different viewpoints • Pivot. Chart—interactive graphical representation of Pivot. Table data Copyright © 2017 Pearson Education, Inc.

Questions ? Copyright © 2017 Pearson Education, Inc.

Copyright All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America. Copyright © 2017 Pearson Education, Inc.