Google Sheets for Data Analysis – Part 2

Part 1 on Google Sheets

Check out Part 1 of this article for some background on Google Sheets, as well as information on using filters and functions. And if you want to follow along with what I’m doing in this article, you can make a copy of the spreadsheet I’m working from. This is a selection of student demographic data downloaded from SID. (Don’t worry, I ran a program to replace identifying information with fake names, ages, and birthdates.)

Pivot Tables

After you’ve gotten comfortable with functions, then the next powerful tool to learn is Pivot Tables. Pivot tables allow you to summarize and categorize large groups of data by grouping like categories together. You can then see averages, totals, or other values based on those categories (and without the hassle of manually calculating them!) A pivot table can take a table like the one below and turn it into a table with the sum of each category.

From standard table:

To pivot table:

Here’s a simplified set of data to use to practice – after you’ve gotten the hang of pivot tables using the simpler data, go back to student demographic data above and play with it, too!

To utilize a pivot table, go to Menu>Insert>Pivot Table

You’ll then be given the option to select your data and choose where you want your pivot table to populate to.

After that, the real fun begins. You then get to choose what you’re categorizing and how you want your data to appear.

• Rows – Defines how data is grouped (e.g., group by salesperson). These are normally populated from column headers.
• Columns – Adds additional breakdowns (e.g., group by salesperson and product).
• Values – Defines calculations (e.g., sum of units sold).
• Filters – Allows filtering data (e.g., show only a specific product).

A practice question based on this dataset: Create a pivot table showing total sales revenue by region. Below is what your pivot table editor should look like to create that table.

Play around with the data, choosing different categories to aggregate on, and once you feel a little bit confident, come back to the student demographic data.

Practicing with pivot tables

Here’s a couple of practice questions, and the pivot table output. You can’t harm this data; it’s all fake, so feel free to try as much as you want until you get the hang of it.

Question 1: Create a pivot table that shows student total hours broken down by gender.

Question 2: Next, try to get all of the learners’ primary languages and the number of learners who reported each language.

(Hint: I used “Last Name” for the Values because in this fake data set, SID ID numbers were removed and all of the fake last names were unique. If I were analyzing data from a real SID report, I would use learners’ SID ID numbers instead.)

Question 3: Finally, try to find the average number of total hours by student primary language.

Remember: you can watch the webinar recording, Session 2: Data Summarization with Pivot Tables, if you’d like to see pivot tables in action!