How To Create A New Pivot Table

Pivot tables let you analyze large amounts of data and narrow down large data sets to see the relationships between data points. Google Sheets uses pivot tables to summarize your data, making it easier to understand all the information contained in your spreadsheet.

What Are Pivot Tables?

Pivot tables are handy for analyzing massive amounts of data. Where a regular spreadsheet uses only two axes—columns and rows—pivot tables help us make sense of the information in your spreadsheet by summarizing any selected columns and rows of data. For example, a pivot table could be used to analyze sales brought in by divisions of a company for a specific month, where all the information is randomly entered into a dataset.

Sample dataset in Sheets

Creating a pivot table from the information in the picture above displays a neatly formatted table with information from selected columns, sorted by division.

Sample Pivot Table

RELATED: The Best Google Sheets Add-Ons

How to Create a Pivot Table

Fire up Chrome and open a spreadsheet in Google Sheets.

Next, select any of the cells you want to use in your pivot table. If you're going to use everything in your dataset, you can click anywhere on the spreadsheet, you don't have to select every cell first.

Select all the cells you want to appear in your Pivot table

Note:Each column selectedmusthave a header associated with it to create a pivot table with those data points.

On the menu bar at the top of the page, click "Data," then click "Pivot Table."

From the menu bar, click "Data," then click on "Pivot Table."

If the new table doesn't open automatically, click "Pivot Table," located at the bottom of your spreadsheet.

If your pivot table doesn't automatically open, click "Pivot Table" at the bottom of your browser to open it.

How to Edit a Pivot Table

From the pivot table sheet, the side panel lets you add rows, columns, values, and filters for viewing your data. Sometimes, Sheets offers up suggestions based on the information you chose. Click a suggestion or click "Add," located next to any of the other options below.

Choose between suggested pivot tables, or create your own custom pivot table.

When you click on any of the suggestions, Sheets automatically builds your pivot table using the option you selected from the list given.

If you'd rather customize a pivot table to for your own needs, click any of the "Add" buttons next to the four options below. Each option has a different purpose, here's what they mean:

Rows:Adds all unique items of a specific column from your dataset to your pivot table as row headings. They are always the first data points you see in your pivot table in light grey on the left.
Columns:Adds selected data points (headers) in aggregated form for each column in your table, indicated in the dark grey along the top of your table.
Values:Adds the actual values of each heading from your dataset to sort on your pivot table.
Filter:Adds a filter to your table to show only data points meeting specific criteria.

Click on "Add" next to Rows and add in the rows you want to display in your pivot table. For this example, we'll be adding division and subdivision.

Click "Add", then choose which rows you want added to you table.

Next, click "Add" next to Values As and insert the values you want to sort information. We'll be using the sum of the number of units sold and the average price per unit.

Next to "Values," click "Add," then choose what values you want summarized in your table.

To change the sorting of each unit, click the drop-down menu, located under the heading "Summarise by." You can choose from the sum, count, average, min, max, among others listed below.

Click the drop-down menu of any value to choose how you want them to appear in the table.

After adding all rows, columns, values, etc. what we're left with is an easy to read pivot table that outlines which division sold the most units and the average cost of all units sold.

All the information is neatly displayed, with each division's totals listed below

RELATED: How to Import an Excel Document into Google Sheets

If you'd prefer to make your own formula, click "Add" next to the Values as heading, then click "Calculated Field."

To add a custom summary, click "Add" next to the Values heading, then click "Calculated Field."

From the new value field, enter a formula that best summarises the data in your pivot table.

In the box provided, enter a custom formula for your data

If you want to add a filter to your table, click "Add," located next to the Filters heading.

Click "Add," then choose a filter for your table

When adding a filter to your table, select—or deselect—the values you want to show on your table, then click "OK" to apply the filter.

Choose how you want to filter the data, then click "OK."

That's all there is to it. Although this is just an introduction to using pivot tables, there is a seemingly endless amount of possibilities for utilizing this feature that not many people know much about.