Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 29 ยท Working with Data

Pivot Tables

Summarize thousands of rows in seconds with Google Sheets pivot tables: rows, columns, values, filters, date grouping and calculated fields.

A pivot table turns a long list of records into a compact summary. Give it a table of sales and it can show total revenue per region, orders per month, or average price per product and region side by side, with no formulas to write. When the source data changes, the pivot table updates.

In this lesson you will build one step by step, choose what goes in rows, columns and values, filter, group dates and add a calculated field. Pivot tables are a point-and-click feature, so the real steps are described in text. To see what the tool is doing, the Python blocks use pandas, whose pivot_table works the same way. The pandas code is a stand-in for the idea, not what Sheets runs.

Four boxes build a pivot

Rows One row per group.
Columns One column per group.
Values What to calculate.
Filters Which records to include.
1 / 2

Preparing the source data

A pivot table works best on a clean, flat table: one header row, one record per row, no blank rows, no merged cells and no subtotals mixed in. Each column should hold one kind of value, such as Date, Region, Product, Units and Revenue. Numbers should be real numbers and dates real dates (see Dates and Times). Fixing this first saves most pivot headaches.

Creating a pivot table

  1. Select any cell inside the table, or the whole range.
  2. Choose Insert โ†’ Pivot table.
  3. Put it in a New sheet (the usual choice) or an existing one, then press Create.
  4. In the side panel, click Add next to Rows, Columns, Values and Filters and pick fields.

If you plan to add records later, set the data range to whole columns such as A:E so new rows are included automatically.

Google Sheets cannot run on this page. The Python examples use pandas on a small table to show what each box of the pivot panel does. The table below has the same shape as the data you would select in Sheets.

Python Stand-in: rows and values

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In Sheets, the same result comes from adding region under Rows and revenue under Values (summarized by SUM). The pivot also adds a Grand total row unless you untick Show totals.

Summarizing values

Each field in Values has a Summarize by setting. The common choices are:

Setting Meaning
SUM Total of the numbers (the default for numbers)
COUNTA How many records (default for text)
COUNT How many numbers
COUNTUNIQUE How many different values
AVERAGE, MIN, MAX, MEDIAN The matching statistic

You can add the same field more than once, for example SUM and AVERAGE of revenue next to each other. The Show as option turns values into a percentage of the grand total, of the row total or of the column total, which answers questions such as "what share of revenue comes from each region?".

Columns for a two-way table

Adding a field to Columns splits each row into several columns. With region in Rows and product in Columns, you get a grid with one cell for every region and product pair, plus row and column totals. Empty combinations appear blank. The grid is easy to read for two categories, but not for five or six; if columns multiply, move one field to Rows instead.

Python Stand-in: two-way table, counts and percentages

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Filtering

The Filters section limits which records feed the pivot. Click Add, choose a field, and then either filter by condition (for example Greater than or Text contains) or by values (tick the ones to include). You can also filter a field that is already in rows or columns, using the arrow in its panel header. Filtered-out records are still in the source data; they are just not counted. A filter at the pivot level is different from a sort or filter on the source sheet, so keep them straight when numbers do not match.

You can sort row and column groups by their labels or by a value: choose Sort by in the field's panel and pick, say, descending by SUM of revenue to see best regions first.

Grouping dates

If Date is added to Rows, every date appears as its own line. To get totals per month, right-click one of the dates in the pivot and choose Create pivot date group, then pick Year-Month, Year-Month-Day, Month, Day of week, Quarter and so on. The same option exists on the field's panel under Group. Grouping needs real dates, so a column that Sheets reads as text will not offer it. You can also group numeric fields into ranges with Create pivot group.

Python Stand-in: totals per month

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Calculated fields

Sometimes the number you need is not a column in the source, such as revenue per unit. Under Values, click Add โ†’ Calculated field and type a formula that uses field names:

TEXT
=revenue / units

Be careful: a calculated field sums each field first and then applies the formula, so revenue / units means total revenue divided by total units for each group, which is the right weighted average. It is not an average of the per-row ratios.

Python Stand-in: total ratio versus average of ratios

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

For East the calculated field gives 550 / 10 = 55 per unit, while averaging the two row ratios gives 75, a very different answer because one order was nine times larger.

Tips and limits

  • Do not type inside a pivot table. It is generated; change it through the panel. Put your own formulas beside it, not over it.
  • Use GETPIVOTDATA to read a specific cell from a pivot table with a formula that survives re-layout.
  • Blank or text in a numeric column makes Sheets choose COUNTA instead of SUM; clean the source or change Summarize by.
  • New records do not appear if the pivot's range stops at the old last row; edit the range or use whole columns.
  • Charts can be built from a pivot table, which the later lesson on charts covers.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which pivot table section decides what is calculated, such as a sum of revenue?

    Values holds the fields you summarize and the function (SUM, COUNTA, AVERAGE...). Rows and columns decide the groups.
  2. 2. You add Date to Rows and see one line per day. How do you get monthly totals?

    Right-click a date in the pivot and create a date group, or use Group in the field panel. This needs real dates.
  3. 3. A calculated field =revenue/units gives, per group:

    Each field is summed first, then the formula is applied, which gives a weighted ratio.
  4. 4. New rows added below the source do not show in the pivot. What is a likely cause?

    Edit the pivot's data range or use whole columns like A:E so later rows are included.

Summary

  • A pivot table summarizes a flat source table; set it up with Rows, Columns, Values and Filters.
  • Values can be summed, counted, averaged and more, and can be shown as percentages.
  • Date groups give monthly, quarterly or weekday totals when the source holds real dates.
  • Calculated fields sum each field first, then apply your formula.
  • Keep the source clean, never type inside the pivot, and use whole-column ranges so it keeps growing.

Next: Charts and Sparklines