Pivot Tables and Pivot Charts to the Rescue
Pivot Tables (and Pivot Charts) to the Rescue! Dereck Norville, MSSW Hostos Community College – City University of New York DC Data Summit July 7 th, 2014
• Overview of the sections of the training workshop: • Part 1 - Pivot Table Overview • Part 2 - Pivot Chart Overview • Part 3 - Question-based Approach to Analysis via Pivot Tables (extracting the significance) • Excel Training – Part 1 • Excel Training – Part 2 • Excel Training – Part 3 Training Workshop Agenda
• By the end of the workshop, participants will … • Better understand the utility of using Pivot Tables to extract significant findings from their datasets. • Be able to construct Pivot Tables and Pivot Charts. • Be able to create a simple interactive dashboard using slicers in Pivot Charts. • Be able to apply a research methodology to data analysis activities. Learning Objectives
• Topics covered in this section may include: • Definition of a pivot table • Overview of the components of a pivot table (e. g. fields, column labels, row labels, values, report filters, and slicers) • Rearranging fields in a pivot table • Changing data summary calculations • Displaying values as a percentage • Renaming headings in the table • Introducing filters and slicers • Drill-down functionality Part 1 – Pivot Table Overview
• Topics covered in this section may include: • • • Multiple ways to create a pivot chart Altering chart types, formats, and layouts Filtering Pivot Charts Hiding Pivot Chart elements Using “slicers” to create dashboards from Pivot Charts Part 2 – Pivot Chart Overview
• Topics covered in this section may include: • Determining questions to answer (based on the pre-assigned dataset); • Determining the variables from the dataset that will be needed to create the pivot table; • Creating the appropriate pivot table to determine the answer; and • Deciding on an appropriate visualization via Pivot Charts. Part 3 - Question-based Approach to Analysis
• Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set. Its primary purpose is to summarize data and reveal patterns and trends. One of the best features is its drill-down capabilities. • There are several components to a pivot table: • Fields – This is where the list of variables that you selected will be housed for you to use in your tables. • Column Labels – dragging a field into this box will set the values in that field as your column labels. • Row Labels - dragging a field into this box will set the values in that field as your row labels. • Values – Whatever field(s) you drag into this box will populate the values (counts, averages, sum, max, min, etc. ) for that field (variable). • Report Filter – whatever field(s) you drag into this box allows you to filter your table results by the values in that field (variable) • Slicers – Allows you to “slice” your data by any criteria found in one of your variables (e. g. this function would allow me to slice overall workshop participation by race – Race being the slicer). Intro to Pivot Tables
- Slides: 7