In May 2023, I led a 45-minute session at Good Tech Fest about using pivot tables to analyze a dataset from start to finish.
The conference recording wasn’t perfect — I got kicked out of the conference session midway, and again towards the end. The audio sounds funky; it sounds… mechanical? Static-y? Unfortunately the conference platform was glitchy for many, many sessions. Lots of presenters got kicked out and had to re-load and re-join. Sigh.
I was hesitant to share the recording and materials at all. But, maybe you’re brand new to pivot tables and something is better than nothing? Fingers crossed that this blog post reaches the right people!
What are Pivot Tables?!
Pivot tables are a great option for our recurring analyses, like weekly, monthly, or quarterly updates to our boss; or for annual reports to our funders.
You’ll create the pivot tables you need just once, so you can save precious time and avoid having to re-do your analyses over and over again for each report deadline.
My Goal
Let’s move you up a half-step within just 45 minutes!
What’s your current familiarity with pivot tables?
- Beginner: “I’ve never heard of pivot tables.” “I heard of pivot tables years ago, but never used them.” “Everything in spreadsheets takes forever.” “Using Excel is the least favorite part of my job.”
- Intermediate: “I used pivot tables a couple times, but I don’t have the hang of them yet.” “I need a refresher.” “I get stuck sometimes.” “I’m not sure if I’m doing it right; sometimes I’m guessing.”
- Advanced: “I use pivot tables daily.” “I could teach others to use pivot tables.”
Did you move up a half-step after participating in the session?
Comment below and let me know!
Download the Spreadsheet
This session was interactive; I demonstrated a skill, the attendees practiced, and so on.
Here’s the spreadsheet: https://depictdatastudio.gumroad.com/l/GoodTechFest
Watch the Conference Session
What You’ll Learn about Pivot Tables
Here’s what we covered during the conference session.
Step 0: Optional: Use An Excel Table
How familiar are you with Excel Tables? Comment below and let me know!
Excel Tables help to future-proof our analyses, making it easier to add new rows to the bottom of our spreadsheet (“append” the table).
Here’s how to add Excel Tables:
- Go into your dataset and click on the upper left corner.
- Go to the Insert tab.
- Click on the Table button.
- In the pop-up window, click OK.
- That’s it! You’ve got an Excel Table.
You probably recognize the banded rows?
You’re probably familiar with filters?
My favorite part of Excel Tables is that they expand easily to handle new columns and rows; i.e., our new entries will feed easily into our Pivot Tables (as long as you “refresh” the pivot table).
Step 1: Make Sure Pivot Table Prerequisites Have Been Met
There are two prerequisites:
- All columns must be labeled. If you add a new column, make sure to give the variable a name. Otherwise, you’ll get an error message later.
- We need contiguous cells. We can’t have any completely empty columns or rows. A “Swiss cheese pattern” with a few empty cells here and there is normal and fine.
Luckily, having an Excel Table makes this step unnecessary!
In other words, if you add an Excel Table, then you’ll automatically have labeled columns and contiguous cells, phew!
In the video, you’ll see me demonstrate what type of errors can arise from non-contiguous cells that aren’t stored in Excel Tables. Knowing what not to do is just as important as knowing what to do.
Step 2: Add a New Pivot Table
Let’s insert a brand new pivot table from scratch!
In the video, you’ll see me go to the dataset… go to the Insert tab… and click on the Pivot Table button.
You’ll see a pop-up window, which can be confusing for newbies. It’s just (1) verifying that your correct columns and rows are selected and (2) asking where you’d like your pivot table.
You can put your new pivot table in:
- a brand new sheet, or
- an existing sheet (like if you’d want multiple tables, linked to multiple graphs, which is great for interactive dashboards).
(In this example, I simply added a pivot table to a brand new sheet.)
Then, you’ll re-name your sheet to stay organized. Don’t leave your sheets as Sheet1, Sheet2, or Sheet3! This is a red flag. It tells me your Excel file is a disorganized disaster.
On your new sheet, you’ll see a few boxes on the right side. Here’s an overview of what they mean:
- Fields: The names of all the variables in your dataset. (The column headers.)
- Filters, Columns, Rows, and Values: The boxes where you’ll drag-and-drop your variables into.
Step 3: Drag and Drop Variables!
You’ll drag-and-drop variables to build a summary table (so you don’t have to learn formulas like countifs and sumifs).
Here are some Rules of Thumb:
- If your dataset has unique IDs, then you’ll drag those into the Values box, and you’ll change them to a Count. (You’ll see me demonstrate this in the video.) By default, Excel gives us Sums, which don’t make any sense for variables like ID numbers.
- Start your analysis by dragging one variable at a time into Rows. (In the video, you’ll see me drag Q1 into Rows.) Then, we get a summary table, like how many people selected Strongly Agree or Agree on our fictional survey.
- Pivot your table! Drag variables into Rows… and then into Columns! This is where the pivot table gets its name. You can rearrange your summary table as many times as you need. I encourage you to trust your gut instinct here!!! You’ll find summary tables that are naturally easier to read then others.
- Then, try crosstabs — where you keep one variable in Values (the ID numbers) and drag two variables into Rows and/or Columns. For example, you might look at how people responded to Q1, by cohort. This is my personal max for pivot tables: one variable in Values, and two in Rows or Columns. Once you add three, four, or five variables into Rows or Columns… it gets impossible to read.
Optional: Re-order variables within the pivot table. For example, you can sort the variables from Strongly Agree to Agree to Disagree to Strongly Disagree (instead of the default setting, which is alphabetical).
Optional: Show percentages in addition to, or instead of, your counts. In the video, you’ll learn how to Show Values As a percentage. Finally, you’ll learn how to round the percentages with the Decrease Decimal button.
Behind the scenes, Excel is calculating countifs and/or sumifs for us. I personally love love love formulas… but I love that pivot tables can bypass these formulas entirely. In the video, you’ll see me create a countifs formula.
Optional: Add quick vizzes, like Color Scales, to your pivot table. Color Scales help the patterns jump off the screen and into our brains.
Step 4: Refresh Your Pivot Table
Anytime you update your dataset… you’ll need to refresh the linkage with your Pivot Table.
You might add new columns. Or new columns. Or make tiny adjustments within the body of your dataset.
You’ll simply click on your Pivot Table, right-click, and choose Refresh. That’s it!
Phew! Right as I was describing the Refresh button, the conference platform kicked me out for a second time. I was definitely crying inside because all my hard prep work was getting flushed down the drain. Sigh.
Leave a Reply