10 Minutes

What is a Pivot Table? 2024 Guide (Excel & Google) · Polymer

Pivot tables are the holy grail of spreadsheet analysis techniques. They allow a complete beginner with zero data experience to quickly analyze their data. But what are pivot tables and how do they work?

Pivot Tables: Step-by-Step Tutorial, Examples, & Tips for 2024

What is a Pivot Table?

In simple words, a pivot table is a data analysis technique used for summarizing large datasets and answering questions you may have about the data. It is available in spreadsheet applications like Microsoft Excel, Google Sheets and Polymer (interactive pivot tables). It's a very powerful way to organize your data.

Here’s an analogy to better explain what a pivot table does and how they work in 2024.:

Imagine you have a jar of lollipops:

jar of lollies pivot table example

And you want to figure out: How many red lollies are there? How many circle lollies are there? How many red square lollies are there? 

One way to do that is to manually count each one. This can be time consuming for larger datasets. 

The best way to get the answer is by creating a pivot table. 

Pivot tables are a way of restructuring and summarizing complex datasets into a table that allows us to easily find patterns or solutions to any questions we might have about the dataset. In a way, you're grouping together different variables in the dataset. This is also known as aggregating data. 

There are several ways to group these lollies: 

  • You can group them by color 
  • You can group them by shape 
  • You can group them by shape & color
pivot table examples

In essence, this is what a pivot table does. It groups your data together and allows you to perform calculations such as COUNT & SUM on the data.

What are Pivot Tables Used for?

Pivot tables are used for summarizing and rearranging large amounts of data into an easier-to-understand table that allows us to draw important business conclusions. 

Real life use-cases/examples of pivot tables are:

  • Summarizing annual business expenses
  • Showing the average spending power of different customer demographics
  • Showing the distribution of marketing spend across multiple channels

Pivot tables utilize SUM and AVERAGE, among other functions, to quickly get the answer to these questions.

Why Use a Pivot Table?

When confronted with massive datasets, it's easy to feel overwhelmed. That's where pivot tables come into play. Pivot tables are not just a tool; they are an essential asset in any data analyst's arsenal. Let's dive into why you should consider using them:

  1. Simplified Data Analysis: Asking "What is a pivot table?" is akin to asking "How can I make sense of my data easily?" Pivot tables allow you to distill vast amounts of data into digestible chunks, facilitating better decision-making.
  2. Quick Insights: Instead of sifting through rows upon rows of data, pivot tables provide immediate insights by showcasing data summaries. This rapid insight can be invaluable for business decisions.
  3. Versatility: Pivot tables can be used across various industries and for numerous purposes, from finance to sales to academic research. Their flexibility means that regardless of your field, they can be of immense help.
  4. Data Comparison: Want to compare sales data from two different quarters? Or perhaps you're keen to understand the growth rate over the past five years? Pivot tables make these comparisons straightforward.
  5. No Advanced Skills Required: As highlighted in the introduction, even complete beginners can harness the power of pivot tables. There's no need for advanced data analysis skills or knowledge of complex formulas.

The Evolution of Pivot Tables: Modern Platforms

Pivot tables have come a long way since their introduction. While many associate the term "pivot table" with Microsoft Excel, today's landscape offers a plethora of platforms that have integrated and enhanced this powerful feature. With the advent of AI tools like ChatGPT, it's becoming easier and more important to supplement pivot tables with AI data analysis.

  1. Polymer (Interactive Pivot Tables): An advancement in the world of pivot tables. Polymer not only allows users to create pivot tables but also offers interactive features, making data analysis even more intuitive.
  2. Microsoft Excel: The OG of pivot tables. Excel provided users with the ability to create pivot tables from lists or databases, making data analysis accessible to millions.
  3. Google Sheets: Google's foray into the spreadsheet world came with its version of pivot tables. While similar to Excel, Google Sheets offers collaboration features that have made it a favorite for many.
  4. Integrated BI Tools: With the rise of Business Intelligence (BI) tools like Tableau, Power BI, and QlikView, pivot tables have found a new home. These platforms take the basic functionality of pivot tables and elevate it, offering advanced visualization and analytics capabilities.

How to Create Pivot Tables in Excel

If you’re using Google Sheets, go to the next section.

Step One: Insert Pivot Table

Select the data you want to analyze in Excel. To select everything, click the triangle at the top left or press CTRL + A.

At the top, click Insert -> Pivot Table.

create a pivot table in Excel

Step Two: Drag & Drop Variables into Correct Box

customizing a pivot table

There are 4 boxes: filters, columns, rows and values. Here you can re-arrange the different variables to give you different outputs.

How you arrange them depends on what questions you want answered.

For further detail, scroll down below and read "Rows, Columns, Values and Filters: Which One to Use?"

Step 3: Set the Calculation

In the “values” box, after dragging a variable in there, you can select the calculation you want to apply. The most common ones are SUM and AVERAGE.

Since here we want to get the total of all sales, we’ll choose SUM.

Once the pivot table is created, you can sort the data from highest to lowest by right-clicking on the table -> sort -> sort largest to smallest.

How to Create Pivot Tables in Google Sheets

Creating a pivot table in Google Sheets is very similar to Excel.

Step One: Insert the Pivot Table

Start by opening your spreadsheet on Google Sheets and selecting all the data. 

You can quickly select all the data by clicking the top left corner of the spreadsheet or by pressing CTRL + A.

Go to Insert -> Pivot Table:

create a pivot table in Google Sheets

Step Two: Choose Where to Create the Pivot Table

You can create the pivot table in a new sheet or the existing sheet. It’s generally easiest to put it into a new sheet, but this is up to personal preference. 

Create a pivot table New Sheet

Step Three: Customize the Pivot Table

There are two ways to customize a pivot table in Google Sheets:

1. Using the AI suggested insights

2. Using your own input (recommended)

You can do both of these is using on the right hand side of the pivot table you just created:

Pivot table editor

Click “Add” to create your own personalized pivot table. Similar to Excel, you can manually add variables into “rows, columns, values and filters.”

Rows, Columns, Values and Filters: Which One to Use?

Now you have a pivot table set up, how do you know which box to put each variable in? Rows, columns, values or filters?

Here's how to use each one:

  • Categorical Variables (like gender, country, city, category) should go into “columns” or “rows.” 
  • Numerical variables (like age, height, cost, unit price, profit) should go into “values”
  • Anytime you want to filter for a specific result, you can put the variable into the "filters" box. For example - if I only want to see sales from a Canada.

Rows or columns?

If you’re only dealing with one categorical variable, it doesn’t matter which one you use. Both will be easy to read.

But when we want to look at 2 things at once - say income generated from "user country" and "gender," then you'll have to mix and match and see which one works best. Try putting one into rows, and one into columns and see if you like the resulting pivot table.

There is no hard rule when deciding where to put each variable. Put it in a way where it's easy to read the data.

Types of Pivot Tables

Here are some advanced pivot table techniques:

1. Pivot Tables with Multiple Columns

Pivot tables with multiple columns allow you to compare multiple values for example, if you want to see total ad spend vs. return on ad spend.

There are two ways to do this:

  • Using Polymer Search
  • Using Google Sheets

Here’s how to do it on Polymer:

1. Go to the insights tab -> New Block -> Pivot Table:

Pivot Table in Polymer

2. Click Add Value -> Choose the second value you want to display:

Create a nice looking pivot table with multiple columns

Here we choose “amount spentd” and “purchases.” 

3. Set up the rest of the pivot table as normal, choosing the appropriate rows and columns.

The benefits of doing this on Polymer is the data is interactive and presentable.

You can also do something similar on Google Sheets using the pivot table editor.

Pivot table editor

Go to the pivot table editor, and click the Add button next to Rows. Then locate the row you want to show and click on them. Repeat the same process to insert a Column to start seeing your pivot table take shape. You can also select the right Filters and Values to display multiple columns according to your needs.

2. Pivot Tables with Slicers

Google sheets also allows you to add pivot table filters with slicers. Slicers are novel, visual filters that show you the items you’ve chosen in your table. To add a slicer, open your pivot table. Go to the navbar and under Data select Add a slicer.

Add a slicer to a pivot table

A slicer menu will appear at the right of the window. Here, you can select a column to filter by. Then, simply click the slicer and choose the fields you want to filter by.

Pivot Tables with Interactive Filters

Similar to slicers, you can also set up a pivot table with interactive filters like this:

pivot table with filters
filter pivot table data

By clicking on the interactive tags, you can filter the data in any way. Common ways to filter are by date, product type and demographics.

Another technique is to filter OUT data:

filter out data

Filtering out data is often used for 3 various purposes: 

1. Removing outliers

2. Data exploration

3. Showing how the data would look if specific data points were removed

To set up an interactive pivot table, Polymer Search is a tool that allows you to do this with zero coding.

How to Refresh Pivot Tables

If you’re using Excel, you’ll need to manually refresh your pivot tables

If you’re using Google Sheets, they are updated automatically. However, if they aren’t refreshing, there might be a few reasons for this:

1. The Pivot Table Uses Filters

If you have filters added for your pivot tables, they won’t automatically update.

rows, columns, values, filters

To remedy this, you’ll need to remove all filters from your pivot tables by clicking the ‘x’ next to them.

You can add them again afterwards.

2. Newly Added Data is Outside the Selected Range

When you first create a pivot table, you have to select the data you want to analyze.

When new rows are added, they might fall outside of the pivot table’s range.

You can manually edit the pivot table’s range by using the pivot table editor and clicking the “select data range” symbol:

editor

3. Your Data Uses Dynamic Functions

Dynamic functions cause your data to change automatically. Examples are RANDOM and TODAY functions.

If your dataset includes these functions, then it will cause the pivot table not to update automatically. The only way to fix this is to recreate the table or remove these functions entirely.

Conclusion

Pivot tables aren't as complicated as they sound. Experiment with them and you'll get the hang of it in minutes.

I recommend practicing with an example dataset. Polymer Search has multiple example datasets you can use.

Try Polymer Search for free to get access to interactive pivot tables and dashboards that'll make your data analysis 10x easier.

Posted on
November 29, 2023
under Blog
November 29, 2023
Written by
Rand Owens
Founding team member at Motive (Formerly KeepTruckin) and passionate about all things Marketing, RevOps, and Go-To-Market. VP of Marketing @ Polymer Search.

Explore Our E-commerce Business Intelligence Templates

Browse All Templates

Related Articles

Browse All Templates

Start using Polymer right now. Free for 14 days.

See for yourself how fast and easy it is to uncover profitable insights hidden in your data. Get started today, free for 14 days.

Try Polymer For Free