5 min read

Pivot Tables in Excel (Beginner's Guide)

A complete dummies guide to creating pivot tables in Excel to answer any questions you have about the data.

What are Pivot Tables?

Imagine you have a jar of lollies:

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.

Another 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:

  1. You can group them by color
  2. You can group them by shape
  3. You can group them by shape & color
pivot tables lollies example

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?

If you have any questions about your dataset, you can answer them using pivot tables. Here are some things that pivot tables can be used for:

Count by Groups/Categories:

Pivot tables allow you to easily count by group or categories. Let's say we have a table like this:

Food Type
1 Apple Fruit
2 Apple Fruit
3 Cabbage Vegetable
4 Orange Fruit
5 Asparagus Vegetable
6 Cabbage Vegetable
7 Orange Fruit
8 Lemon Fruit

And we want to figure out how many items there are in each category.

Using a pivot table, we can easily count how many fruits vs. vegetables there are:

Fruits: 5
Vegetables: 3

This can be extremely useful for many datasets.

For smaller datasets, you can count manually. However, imagine if this dataset was 1000 rows and had multiple categories: fruits, vegetables, dairy, protein, pet food, confectionaries etc. Counting manually would be a huge headache, so instead we use pivot tables to give us our answer within seconds.

Compare Sales Rep Totals

If you manage a sales team and working with sales data, you might want to find out: "Who is responsible for the most sales in your company?"

One way to do that is to sift through your data and add up all the sales each person has made. You'll find the list of sales that person 1 has made and you'll add it all up. Then you'll do the same for person 2, person 3, person 4 etc. This can take a tremendous amount of time, especially if your dataset is large or you have to do this task often.

Another way to do this is by using pivot tables which automatically gives you your answer in seconds.

Summarizing Financial Data

Pivot tables allow you to easily explore financial data to see where all the income/expenses are coming from. They allow a clearer picture on how much income your business is generating before and after tax, the distribution of expenses (i.e. how much is going to employee salary & benefits, building maintenance and rent, insurance etc.).

Average by Date/Category/Item

If you have retail date, you might find that you have a list of items sold with a date attached to them. To get the average sales for each date, you'll need to find all the items sold on one particular date and manually calculate the average for every day. Very time consuming. Pivot tables allow you to get the averages for all the dates within seconds.

Pivot Tables Explained

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

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. 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" -> "rows" and "quantity" -> "values."

pivot table example

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

What Goes Into Rows and Columns?

In general, you put any numeric measurement into the "values" box. But what about rows and columns?

Rows and columns are meant for categorical variables i.e. variables that categories things like gender, city, country, age brackets, and color. How do you decide if a variable should go into rows or columns?

Here's the good news: There is no right or wrong answer. You can put the above category into "columns" and it'll give you the same answer. So feel free to experiment with this.

How to Learn Pivot Tables

The best way to learn pivot tables is to download some sample datasets, come up with questions and try to answer them using a pivot table. The key is to practice, practice, practice. Once you get good, everything will be done on autopilot and you won't even need to think.

You can find sample datasets on Polymer Search or Kaggle.

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