Pivot Tables: what they are, how to create in Excel and Google. Tutorial with examples

Pivot tables are a spreadsheet analysis technique.

They allow a complete beginner with zero data experience to quickly analyze their data. 

But what are pivot tables and how do they work?

Estimated reading time: 9 minutes

Simply put, a pivot table is a data analysis technique used to summarize large data sets and answer questions you may have about the data. It is available in spreadsheet applications such as Microsoft Excel and Google Sheets. It's a very powerful way to organize your data.

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

Let's imagine we have a jar of candy:

And we want to understand: how many red candies are there? 

How many candies are there in each color? 

How many candies are there in each shape? 

One way to do this is to manually count them one by one. This can take a long time. 

The best way to get the answer is to create a pivot table. 

PivotTables are a way to reorganize and summarize complex data sets into one table, which allows us to easily find patterns or solutions to any questions we may have about the data set. In a sense, we are grouping several variables in the dataset. This action is also known as data aggregation. 

There are several ways to group these candies: 

• We can group them by color 
• We can group them by shape 
• We can group them by shape and color

In essence, this is what a pivot table does. Groups data and allows you to perform calculations such as counting and summing data.

What are pivot tables for?

PivotTables are used to summarize and reorganize large amounts of data into an easier-to-understand table that allows us to draw important conclusions. 

Use cases/examples of pivot tables in real life are:

• Summary of annual business expenses
• Show the average spending power of customer demographics
• Shows the distribution of marketing spend across multiple channels

PivotTables use functions like SUM and AVERAGE to quickly get the answer to these questions.

Why use a pivot table?

When faced with enormous amounts of data, it's easy to feel overwhelmed. This is where pivot tables come in. PivotTables are not just a tool; they are an essential resource in any data analyst's arsenal. Let's find out why you should consider using them:

1. Simplified data analysis: ask “What is a pivot table?” is like asking “How can I easily make sense of my data?” Pivot tables allow you to distill large amounts of data into digestible chunks, facilitating better decision making.
2. Quick Insights: Instead of sifting through row after row of data, PivotTables provide immediate insights by showing summaries of the data. This quick understanding can be invaluable for business decisions.
3. Versatility: Pivot tables can be used in various industries and for numerous purposes, from finance to sales to academic research. Their flexibility means that no matter what your field, they can be of immense help.
4. Data comparison: Do you want to compare sales data from two different quarters? Or perhaps you want to understand the growth rate of the last five years? PivotTables make these comparisons simple.
5. No advanced skills required: As highlighted in the introduction, even complete beginners can harness the power of pivot tables. You don't need advanced data analysis skills or knowledge of complex formulas.

The evolution of pivot tables: modern platforms

Pivot tables have come a long way since their introduction. While many associate the term “pivot table” with Microsoft Excel, today's landscape also offers other platforms that have integrated and improved this powerful functionality.

1. MS Excel: provided users with the ability to create pivot tables from lists or databases, making data analysis accessible to millions of people.
2. Google Sheets: Google's foray into the world of spreadsheets came with its version of pivot tables. Although similar to Excel, Google Sheets offers collaboration features that have made it a favorite for many.
3. Integrated BI tools: with the advent of Business Intelligence (BI) tools like Tableau, Power BI, and QlikView, pivot tables have found a new home. These platforms take the basic functionality of pivot tables and elevate them, offering advanced visualization and analysis capabilities.

How to create pivot tables in Excel

First step: insert the pivot table

Select the data you want to analyze in Pivot.

At the top, click Insert -> PivotTable -> From Table/Range.

Second Step: Specify whether you want to create the table in the same Excel sheet or in another Excel sheet

THIRD STEP: drag and drop the variables into the correct box

There are 4 boxes: filters, columns, rows and values. Here you can rearrange the different variables to get different results.

How you organize them depends on the questions you want to answer.

FOURTH STEP: set up the calculation

In the "values" box, after dragging a variable into it, you can select the calculation you want to apply. The most common are SUM and AVERAGE.

Since we want to get the total of all sales here, we will choose SUM.

Once the pivot table is created, you can sort the data from highest to lowest by right-clicking 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.

First Step: Insert the pivot table

Start by opening your spreadsheet in Google Sheets and selecting all your 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 -> PivotTable:

Second step: choose where to create the pivot table

You can create the pivot table in a new sheet or in the existing sheet. It's usually easier to insert it into a new sheet, but it depends on personal preference. 

Third Step: Customize the pivot table

There are two ways to customize a PivotTable in Google Sheets:

1. Using insights suggested by artificial intelligence

2. Using your own input

You can do both using on the right side of the pivot table you just created:

Click “Add” to create your custom pivot table. Similar to Excel, you can manually add variables in “rows, columns, values ​​and filters”.

Rows, columns, values ​​and filters: which one to use?

Now that you've set up a pivot table, how do you know which box to put each variable in? Rows, columns, values ​​or filters?

Here's how to use each:

• Categorical variables (such as gender and province) should be placed in “columns” or “rows”. 
• Numeric variables (like amount) should go into “values”
• Whenever you want to filter for a specific result, you can enter the variable in the “filters” box. For example, if I want to see only the sales of a specific province, or of a month.

Rows or columns?

If you're only dealing with one categorical variable, it doesn't matter which one you use. Both will be easy to read.

But when we want to consider 2 things at the same time, for example sales generated in a “province” and by “genre”, then you will have to mix and match and see which works best. Try placing one in the rows and the other in the columns and see if you like the resulting pivot table.

There is no fixed rule for deciding where to insert each variable. Put it in a way where it's easy to read the data.

Related Readings

• How to create a project report with Microsoft Project
• How to make reports and how to extract structured data from your projects managed with MS Project
• ZURB Foundation: User Interface CSS framework for a responsive front end
• How and what costs to enter into Microsoft Project Cost Management
• Blockchain what it means, what it is and how to use it

Ercole Palmeri