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. |
|
|
| Create and populate PivotTables. |
|
|
| Refresh PivotTable data. |
|
|
| Change PivotTable value field settings such as summarization and formatting. |
|
|
| Create calculated fields in a PivotTable. |
|
|
| Add and use PivotTable filters. |
|
|
| Create and use PivotTable slicers. |
|
|
| Create and format PivotCharts to illustrate PivotTable data. |
|
|
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
References
- 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.
- 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/.
- 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.