1 Summarizing Excel data with Pivot Tables
If you’ve ever been presented with an Excel spreadsheet with a gosh-awful number of rows and/or columns in it and assigned the task of making sense of all those numbers (grouping, summarizing, or making other calculations), you need to learn about Pivot Tables.
Okay, people, I hear yawning out there! Seriously, this is a good skill to have in your back pocket, even if you only work with Excel occasionally, because it saves so much time. So to motivate you properly, here’s a fun little YouTube introduction to the whys behind Pivot Tables:
Basically, a Pivot Table is a way to summarize columns and rows in a meaningful way. Instead of your having to manipulate rows and rows of data by hand (which, depending on the size of the spreadsheet, could take hours), you can select the data to be summarized, go to the Insert tab, click Pivot Table, and tell Excel how you want those rows summarized.
For example, say your client is involved in an employment discrimination suit. The employer has produced a very large spreadsheet showing all the time entries recorded, including this information:
- Timekeeper initials
- Date worked
- Hours worked
- Work code
- Description of work performed
If you’re being asked to figure out how many hours timekeeper CAL (the plaintiff) clocked in Word Code 01 during the month of June, you don’t want to have to manually add those hours. Sure, you could sort the rows and put in a summary row, but even that’s not necessary with Pivot Tables.
A pivot table allows you to take a spreadsheet that looks like this (times several hundred or thousand rows);
And turn it into a summary table that looks like this:
And it just takes a few mouse clicks. Let Excel do all the work for you!
Here’s a video demonstration:
[To view this full-screen, click the button in the lower right-hand corner]
Where could you put this trick to use?