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:
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
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.
Step Two: Drag & Drop Variables into Correct Box
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:
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.
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:
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:
2. Click Add Value -> Choose the second value you want to display:
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.
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.
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:
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:
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.