5 min read

How to create pivot tables in Excel (dummies guide)

A complete beginner's guide to using pivot tables to analyze your data (in Excel).

Pivot tables are a very handy tool that allows us to quickly summarize complex datasets and structure data neatly to find patterns. It is a tool commonly used by people of all skill levels, not just data analysts.

Pivot Tables Explained

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

The concept of a pivot table is very simple.

Let's say we have a dataset:

sample dataset
*Just a snippet. The full dataset contains over 200 rows and can be downloaded here.

This dataset tells us the grocery sales ordered by city, category, product, the number of sales, and price of each item.

When looking at this data, we might have several questions such as:

  1. Which city is producing the most sales
  2. Which products are highest in demand? Which products aren't selling well?
  3. Do certain cities have preferences for certain products?

These questions can all be answered by creating a pivot table.

Example of a pivot table:

pivot table example

What Are Pivot Tables?

A pivot table is a customizable table that groups a smaller portion of a larger dataset together for easier understanding & reporting. Pivot tables allow you to choose which variables are being displayed and how they are being displayed (in columns or rows).  

How to Create Pivot Tables in Excel

  1. Select all your data
  2. At the top, click Insert -> Pivot Table
how to create pivot tables with excel

3. Drag & drop the variables at the top into the boxes below.

How do you know which variables go into which box? First, you need to have a question/hypothesis. Let's say we want to know:

Which city is producing the most sales?

Next, identify which variables are relevant to the question:

  • quantity (of sales) and
  • city

It'll take a bit of practice and experimentation before you fully understand where each variable goes, but in this case you put "city" as "rows" and "quantity" as "values."

pivot table example

Sort the information from highest to lowest: right-click on the table -> sort -> sort largest to smallest

Disadvantages of Excel:

Here are three problems you might encounter in Excel:

#1: Excel gets extremely difficult & inconvenient to use

The example you just saw was just a very basic use case of pivot tables. For simple analyses like that, Excel is serviceable.

However, more often than not, you are required to look at how variables impact each other, for example:

  • Which products sell the most in which city?
  • How do dates impact the number of sales of products across cities? (Seasonality)

messy complicated pivot table
Excel can get quite messy...

These questions are quite commonplace, but they are a huge nightmare to do in Excel.

The other day I was looking at my company's advertising data and had to figure out which ads were producing the best conversion rates across multiple demographics: gender, age, country, device type, etc.

I tried doing this on Excel and found it nearly impossible and extremely time-consuming due to how messy it got and the limited sorting options.

For that reason, I don't recommend Excel for tasks like these, instead, Polymer allows you to perform these analyses in 10-30 seconds.

With Polymer, it takes 2 clicks to create the same pivot table. And if you want to do something more complex? Just a few more clicks.

#2: You need a starting hypothesis

Pivot tables can be created if you have a question or hypothesis like 'which city produces the most sales?'

However what happens if you have a datasheet and have no idea where to start?

Also, what happens if you want to explore the data beyond the initial questions/hypotheses to find more interesting results?

Excel is not the best program for this due to how the information is organized and how messy the data can get. Data introspection is better done from a cleaner interface using interactive data.

There are three features in Polymer that helps you overcome this: 

  1. The 'auto-recommend' feature uses AI technology to help analyze your data and provides you with insights to help brainstorm ideas for your hypothesis
  2. The 'auto-insights' feature allows you to dive into your data to find patterns in your data that you would've otherwised missed.
  3. It uses a clean, interactive interface that allows you to easily filter/sort data which can make introspection a lot easier.

#3: Lack of contextual information

In Excel, there is no context to the information it provides:

Saying 'Los Angeles sold 3769 products' is not very informative. Is this a good number? How much higher/lower is it from the average? Is it underperforming or overperforming?

We won't really know unless we do the manual calculations ourselves using Excel formulas which is a very time consuming task (even moreso for large datasets which take forever for Excel to calculate).

Improved pivot Table

In Polymer, all the information is automatically calculated for you. Instead of just saying 'Los Angeles sold 3769 products, it'll also tell you that this number is 2.37% below the average.

Is Excel Obsolete?

Absolutely not!

Excel is one of the best tools for storing data and will never become obsolete in that regard. However, it's universally agreed amongst experts that Excel is a very bad for analyzing data.

Most data analysts prefer using other tools & programming languages like R, SQL and Python to analyse their data.

Unfortunately, not everyone will have the technical knowledge to operate these tools.

This is where Polymer comes in. Polymer allows you to become your own data analyst even if you have zero technical knowledge.

Best of all, it's free and requires no download!

Click here to start creating pivot tables in 2 clicks

Here's a simple tutorial on how to do that.

Posted on
November 3, 2021
under Blog
November 3, 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