How to Make a Pivot Table in a Google Docs Spreadsheet

A pivot table is one of the most useful features in Google Sheets. Pivot tables allow you to summarize large datasets, identify patterns, and create customizable views of your data. With just a few clicks, you can transform thousands of rows of data into a dynamic pivot table that makes analyzing your data a breeze.

In this step-by-step tutorial, you’ll learn how to create a pivot table in Google Sheets to turn raw data into meaningful insights.

What is a Pivot Table?

A pivot table allows you to analyze and summarize a large dataset in a flexible, customizable layout. Here are some key things pivot tables enable you to do:

• Rearrange views of your data – With various drag-and-drop fields, you can pivot (or change) views of your data to see things from different angles.
• Summarize large datasets – See overviews, totals, averages, and other summaries for the entire dataset or for rows/columns.
• Spot trends and relationships – Identify patterns, correlations, and insights that may be difficult to see in the raw data.
• Focus on areas of interest – Filter to narrow in on relevant data in large datasets by applying filters, slicers, and other options.

For example, you could use a pivot table to summarize monthly sales data for different products, view totals by region, apply filters by product categories, and more. The flexibility of pivot tables makes them a dynamic tool for data analysis.

Step 1 – Select Source Data

The first step is to add the dataset you want to analyze into a sheet and ensure it meets a few requirements:

• Raw dataset – Your source data should contain the raw, detailed records. That likely means each row represents an individual record.
• Column headers – Ensure the first row contains header labels, not data. This first row will be used to populate the various pivot table fields.
• No blanks or errors – Remove any blank rows/columns or fix errors before creating your pivot table to prevent issues.

For our example, we’ll use a dataset containing sales records for a fictional store:

Sales data image

This contains individual sales records over several months for different products.

Step 2 – Insert a Pivot Table

With your data source ready, it’s time to generate the pivot table:

1. Select any cell in your source data range, including column headers. For our example, we’ll select A1:E13.
2. Go to Insert > Pivot table. A pop-up will appear.
3. Make sure the range is correct under “Data range” then click Next.
4. Choose where you want the pivot table inserted by selecting a cell or sheet then click Create.

That’s it! Your empty pivot table framework has now been inserted alongside the raw data. Next, we’ll add fields and customize the view.

Step 3 – Add Rows, Columns, Values

When first created, the pivot table will be blank. Use the right-hand panel under “Pivot table editor” to add fields:

Blank pivot table

There are three areas you can add fields to:

• Rows – The pivot table rows. You can add multiple row fields to drill-down.
• Columns – The columns show data broken down by the column field(s).
• Values – The values area shows summarized numeric data, like sums or averages.

For our sales data example, let’s add fields as follows:

• Row – Month field. This will show one row per month.
• Column – Product field. This will break monthly data down by product.
• Value – Sales field summarized by SUM. This will show total sales amounts.

After adding those fields, our pivot table now neatly summarizes sales by product and month!

Filled pivot table

Step 4 – Customize the Pivot Table

One of the great things about pivot tables is they are highly customizable. Here are some ways to tailor your pivot table:

Rearrange Fields

Click on any field button in the editor and drag-and-drop to move it to another area. For example, we could swap Month and Product:

Swapped fields pivot table

Remove Fields

Click the X beside any field to remove it completely.

Apply Sorting

Use the sort icon beside a field to sort data. For example, we could sort months chronologically:

Sorted pivot table

Show/Hide Subtotals

Use the pivot table editor toggle buttons to show/hide row/column totals.

Apply Custom Formatting

Format cells, apply styles, add conditional formatting, etc. just like regular sheets.

From changing field placements to formatting, pivot tables enable complete customization. Feel free to drag fields around and tweak things until you get the view you want!

Step 5 – Save the Pivot Table

Don’t forget to save your sheet after adding a pivot table! By default, pivot tables in Google Sheets use the same data source range even if your original data changes.

That means you can add or edit records in your source data, and the pivot table will automatically update. No need to keep generating it from scratch.

Tips & Tricks

Here are some additional tips for working with pivot tables in Google Sheets:

• Use the Explore feature (under the Analyze tab) to have Google Sheets automatically suggest pivot tables from your data.
• Change the data source at any time by selecting the pivot table and updating the range under Pivot table editor > Data source.
• Double click any pivot table cell to view the raw data making up that value in a pop-up window.
• Check out Pivot table add-ons like Cloud Pivot Tables for even deeper analytics.
• Use the Paste special > Paste values only option on your pivot table to convert it static values independent of the data source.

Common Pivot Table Uses

Here are just a few examples of what you can do with pivot tables in Google Sheets:

• Summarize survey results – Analyze responses by question, demographic groupings, date, etc.
• Explore sales data – View totals by product, sales rep, region and more. Spot top-selling products.
• Report on web traffic – Breakdown website visits by source, page, browser etc. Identify trends.
• Analyze experiments – Compare test variables, look for variation and statistical significance.
• Review budgets – See expenditures across accounts, programs, time periods. Track vs. budget.

The options are limitless! Pivot tables enable you to answer almost any data question.

Next Steps

And that’s how to make a pivot table in Google Sheets! Here what to do next:

• Add the pivot table to a dashboard using sheets/cells to showcase key metrics.
• Create a pivot chart by selecting your pivot table then going to Insert > Chart.
• Make a copy of your pivot table to experiment with different views.
• Set up a Google Data Studio dashboard connected to your sheet to build interactive reports.

Now that you know how to make pivot tables, unlock insights from your data with custom analysis!

Let me know in the comments if you have any other questions!