How to use pivot tables in Google Sheets

[.blog-callout]

✨TL;DR:

• A pivot table summarizes large Google Sheets datasets into dynamic tables that calculate totals, averages, or counts automatically.
• This guide covers four tutorials: creating a pivot table, adding a calculated field, grouping data, and adding slicers.
• Pivot tables work well for one-off analysis, but they're static and single-user. If you need a live, shareable dashboard, connect your data to a proper database instead. [.blog-callout]

A pivot table is a Google Sheets feature that helps you summarize and analyze large datasets quickly. It reorganizes your raw rows into a dynamic table that automatically performs calculations and aggregation based on the fields you select, so you can spot trends without manually sorting through hundreds of rows.

In this article, we'll walk through four tutorials for working with pivot tables in Google Sheets:

1. How to create a pivot table in Google Sheets;
2. How to add a calculated field to a pivot table in Google Sheets;
3. How to group data within a pivot table in Google Sheets;
4. How to add slicers to a pivot table in Google Sheets.

How to create a pivot table in Google Sheets

For our example table, we'll use the sales data of five products, five countries, and five salespeople, with the goal of finding out the number of items sold per country in a given year. However, you can use your own dataset for whatever calculations you need to perform.

Step 1: Set up your dataset in Google Sheets

First, you need to ensure that all your data is in a single sheet in Google Sheets.

Pro Tips:

• There shouldn’t be any merged cells in your data.
• Give a title to every column of your database.
• Delete all empty rows and columns within your data sets.
• Every item should be of the same data type in all columns(numbers, string).

In our case, our dataset includes the following variables:

• Country;
• Item type;
• Salesperson name;
• Unit sold;
• Unit price;
• Order date.

Step 2: Select the data

Once you have all the data in Google Sheets, you need to select the range of cells where the data you wish to include in the pivot table can be found.

Step 3: Insert a pivot table

In order to insert a pivot table, navigate to the top menu bar in your Google Sheets and click on "Insert." A drop-down menu will appear, where you should choose "Pivot table."

Step 4: Click on “Create”

Because you selected the data range to be used to create a pivot table in Step 2, it will now appear in the dialogue box. However, you have the option to choose whether you want the pivot table to be inserted on a new sheet or on the existing sheet. We recommend that you select the “Existing sheet” option to preserve your data.

Once you’ve made your choice, you can simply click on “Create.”

Step 5: Organize your pivot table

Now, you need to decide where each variable should go, rows, columns, values, or filters. Here’s my recommendation:

• Categorical variables (e.g., gender, country, city, items name category): these are best suited for the columns and rows of your pivot table
• Numerical variables (e.g., age, height, cost, unit price, unit sold, profit): these belong in the values section of your pivot table
• Filtering specific results: if you want to focus on specific results, use filters.

Step 6: Add a categorical variable to the rows

In this example, in order to have the country names in every row, click on the “Add" button near “Rows” in your Pivot table editor. A drop-down menu will pop up, where you can select your chosen variable.

This step will result in rows being added to your pivot table as shown below:

Step 7: Add a categorical variable to the columns

In order to display a categorical variable in each column, click on the "Add" button near “Columns” in your Pivot table editor. In our example, we selected the “Item Type” as a column.

This step will result in columns being added to your pivot table.

Step 8: Add a numerical variable as values

Now you need values in your pivot table to perform calculations on your data. To fill the table with values, click on the “Add” button next to “Values” to choose a numerical variable. In our example, we chose “Units Sold.”

Values will now appear in your pivot table.

Step 9: Add a variable as a filter

Optionally, you can add a filter to your pivot table. In our example, we wanted to find the amount of units sold in 2014 and, in order to accomplish that, we need to use filters.

To add a filter, click on the "Add" button next to “Filters” and select the variable you want to use as a filter.

In our example, because we selected the variable Order Date, it allowed us to choose which dates we wanted. As we only wanted to find the number of units sold in 2014, we selected all the dates in the year 2014, and then clicked on “Ok.”

Voila! You have created a pivot table in Google Sheets.

How to add a calculated field to a pivot table in Google Sheets

Pivot tables in Google Sheets can have multiple formulas and functions, which you can use to perform specific calculations. The following is a step-by-step method you can use to add a calculated field to your pivot table.

In our example, we'll calculate continent-wide sales for the fourth quarter of the year, using our dataset with 3 months of sales data from 3 different continents: Europe, Africa, and Asia.

Step 1: Ensure your pivot table is ready

Once you have a pivot table, you need to ensure that you have your desired categorical pivot as rows. In our example, that’s the variable Continent, so that each continent in our dataset is displayed per row.

Step 2: Add a calculated field

Now, it’s time to add the calculated field. In our example, we want to combine and calculate data from three different columns.

Step 2.1: Click on “Add” next to “Values”

To add the calculated field, click on the “Add” button next to “Values.”

Step 2.2: Select “Calculated Field”

In the drop-down menu that appeared, select the “Calculated Field” option.

This step will result in the adding of a new column to your pivot table.

Step 3: Select “SUM”

Now that you added a calculated field, you need to select whether you want to sum values or do something custom. In our case, we want to select “SUM.”

Step 4: Write the formula

In the formula section, write the formula to be used to calculate the values in the calculated field. In our example, because we want to sum the values of October, November and December, we added the following formula: October+November+December.

Step 5: The calculated field has been added to your pivot table

Because you followed all the previous steps, you can see the result of the calculation you set up. Here, we can see the total revenue generated in a quarter in three continents in the pivot table.

How to group data within a Google Sheets pivot table

Grouping data involves collecting similar data into categories based on shared criteria, such as years, geographic regions, and salary ranges. Below, we‘ll group data by year.

Step 1: Select the data to be grouped

First, you need to select the data you want to group and then right-click. In our example, we selected the dates that belong to the year of 2015.

Step 2: Click on “Create pivot group”

In the drop-down menu, click on “Create pivot group.”

Step 3: Your data is now grouped

Your pivot table now features grouped historical data for the year 2015.

How to add slicers to a pivot table in Google Sheets

Slicers in Google Sheets can simplify the process of filtering data within your pivot table. Think of a slicer as a tool that lets you trim down the data you don't need, showing instead only the information you need to focus on.

This time, we‘ll show you an example of how to use slicers in a pivot table with a dataset of properties sold by different real estate agents.

Step 1: Click on “Data”

Once you have a pivot table, click on "Data” on the top menu.

Step 2: Click on “Add a Slicer”

In the drop-down menu, click on "Add a slicer."

Step 3: Specify the data range

A slicer bar will appear, along with a window, where you need to specify the data range to which you want to apply the slicer. Let's use the cell range A1:G7, so that it includes all the data in our pivot table.

Step 4: Choose the aspect you want to analyze

In the Slicer panel, under the Column section, use the dropdown menu to select what you want to analyze. Here, I chose "SUM of Closed” because I want to see the amount of properties sold, not their type.

Step 5: Filter the data

Now, you can use the slicer to exclude the data from the pivot table that is irrelevant to your data analysis. To do so, click on the slicer bar, and uncheck the values that you want to hide. Once you’re done, click OK.

With that, you’ve successfully added a slicer to your pivot table, hiding all data related to Noah Smith.

Pivot table use cases

As the examples above show, pivot tables can be applied to a wide range of scenarios for data analysis and reporting. Some of the most common use cases include sales analysis, financial reporting, survey data analysis, inventory management, and attendance tracking.

Sales Analysis

Pivot tables can help you analyze sales data to identify trends, top-performing products, and revenue-generating regions. For example, you can create a pivot table to display the total revenue generated by each product across different regions, by configuring it in the following way:

• Rows: Product
• Columns: Region
• Values: SUM of Revenue

As the pivot table would display the total revenue for each product in every region, you’d easily identify the bestselling product and the top-performing region.

Financial Reporting

Pivot tables are an excellent tool for generating financial reports, such as income statements, balance sheets, or cash flow statements. For instance, you can create a pivot table to summarize expenses by category and month, by configuring it as follows:

• Rows: Expense Category
• Columns: Month
• Values: SUM of Expense Amount

This pivot table would display the total expenses for each category by month, allowing you to monitor spending trends and identify areas for cost reduction.

Survey Data Analysis

Analyzing survey data often involves examining responses across various demographic groups or question categories. Pivot tables can help you explore the data and draw meaningful insights, if you set it up in the following way:

• Rows: Age Group
• Columns: Satisfaction Level (e.g. Very Satisfied, Satisfied, Neutral, Dissatisfied, Very Dissatisfied)
• Values: COUNT of Responses

This pivot table would display the number of responses for each satisfaction level within each age group, enabling you to identify patterns and trends in customer satisfaction.

Inventory Management

Pivot tables can be used to track and analyze inventory levels, allowing you to identify patterns in sales, monitor stock levels, and plan for future demand. Here’s how you can set up a pivot table for this purpose:

• Rows: Product
• Columns: Warehouse Location
• Values: SUM of Stock Quantity

This pivot table would show the stock levels of each product in different warehouse locations, helping you manage inventory more effectively and allocate resources as needed.

Attendance Tracking

In educational or professional settings, pivot tables can be utilized to track and analyze attendance records, highlighting trends and patterns in attendance or absence. To accomplish this, set up a pivot table in the following way:

• Rows: Student Name (or Employee Name)
• Columns: Month
• Values: COUNT of Absences

This pivot table would display the number of absences for each student (or employee) by month, allowing you to monitor attendance and address any issues that may arise.

What to do when a pivot table isn't enough anymore

Pivot tables are great for answering a specific question once: how many units sold last quarter, which continent grew fastest, which agent closed the most deals. But they have real limits. They're static snapshots that only refresh when someone with edit access opens the file and hits refresh. They don't handle permissions, so anyone with the link can see every row, filter, or slicer. And once your data lives across several tabs or several tools, building a single pivot table becomes a workaround rather than a solution.

If your team relies on the same pivot table every week to track sales, inventory, or operations, that's usually a sign the spreadsheet needs to be replaced. At that point, it's worth connecting your data to Softr Databases instead of Google Sheets. You get the same rows and columns you're used to, but with relational links between tables, row-level permissions so each teammate or client only sees what they should, and database AI agents that can clean, tag, or enrich records automatically.

You can create both a database and a dashboard interface on top of it using Softr's AI Co-Builder. Because the dashboard is synced with your data, it updates automatically as new records come in (no refresh button required). Instead of exporting a static summary and emailing it back and forth, your team and stakeholders get a shared, permission-based view of the same numbers, in real time.

Try Softr free and migrate from Google Sheets today.