EC Data University – Using Pivot Tables for Data Analysis

Using Pivot Tables for Data Analysis

Are you feeling overwhelmed by all the data you have on hand? Are you looking for an easy tool to help you begin analyzing your data? In this video tutorial series, you will learn about the pivot table, a powerful tool in Microsoft Excel that lets you quickly slice and dice your data in numerous ways. You will never look at data analysis the same way again!

Notes:

  • We recommend downloading the sample data file under Resources (below) to practice using pivot table features demonstrated in the videos. For the best experience, try practicing skills learned in one video using this sample data set before moving on to the next video in the series.
  • We recommend using a computer running Microsoft Excel 2010 or later to follow the steps outlined in the video tutorials.
  • These video tutorials assume you have basic knowledge about Microsoft Excel, including familiarity with data laid out in rows and columns, and the Excel menu ribbon.

Learning Objectives

By watching this video series, users will:

  1. Learn how, when, and why to use pivot tables for data analysis
  2. Gain confidence and fluency in creating basic-to-intermediate pivot tables
  3. Learn how to prepare data for use with pivot tables
  4. Discover how to create more user-friendly, interactive pivot tables
  5. Learn how to manipulate pivot tables to help answer critical questions

Data Leadership Competencies addressed by this content

  • FD-1. Demonstrates basic data literacy skills.
  • FD-6. Is knowledgeable about data analysis methods, their strengths and limitations, and their use in developing data products to meet the needs of intended users including how to use data analysis and presentation techniques to portray data for historically underserved groups appropriately.
  • DC-2. Demonstrates the ability to analyze and use data to develop the state performance plan and annual performance report (SPP/APR).
  • DC-11. Demonstrates the ability to effectively communicate data to policymakers, OSEP, the general public, and other stakeholders through presentations, websites, reports, etc.

To get started:

Videos

  1. Overview: Benefits of pivot tables, pivot table terminology, structuring data for pivot tables
  2. Prepare and format source data
  3. Create a basic pivot table
  4. Change table calculations: Summarize values by sum, count, average, etc.
  5. Change table calculations: Show values as percent of, etc.
  6. Relabel rows and columns
  7. Sort row and column labels
  8. Show detail in cell
  9. Apply filters
  10. Add subcategories (nested rows and columns)
  11. Group and ungroup labels
  12. Use slicers for intuitive, dynamic filtering
  13. Use timelines to filter time periods
  14. Formatting and design enhancements
  15. Show rows, columns, and cells with no data
  16. Refresh a pivot table
  17. Spin off multiple pivot tables from one pivot table for multiple subgroups
  18. Create a pivot chart
  19. Create a basic dashboard
  20. Final words and where to learn more

Resources

 

Published February 2024.

Tags: , , ,