- MN ABE Connect
- Archive
- Google Sheets for Data Analysis – Part 2

Google Sheets for Data Analysis – Part 2
Clark Roman, Literacy Services DirectorNote: This article summarizes the major points from Clark’s recent webinars. You can view the recordings on the MN ABE PD YouTube Channel:
- Session 1: Introductory Data Analysis (covered in Part 1 of the article)
- Session 2: Data Summarization with Pivot Tables (covered in Part 2 of the article)
You may use this form to request CEUs for viewing the recordings. As always, contact the Digital Learning Team with questions or to request support or training on other tech topics!
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!

Newsletter Signup
Get MN ABE Connect—the official source for ABE events, activities, and resources!
Sign UpArticle Categories
- ABE Foundations/Staff Onboarding
- ACES/Transitions
- Adult Career Pathways
- Assessment
- CCR Standards
- Citizenship
- COVID-19
- Cultural Competency
- Digital Literacy/Northstar
- Disabilities
- Distance Learning/Education
- ELA
- Equity/Inclusion
- ESL
- HSE/Adult Diploma
- Listening
- Math/Numeracy
- Mental Health
- Minnesota ABE
- One-Room Schoolhouse/Multilevel
- Professional Development
- Program Management
- Reading
- Remote Instruction
- Science
- Social Studies
- Speaking/Conversation
- Support Services
- Teaching Strategies
- Technology
- Uncategorized
- Volunteers/Tutors
- Writing