How to Make a Pivot Table in Excel & Google Sheets in 2024

If you have a large dataset but little to no experience in data analytics, pivot tables might just change your life.

Pivot tables are data analysis tools that allow anyone to analyze their data quickly. 

But what exactly are pivot tables and how do they work? 

Read on to find out.

Pivot Tables: Step-by-Step Tutorial, Examples, & Tips for 2024

What is a Pivot Table?

A pivot table not only summarizes large arrays of data. It also allows users to interact with the dataset and identify trends, underline outliers, compare metrics, and more — regardless of their background in data science. 

By definition, a pivot table is a type of data visualization designed to help extract useful insights from raw datasets. 

Most spreadsheet applications, like Google Sheets and Microsoft Excel, are equipped with pivot table creation tools. Comprehensive Business Intelligence (BI) platforms like Polymer also help organize your data and create interactive pivot tables, either via the drag-and-drop dashboard builder or with AI-powered tools.

What are Pivot Tables Used for?

Fundamentally speaking, pivot tables are used for organizing and summarizing large amounts of data into a readable (sometimes interactive) table. This allows you to essentially convert raw data into useful insights, which can fuel your decision-making process.

Here are some real-life use cases of pivot tables:

• Identifying your top-performing sales agents based on the value and volume of transactions.
• Tracking the average transaction value across different customer segments.
• Analyzing marketing or advertising campaigns in terms of leads generated, spending, and so on.
• Highlighting the biggest business expenses per month, quarter, or year.

Some of the best tools for building pivot tables utilize functions like SUM, AVERAGE, and MEDIAN to help answer data-related questions. The use of interactive elements, like filters and "sort" buttons, also differentiates pivot tables from regular, non-interactive tables.

Why Use a Pivot Table?

Here are five important reasons why you need pivot tables in your data analysis:

1. Simplified Data Analysis. Pivot tables offer clarity when datasets become overwhelmingly large. With the right implementation, they allow you to complete various data-related tasks that may normally require multiple visualizations. 
2. Quick Insights. A well-structured pivot table saves you the trouble of sifting through hundreds of rows of data by highlighting clear data summaries. Interactive pivot tables that let you repurpose your table in a snap are advantageous in this regard.
3. Versatility: Pivot tables can be used across different stages of data analytics, including diagnostic analytics, descriptive analytics, and predictive analytics. They also benefit from the flexibility offered by BI and spreadsheet software, making them a valuable asset in any dashboard.
4. Data Comparison. Pivot tables are perfect for creating side-by-side comparisons between important metrics and data categories. BI platforms like Polymer also let you sort columns with one click, which speeds up comparisons by instantly identifying the top (or worst) performers.
5. No Advanced Skills Required. Perhaps the biggest benefit of pivot tables is how easy they are to create and use. Thanks to modern spreadsheet tools and visual BI solutions, pivot tables can be set up and ready to go with just a few clicks.

The Evolution of Pivot Tables: Modern Platforms

Pivot table creation tools have come a long way from your everyday spreadsheet app, like Microsoft Excel. The modern landscape offers a plethora of platforms that combine the power of pivot tables with advanced features like AI chatbots and interactive data visualization.

Here's a quick rundown of modern platforms that let you unlock the true potential of pivot tables: 

1. Polymer (Interactive Pivot Tables) — Polymer allows you to take pivot tables to the next level with interactive features. It also enables AI data analysis with tools like the PolyAI chatbot, insight suggestions, and auto-generated explanations.
2. Google Sheets — Microsoft Excel and Google Sheets are, without a doubt, the top two spreadsheet software on the internet. Google Sheets offer much of the same benefits as Excel in terms of versatility, ease of use, and function — with the added benefit of being 100% cloud-based and seamlessly compatible with Google products.

Integrated BI Tools — Apart from Polymer, other BI tools like Tableau, Power BI, and QlikView also lets you instantly convert datasets into ready-to-use pivot tables. Be sure to look at their features and available data connectors when choosing a BI solution for your team.

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 in Excel. 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 four boxes: filters, columns, rows and values. Here you can re-arrange the different variables to give you different outputs.

How you arrange them depends on what questions you want answered.

For further detail, scroll down below and read "Rows, Columns, Values and Filters: Which One to Use?"

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 we want to get the total of all sales here, 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 on a new sheet or an 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 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.”

Rows, Columns, Values, and Filters: Which One to Use?

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

Here's how to use each one:

• Categorical Variables (like gender, country, city, category) should go into “columns” or “rows.” 
• Numerical variables (like age, height, cost, unit price, and profit) should go into “values”
• Anytime you want to filter for a specific result, you can put the variable into the "filters" box. For example - if I only want to see sales from Canada.

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 look at two things at once - say income generated from "user country" and "gender," then you'll have to mix and match and see which one works best. Try putting one into rows and one into columns and see if you like the resulting pivot table.

There is no hard rule when deciding where to put each variable. Put it in a way where it's easy to read the data.

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 spent” and “purchases.” 

3. Set up the rest of the pivot table as normal, choosing the appropriate rows and columns.

The benefit 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. 3. Showing how the data would look if specific data points were removed

How to Refresh Pivot Tables

If you’re using Excel, you’ll need to manually refresh your pivot tables. 

If you’re using Google Sheets, they are updated automatically. However, if they aren’t refreshing, there might be a few reasons for this:

1. The Pivot Table Uses Filters

If you have filters added for your pivot tables, they won’t automatically update.

To remedy this, you’ll need to remove all filters from your pivot tables by clicking the ‘x’ next to them.

You can add them again afterward.

2. Newly Added Data is Outside the Selected Range

When you first create a pivot table, you have to select the data you want to analyze.

When new rows are added, they might fall outside of the pivot table’s range.

You can manually edit the pivot table’s range by using the pivot table editor and clicking the “select data range” symbol:

3. Your Data Uses Dynamic Functions

Dynamic functions cause your data to change automatically. Examples are RANDOM and TODAY functions.

If your dataset includes these functions, then it will cause the pivot table not to update automatically. The only way to fix this is to recreate the table or remove these functions entirely.

Helpful Tips and Tricks for Pivot Tables

Make the most out of your pivot tables with the following proven tips and tricks:

1. Clean Up Your Data Set 

In data analytics, faulty data leads to wonky, inaccurate results. 

The same goes if you're trying to turn your data into a pivot table. As such, be sure you use optimized data by removing duplicates, factoring in outliers, and selecting the right data range prior to loading data into your pivot table. 

2. Standardize Your Data

If you're creating a pivot table with datasets from different platforms, you need to implement standardization in terms of labels and number formats. BI tools with built-in data management are perfect for this since you don't need to disrupt your workflow just to implement consistent formatting across your datasets.

3. Encourage the Use of Filters

Spreadsheets and BI platforms allow users to narrow down their analysis through filtering tools. If your pivot table is only part of a more robust dashboard, use platforms like Polymer to create global filters that completely weed out irrelevant data from all your visualizations. 

4. Use Clear Labels

Help data stakeholders make sense of your pivot table by setting clear, straightforward field names and labels. Polymer, for example, lets you quickly personalize labels without leaving the platform through the visual data manager.

5. Always Refresh Your Pivot Table Data.

As with any other type of data visualization, it's important to make sure your pivot table works with the latest data available. You can automate this process with the help of BI solutions that use direct integrations with your data sources. 

What are Good Alternatives to Pivot Tables

Although pivot tables can be used in pretty much any form of data analysis, some insights are better presented through other visualization tools.

Here are some examples:

• AI assistants. We're past a point where AI tools can reliably retrieve valuable data and produce actionable insights with minimal human input.
• Scorecards and information windows. Some BI solutions let you create data scorecards or information panels to help users keep track of the numbers that matter.
• Correlation charts. Correlation charts and scatter plots let you dissect the relationship between two metrics — a more convenient approach than conducting line-by-line comparisons.
• Column charts. If your objective is to compare data, a practical option is to create a bar chart that directly compares data points side by side.

Pie and donut charts. Aside from being easy to use, a simple pie chart also makes it easier to spot outliers, compare data, and recognize trends without relying on pivot tables.

Conclusion

Pivot tables aren't as complicated as they sound. Experiment with them and you'll get the hang of it in minutes.

I recommend practicing with an example dataset. Polymer Search has multiple example datasets you can use.

Try Polymer Search for free to get access to interactive pivot tables and dashboards that'll make your data analysis 10x easier.

Frequently Asked Questions (FAQs) About Pivot Tables

Can you blend data with pivot tables?

Yes, you can definitely use pivot tables to combine data from multiple sources. You just need to use a tool that can consolidate, standardize, and optimize data for you, like Polymer and our built-in V3 Data Manager.

How do you update data in your pivot table?

If you're using a spreadsheet tool like Excel, you need to manually refresh your pivot table whenever new data becomes available. Alternatively, you can use a cloud-based Business Intelligence (BI) platform that automatically checks for new data and updates all visualizations that use them.

Is it possible to use non-numerical data with pivot tables?

Yes, you can use pivot tables to summarize or organize non-numerical data like dates, product categories, and statuses. Other types of textual data like names, email addresses, and cities are also crucial to organizing your pivot table or creating filters.

What exactly does "pivot" mean in a pivot table? 

The word "pivot" means to rotate or turn — like "pivoting" in basketball terms. In pivot tables, the word pivot essentially means turning your raw dataset into a more scannable and readable format. 

Are pivot tables usable or viewable on mobile devices?

Yes — most pivot table tools, including spreadsheet and data visualization solutions, are compatible with mobile devices. However, it's recommended that you use a desktop display when working with pivot tables for better readability and control. 

What are the limitations of pivot tables?

Although pivot tables are extremely flexible, they have some limitations. This includes the inability to use conditional formatting, performance issues with large datasets, and memory limitations with some tools.

How can you clean up your pivot table data? 

You can fix erroneous, incomplete, or inconsistently formatted data in pivot tables directly from the data sources. If you're getting wrong values for entire columns or rows, double-check your pivot table's configuration to ensure everything is set up properly. 

Can you share or export pivot tables? 

Yes, there are plenty of ways to save, share, or export pivot tables apart from manually exporting them from your spreadsheet software. With a BI platform like Polymer, you can also embed them via a code, share a link with your colleagues, or give other users direct access to your online dashboard. 

Is it possible to use pivot tables without coding? 

Yes, but you'll need to learn how formulas work especially if you use a spreadsheet app to create your pivot table. If you're looking for an even easier way to build pivot tables, take advantage of drag-and-drop data visualization or dashboarding software with a user-friendly, guided interface.