How to Make a Pivot Table in Google Sheets: Group, Filter, Calculate

This guide shows you how to make a pivot table in Google Sheets that turns a long list into a summary, such as total revenue per salesperson per month. It then covers the changes people usually want next: sorting, filtering, grouping dates by month, showing percentages and adding a calculated field. The steps are for Sheets on a computer.

Check your headers before you start

The most common way a pivot table goes wrong starts before you click anything. Google's help page says: "Each column needs a header." The headers become the field names you pick in the side panel. If a column has no header, or your data starts with a blank or merged title row, you won't get clean fields to work with.

So give every column one short, clear name in the first row (Date, Salesperson, Product, Price, Revenue), with the data directly underneath.

Create the pivot table

These steps follow Google's Create & use pivot tables page:

1. Open your spreadsheet in Google Sheets.
2. Select the cells with the source data, including the header row.
3. Click Insert > Pivot table. If the new pivot table sheet doesn't open, click it.
4. In the side panel, next to Rows or Columns, click Add and choose a field. For the revenue example, add Salesperson under Rows.
5. Next to Values, click Add and choose the field to summarize, such as Revenue.
6. To change how an item is listed, sorted, summarized or filtered, click the Down Arrow next to it.

Sheets sometimes shows recommended pivot tables under Suggested, and you can click one to add it. Google also says high-confidence suggestions are inserted automatically when you create the pivot table. If you'd rather start blank, go to Tools > Suggestion controls and turn off Enable Pivot table suggestions.

Edit, move or remove fields later

Click the pop-up Edit button under the pivot table to reopen the side panel. From there:

• Drag a field to another category to move it.
• Click Remove on a field to drop it.
• Click Select data range to change the source range.
• Click Clear all to start over.

Sort, show totals and rename headers

The rest of the customizations live on a second Google page, Customize a pivot table. Each one starts with clicking Edit under the pivot table.

• Sort: under Rows or Columns, click the arrow under Order or Sort by. You can sort by the row or column names, or by the summarized values, which is how you put the top salesperson first.
• Totals: check Show totals.
• Rename: click a row or column name in the pivot table and type a new one. You can't rename "Grand Total."

One quirk: if you rename a column in the source data after building the pivot table, the editor shows the old field name with the new one in parentheses. Google's example is "price per unit (Average Price)."

Group dates by month, or numbers into ranges

Grouping is done by right-clicking in the pivot table itself, per Google's customization page:

• By number range: right-click a cell, click Create pivot group rule, choose an interval size, optionally set where groups start and end, and click OK.
• By hand: right-click the cells, click Create pivot group, and select the cells to group.
• Undo it: right-click a grouped item and click Ungroup pivot items.

Show values as a percentage of the total

Under Values, find Show as, click Default, and choose a percentage option. Google's example is showing October sales as a share of annual sales.

Filter by condition or by values

1. Next to Filters, click Add and choose a field.
2. Next to Showing all items, click the Down arrow.
3. Choose Filter by condition or Filter by values.
4. With Filter by condition, pick a condition from the list, such as "is not empty," "less than a number" or "text contains," or write your own.
5. With Filter by values, uncheck the items to hide, then click OK.

A condition can take a plain value, a cell reference or a field name. Google's examples:

10
=Sheet1!A1
=Revenue

A cell reference is handy when you want to change the threshold without reopening the editor.

Add a calculated field

A calculated field adds a value column that the source data doesn't have, such as average price per product. Google's page describes a formula editor with menus for columns and functions, and live validation. Both paths below start the same way:

1. Click Edit under the pivot table.
2. Next to Values, click Add > Calculated field.

Path 1: a simple SUM total of one column

1. In the input box, type a column name, or pick one from the Columns tab. Google says this path summarizes the column with SUM; its page doesn't name a separate menu where you pick SUM for this path.
2. Watch the indicator: a green checkmark means the syntax is valid; a red exclamation mark means it isn't, and you need to pick another column.
3. Click Add at the bottom right. The new column appears.

Path 2: a custom formula

1. Name the field.
2. Type the formula in the editor, or pick a function from the Functions tab and a column from the Columns tab.
3. Check for the green checkmark. A red exclamation mark means the syntax isn't valid.
4. Under the input box, next to Summarize by, choose Custom.
5. Click Add at the bottom right. The new column appears.

Google's example formula:

=sum(Price)/counta(Product)

Price and Product are field names, and you can use regular Google Sheets functions too. If you compare a field to a value that contains spaces, put the value in quotation marks, as in Google's example ="h sdf".

If you're following an older tutorial, its screenshots may show a plainer calculated field dialog. Go by Google's current steps.

See the rows behind a number

To check where a total came from, click Edit under the pivot table, then double-click the cell. Sheets opens a new sheet with that cell's source rows.

Fix a pivot table that isn't showing new data

Google says the pivot table refreshes whenever you change the source data cells. When new data still doesn't show, look at these two things:

• A filter is holding the old list. According to Google, filter settings save the list of visible values. If you filtered by value and then added data, open the filter and check the new items.
• The range doesn't cover the new rows. Click Edit, then Select data range, and make sure the range includes everything you added.