Leave a Reply

Your email address will not be published. Required fields are marked *

How to Analyze Data with Excel’s Pivot Tables (No Formulas Required!)

Updated on: Jun 5th, 2023
Data Analysis
, , , ,
Ann K. Emery is sitting at a laptop and teaching you about Microsoft Excel's pivot tables.

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.

More about Ann K. Emery
Ann K. Emery is a sought-after speaker who is determined to get your data out of spreadsheets and into stakeholders’ hands. Each year, she leads more than 100 workshops, webinars, and keynotes for thousands of people around the globe. Her design consultancy also overhauls graphs, publications, and slideshows with the goal of making technical information easier to understand for non-technical audiences.

Leave a Reply

Your email address will not be published. Required fields are marked *

You Might Like

Our complimentary mini course for beginners to dataviz. Takes 45 minutes to complete.

Enroll

How to Make Dumbbell Dot Plots in Excel

These non-native charts require some advanced Excel maneuvers. You’ll learn how to assign each dot an x-y coordinate… and then add a scatter plot (!).

More »

Need some time-savers in Excel? Feel like all the formulas are taking forever? Learn how to clean, analyze, and tabulate your dataset.

Enroll

Subscribe

Not another fluffy newsletter. Get actionable tips, videos and strategies from Ann in your inbox.