5 minutes

What is a pivot table & how do I create one? (interactive tutorial)

Pivot tables are a very powerful tool for analyzing data. Learn how to create pivot tables in under 5 minutes with this interactive tutorial!

What are pivot tables? And why are they so useful?

Pivot tables are a simple, yet powerful tool for analyzing data. It takes a complicated dataset with many variables, rows & columns, and re-groups them into a table that's easier to read, allowing you to draw conclusions.

Pivot table example:

Let's say we have a dataset of grocery sales across cities:

Sample dataset

You can transform that dataset into a pivot table that looks like this:

pivot table example

How to make pivot tables:

You can make a pivot table in a few seconds! Here's how to do it:

1) Come up with a question about the data (or a hypothesis) e.g. "Which cities are producing the most sales?"

2) Identify the key variables in the question i.e. Which cities are producing the most sales? Key variables are:

  • City
  • Quantity (of sales)

3) Input those variables into 'smart pivot.'

Try it yourself!

  • Select 'city' in the 'columns' dropdown
  • Select 'quantity (SUM)' in the 'metrics' dropdown:

In a matter of seconds, you've just created your pivot table!

If you did it correctly, it should look like this:

pivot table example

Boston produces the most sales, followed by New York.

Easy?

Now try answering these questions:

  • What is the total price of every item sold in each city?
  • What are the top selling products in each city?

Solutions:

pivot table example 2

Boston sold $13,265 worth of groceries, NY sold $8,258, LA sold $7,687 and San Diego sold $4,113.

pivot table example 3

Carrots are the top selling product in Boston and LA.

Want to give this a try with your own dataset?

  1. Sign up to Polymer here (it's free)
  2. Upload your dataset.
  3. Click 'open Polymer app'

Polymer will encrypt and analyze your data using intelligent AI. Then it'll create a web application like the above for you to play around with.

Polyer is a very powerful tool for analyzing your data: it has a pivot table feature, data visualization features and the 'auto-insights' feature which uses AI to generate insights on your data.

SUM, AVERAGE, COUNT explained

First of, notice how there are multiple entries for the city 'Boston:'

SUM Pivot Tables
(If there were only 1 entry for each city, then 'SUM' and 'AVERAGE' would yield the same result).

Quantity (SUM), adds up the quantity. This can be useful for seeing how many sales each city is producing overall.

But maybe Boston has the most sales because there are more shops set up in Boston. Maybe there are 30 shops in Boston, whilst there are only 15 in New York. In this case, we want to use "Quantity (AVERAGE)" to see how each shop is performing on average.

COUNT tells us how many 'Bostons' there are.

pivot table example 4

Here we choose 'Quantity (AVERAGE).' Looking at this pivot table, it's evident that despite Boston having the most sales, it actually performs worse than Los Angeles and New York in terms of average quantity of sales. This is because there are more instances of Boston (the #COUNT# column will testify to that).

FAQ

How do I create pivot tables in Excel?

Pivot tables are more time consuming and complicated to create in Excel, but I covered that tutorial in another blog post.

How do I create pivot tables in Google Sheets?

It's pretty much the same as Excel with a few minor differences. We'll cover that in a later tutorial.

How to use pivot tables?

Pivot tables are best used when you have a question or hypothesis about the data. Once you have a hypothesis/question, all you need to do is identify the key variables (as we did above) and input them into the smart pivot fields and you'll get your question answered.

If you don't have an initial hypothesis or question, you'll need to do some data exploration. Polymer's auto-insights feature allows you to deep dive into your data to find trends, correlations and patterns. Read more about how to analyze data here.

Does Polymer require any downloads?

Nope, Polymer is entirely a web-based tool!

What are the advantages of Polymer over Excel?

Excel is universally regarded as one of the best tools for storing and editing data, but it's also one of the worst tools for analyzing data. This is where Polymer comes in.

Polymer allows you to analyze & visualize your data without technical programming knowledge. Utilizing AI technology, Polymer is powerful enough for a data analyst but easy enough for a standard citizen.

Start analyzing your data right here.

Posted on
December 2, 2021
under Blog
December 2, 2021
Written by
Ash Gupta
Co-Founder & CEO of Polymer Search. Previously Tech Lead for Machine Learning at Google AdWords and a quant developer on Wall Street.

Latest Stories