How To Use Pivot Tables in Google Sheets

Last Updated on January 10, 2024 by Jake Sheridan

In this guide, you will learn all about creating & using pivot tables in Google Sheets.

What is a Google Sheets pivot table?

A Google Sheets pivot table is a powerful tool that allows users to summarize, analyze, explore, and present data in a spreadsheet.

It provides a flexible way to see different summaries of the data (like sums, averages, or counts) across various dimensions (like categories, time periods, or other groupings).

For example, you may have a large financial dataset containing data for financial records spanning several months across different regions.

Using a pivot table, you can quickly summarize the data into data points like monthly revenue, or total profit per region.

Pivot tables allow you to quickly create tables using a simple drag-and-drop interface instead of setting up custom formulas from scratch.

Pivot table uses:

  • Data summaries and analysis
  • Comparing and contrasting data
  • Interactive exploration
  • Data reporting and presentation

For more detail on these uses, checkout out the FAQ section at the end of this tutorial. But for now, let’s move on to creating a pivot table:

How to Create a Pivot Table in Google Sheets (in 6 steps)

Here’s a step-by-step guide to help you set up a pivot table in Google Sheets:

Step 1

Open your Google Sheets spreadsheet containing the data you want to analyze.

Ensure your data is organized well, ideally in a table format where each column has a header.

In our current example, we are working with a spreadsheet with sales data, including columns for Date, Region, and Revenue.

Step 2

Select the range of data you want to include in the pivot table.

You can do this by clicking and dragging across the cells.

If your data is well organized, you can simply click on any cell within your data range and Google Sheets will automatically select the entire data set for the pivot table.

In the table above, we can use the keyboard shortcut Ctrl+A (Cmd+A for Mac users) to select the entire source dataset.

Step 3

Click on Insert in the top menu, then choose Pivot table from the dropdown menu.

This will open a new dialog box or panel.

You can choose to insert the pivot table to an existing sheet or a new sheet.

For this example, let’s insert our pivot table to a new sheet tab in our document.

Step 4

In the new sheet, you will see the Pivot table editor side panel on the right side of the screen.

Here, you can choose the rows, columns, values, and any filters for your pivot table.

For example, in our sales data, you might choose ‘date’ for Rows, ‘Region’ for Columns, and ‘Sum of Revenue’ for Values.

Step 5

Add the desired fields to each of the sections in the Pivot table editor by clicking on “Add” next to Rows, Columns, Values, and Filters.

This will structure your pivot table.

In our current example, you will see the products listed down the rows and regions across the columns, with sales amounts summarized at each intersection.

Google Sheets gives us an option to create pivot date groups to specify how we want to aggregate by date.

Simply right-click on any cell in the date column and select Create pivot date group.

For this example, let’s add the “Year-Month” pivot date group.

Our pivot table will now be aggregated monthly instead of daily.

Step 6

Adjust and refine your pivot table as needed.

You can change the aggregation type (sum, average, count, etc.), rearrange rows and columns, or apply filters to narrow down the data.

As you make adjustments, the pivot table will update automatically, showing you the refined analysis of your sales data.

For example, we can add an AVERAGE aggregation field to determine the average daily revenue by month and region.

How do you use a pivot table in Google spreadsheets?

Once you have set up a pivot table in Google Sheets, you can start using it to analyze and interpret your data.

Here are some tips of what to do next after setting up your pivot table in Google Sheets:

Exploring Data Patterns and Trends

The primary use of a pivot table is to explore and identify patterns or trends within your data set.

For example, if you have sales data, you might discover which region produces the most revenue or what month you gain the most sales.

Rearranging Data

Pivot tables allow you to easily rearrange data to view it from different perspectives.

You can drag and drop fields between rows, columns, and values to see how the summary changes.

To access the pivot table options, simply click on the Edit icon at the bottom-left corner of your pivot table.

Applying Filters

To focus on specific data subsets, use the filter options in the pivot table.

Filters can be applied to any of the fields, allowing you to narrow down the results – for instance, viewing sales data for a particular product line or a specific time frame.

For example, we can use the Filter by condition option to set a specific time frame you want to summarize with the pivot table.

After setting our filter conditions, Google Sheets will automatically update the pivot table values.

Changing Summary Calculations

Pivot tables automatically summarize data (like summing up sales amounts).

You can change these calculations to average, count, max, min, and so on depending on what insight you are looking for.

This feature is particularly useful for complex data analysis.

Creating Charts from Pivot Tables

For a visual representation of your pivot table data, you can create charts directly from the pivot table.

This is useful for presentations or reports where visual data representation is more impactful.

How to create a chart from a pivot table in Google Sheets?

Creating a chart from a pivot table in Google Sheets can greatly enhance the visual representation of your data.

Here’s a step-by-step guide to help you do this yourself:

Step 1

Ensure you have a pivot table already created.

This pivot table should contain the summarized data that you want to represent in a chart.

In our current example, we have a pivot table showing monthly sales data broken down by region.

Click anywhere inside the pivot table. This action will ensure that the chart you create is linked to the pivot table data.

Step 2

Go to the menu bar and click on Insert > Chart.

This will open the Chart editor panel on the right side of your screen and a default chart will be placed on your sheet.

After this step, you’ll see a chart on your sheet, which may not yet represent your data accurately.

Step 3

In the Chart editor, under the Setup tab, choose the type of chart that best fits your data.

Common types for pivot table data include column charts, bar charts, line charts, and pie charts.

In the Setup tab, you can also select what columns to use for the x-axis and what columns to use as series data.

For our monthly sales data, we’ll use a line chart to track the sales of our two regions (Dallas and Los Angeles).

Step 4

Customize your chart using the Chart editor.

You can adjust the chart style, chart and axis titles, series, legend, and more.

This customization allows you to make the chart more informative and easier to understand.

We can add a title like “Monthly Sales by Region” and format the axis to improve readability.

The final chart should offer a clear visual representation of your pivot table, allowing for easy comparison of metrics across different categories over time.

How to make a pivot table from multiple sheets in Google spreadsheet?

Are you working with data that’s spread across multiple sheets in Google Sheets, such as having separate sheet tabs for each month?

If so, creating a single pivot table to analyze all this data collectively requires an important preliminary step:

Merging the data into a single, consolidated table.

This is because a pivot table in Google Sheets can only source data from a single range within one sheet at a time.

Here are the steps to combine data from multiple sheets into a single sheet and creating a pivot table from the result:

Step 1

In our example, we have two sheets (“Los Angeles Sales” and “Dallas Sales”) that contain a daily breakdown of the revenue for that region.

Step 2

Create a new sheet in your Google Sheets document where you will consolidate all the data.

You can name this sheet something like “Summary” or “Master Sheet.”

Step 3

In the new sheet, click on the first cell where you want your combined data to start.

Then, enter a formula using curly brackets {}.

For example, if you’re combining data from sheets named “January,” “February,” and “March,” your formula would look like:

= {‘January’!A2:D; ‘February’!A2:D; ‘March’!A2:D}

In this example, we’ll combine data from both sheets using the formula:

={‘Los Angeles Sales’!A1:C182;’Dallas Sales’!A2:C182}.

We place a semi-colon between each row to ensure that they are placed vertically rather than horizontally.

We can also use the VSTACK function to perform a similar operation.

In the sheet above, we used the formula =VSTACK(‘Los Angeles Sales’!A1:C182,’Dallas Sales’!A2:C182) to consolidate our sheets.

Do note that if you have headers in your original sheets, make sure to copy them manually to the top of your new “Master Sheet” or include them in the formula by starting from row 1 (e.g., ‘January’!A1:D).

You must also make sure to exclude the headers when referencing the remaining sheets.

Step 4

Once your data is successfully merged into a single master sheet, you can then create a pivot table using the master sheet as a source.

Click on any cell in the master sheet table and select Insert > Pivot table.

We’ll choose to add our pivot table into our existing Summary sheet.

Click Create to proceed.

You should now have a pivot table using data from multiple sheets.

FAQs

Are pivot tables the same in Excel and Google Sheets?

Pivot tables in Excel and Google Sheets serve the same fundamental purpose – to summarize and analyze data sets – but there are differences in their functionalities and user interfaces.

Excel generally offers more advanced pivot table features, such as calculated fields, more complex grouping options, and a broader range of data analysis tools.

Excel can also typically handle larger datasets compared to Google Sheets.

If you’re working with very large datasets, Excel might perform better in terms of speed and responsiveness.

Google Sheets, while simpler, offers enough functionality for most basic to intermediate needs and has the advantage of easy sharing and collaboration.

What is a pivot table used for?

A pivot table is a versatile and powerful tool available in spreadsheet applications like Google Sheets and Microsoft Excel for summarizing, analyzing, sorting, and presenting data.

Pivot tables are particularly useful in several key areas:

Data Summarization and Analysis

Pivot tables provide a quick way to convert large and complex datasets into a summarized form.

For instance, they can summarize sales data to show total sales per product or region.

Users can also use pivot tables to identify trends, patterns, and anomalies in their data, such as spotting which products are performing well in certain markets.

Comparing and Contrasting Data

Pivot tables make it easy to compare different data points, such as comparing sales across different time periods or among different sales teams.

Interactive Exploration

With pivot tables, you can interactively explore your data.

By rearranging fields, you can view data from different perspectives, facilitating dynamic data exploration.

This can save users the hassle of creating new tables from scratch which will typically involve creating custom formulas to get the data you want.

Data Reporting and Presentation

Pivot tables can certainly help in creating concise reports and can be a basis for data visualization.

Pivot tables can be a versatile data source for generating charts and graphs.

These graphs and charts can also take advantage of having pivot tables as source data.

You can add filters and new aggregation fields easily using the pivot table interface rather than using formulas to manipulate and slice your data.

Summary

Hopefully this guide has given you an overview of how to use pivot tables in Google Sheets.

What’s Next?

Explore some of the other useful resources on Sheets for Marketers: