10 Minutes

What is a Pivot Table & How to Create it? (Excel & Google Sheets)

Pivot tables are the holy grail of spreadsheet analysis techniques. They allow a complete beginner with no data background to quickly and easily analyze their data to draw important conclusions from them. But what are they and how do they work?

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 seen in spreadsheet applications like Microsoft Excel and Google Sheets.

Here’s an analogy to better explain what a pivot table does:

Imagine you have a jar of lollies:

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 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.

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. 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. How do you know which box to put each variable in?

Categorical Variables (like gender, country, city, category) should go into “columns” or “rows.” 

Pivot tables: rows or columns?

If you’re only dealing with one variable, it doesn’t matter which one - it comes down to personal preference. For multiple variables, there is no hard rule. Just experiment to see what works best. 

The best way to learn is through experimentation. 

Numerical variables (like age, height, cost, unit price, profit) should go into “values”

Any variables you want to filter can be put into the “filters” box e.g. if you only want to filter in/out sales from a certain city.

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.”

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 practising 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 that'll make your data analysis 10x easier.

Posted on
November 25, 2022
under Blog
November 25, 2022
Written by
Ash Gupta
Former Tech Lead for Machine Learning at Google AdWords (6 years) and a quant developer on Wall Street. Co-Founder & CEO of Polymer Search.

Latest Stories

Start using Polymer right now. Free for 14 days.

See for yourself how fast and easy it is to create visualizations, build dashboards, and unmask valuable insights in your data.

Start for free