Skip to main content

Excel Chapter 5

Chapter Demonstrable Skills

After completing the Excel Chapter 5 portion of the Excel & Data Analysis unit, students will be able to:

Demonstrable Skill Learning Activities
& Informal Assessments
Formal Assessments
(excluding Exams)
Utilize the subtotal option to group data.
  • MyLab Lesson B
  • MyLab Lesson B
Create and populate PivotTables.
  • Excel: PivotTables Participation Project
  • MyLab Lesson B
  • Homework #3
  • Homework #6
  • MyLab Lesson B
  • MyLab Bonus Project #1
Refresh PivotTable data.
  • Excel: PivotTables Participation Project
  • MyLab Lesson B
  • MyLab Lesson B
Change PivotTable value field settings such as summarization and formatting.
  • Excel: PivotTables Participation Project
  • MyLab Lesson B
  • Homework #3
  • Homework #6
  • MyLab Lesson B
  • MyLab Bonus Project #1
Create calculated fields in a PivotTable.
  • MyLab Lesson B
  • MyLab Lesson B
Add and use PivotTable filters.
  • Excel: PivotTables Participation Project
  • MyLab Lesson B
  • Homework #3
  • MyLab Lesson B
Create and use PivotTable slicers.
  • MyLab Lesson B
  • MyLab Lesson B
Create and format PivotCharts to illustrate PivotTable data.
  • Excel: PivotTables Participation Project
  • MyLab Lesson B
  • MyLab Lesson B
  • MyLab Bonus Project #1

Chapter Notes

PivotTables

When working with large datasets, it may be difficult to gather information from the large volume of data. However, there are Excel features that help consolidate and analyze large amounts of data.

Grouping and Subtotaling data presents summary information that helps to analyze the data.  PivotTables allow you to take a huge amount of data and summarize it very quickly [1]. It allows you to make calculations with criteria with ease. A basic requirement of a PivotTable is to have some amount of common or repeating data. You use that data to categorize (organize) the data and then perform summary functions on it.  

For example, if you need to know what the average amount earned by Administration, Faculty, and Staff are in a school system, you could pivot the data very easily to show what the average amount earned for each classification was. Attempting to draw this information out of an Excel table could be time consuming.  Pivoting it is quick and easy.

Tips & Tricks

Grouping PivotTable data

People often overlook grouping. It's simple to group values. Follow these steps [2]:

  • Simply click a cell in the column you want to group, then right-click and choose Group.
  • If you click more than one cell, an entirely different option comes up - clicking a single cell will allow Excel to do the grouping for you.

Suggested Resources

PivotTables

58 Examples Video

This video is also available on YouTube [3].

References

  1. M. A. Poatsy, K. Mulberry, C. Krebs, L. Hogan, A. Rutledge, and E. Cameron, Exploring Microsoft Office 2013, 1st ed., vol. 2. Upper Saddle River, NJ: Pearson Education, 2013.
  2. R. G. Phipps and B. M. Powell, “Excel Chapter 5,” Computer Science 101, May 18, 2016. Available: http://cs101.wvu.edu/materials/units/excel-data-analysis/excel-chapter-5/.
  3. M. Girvin, Highline Excel 2013 Class Video #35: Excel 2013 PivotTables: Basic To Advanced 58 Examples. 2013. Available: https://www.youtube.com/watch?v=e-yuYNgsHAk.