---
title: "Pivot tables and charts in Google Sheets: step by step"
canonical: "https://www.owox.com/blog/articles/pivots-and-charts-in-google-sheets"
updated: "2026-10-05"
---

# Pivot tables and charts in Google Sheets: from a raw table to a report

A pivot table summarizes a raw table, and a chart shows the summary. This guide walks through the whole path in Google Sheets on one small example: prepare the data, insert a pivot table, use the suggested layouts or build your own, read the result, and turn it into a chart that stays in step with the pivot table.

[Google Sheets Tips](/blog/topics/google-sheets-tips) · Updated October 2, 2026 · [Ievgen Krasovytskyi](/team/ievgen-krasovytskyi) · 8 min read

![Pivot tables and charts in Google Sheets: from a raw table to a report](https://cdn.owox.ai/www/webflow/6a19ab683a908b5020ba66dc_AUTOMATICALLY-GENERATE-PIVOTS-CHARTS.png/public)

A pivot table turns rows of raw data into a summary, and a chart turns the summary into a picture. In Google Sheets the path from one to the other takes five steps and no formulas. The example below is a table of seven campaigns with a channel, a start date, a cost and a revenue.

| Step | Where |
| --- | --- |
| 1\. Prepare the table | One header row, one record per row |
| 2\. Insert the pivot table | Insert > Pivot table |
| 3\. Choose the layout | A suggestion, or Rows, Columns and Values by hand |
| 4\. Read the result | Totals by group, plus calculated fields |
| 5\. Chart it | Select the pivot rows, Insert > Chart |

## Step 1. prepare the table

A pivot table needs a plain list:

*   **One header row** with a unique name in every column. The names become the field names of the pivot table.
*   **One record per row**, with no subtotals, blank rows or merged cells inside the data.
*   **One spelling per category.** “Search” and “search “ are two groups to a pivot table. Clean the column first; see [TRIM](/blog/articles/trim-clean-t-functions-google-sheets) and [data validation](/blog/articles/data-validation-google-sheets).
*   **Real numbers and dates.** Values stored as text are counted, not summed.

Converting the range to a [table](/blog/articles/tables-feature-google-sheets) is optional and helps: the columns get types, and the table has a name.

## Step 2. insert the pivot table

Select the data, or any cell inside it, and choose **Insert > Pivot table**. The dialog asks for the data range and for the place of the result. A new sheet is the safe choice: a pivot table changes size as the data changes and overwrites nothing there.

![Google Sheets with a table of seven campaigns and the Create pivot table dialog: the data range is the table name, Table1, and Insert to offers New sheet and Existing sheet](https://cdn.owox.ai/www/content-marketing-article/pivots-and-charts-in-google-sheets/screens/2.png/public)

In our file the data was a table, and the dialog offered its name, Table1, as the data range.

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## Step 3. choose the layout

The new sheet shows an empty pivot table and the editor on the right.

### Start from a suggestion

The **Suggested** section at the top of the editor lists ready layouts that Sheets derives from the columns. For our table it offered “Sum of Cost, Revenue for each Channel”, “Sum of Revenue, Cost for each Channel” and “Sum of Revenue for each Channel”. One click on the first built the whole pivot table:

![Google Sheets with a pivot table of the sum of cost and the sum of revenue for the channels Email, Search and Social with a grand total, and the pivot table editor with the Suggested section and Channel under Rows](https://cdn.owox.ai/www/content-marketing-article/pivots-and-charts-in-google-sheets/screens/3.png/public)

A suggestion is a starting point: after it is applied, the same layout appears under Rows and Values, where it can be changed.

### Or build it by hand

The editor has four areas, each with an **Add** button:

*   **Rows:** the field whose values become the lines of the summary, here Channel. A second field adds a level inside each line.
*   **Columns:** a field whose values become columns, for a cross-table such as channel by month.
*   **Values:** the numbers to calculate, with **Summarize by** set to SUM, COUNT, AVERAGE, MAX, MIN, MEDIAN and others.
*   **Filters:** conditions that exclude rows of the source from the calculation.

Everything about the editor is in the guide to [pivot tables](/blog/articles/pivot-tables-google-sheets).

## Step 4. read the result

| Channel | Cost | Revenue | Revenue per unit of cost |
| --- | --- | --- | --- |
| Email | 350 | 4,500 | 12.9 |
| Search | 3,300 | 13,600 | 4.1 |
| Social | 3,300 | 5,200 | 1.6 |
| Total | 6,950 | 23,300 | 3.4 |

The first two columns of numbers are the pivot table; the last one is what they are for. Search and Social cost the same, and Search brought more than twice the revenue. Email is tiny in cost and by far the most efficient.

To have such a ratio inside the pivot table, add a **calculated field**. As Google’s help describes it: under Values choose **Add > Calculated field**, enter a formula over the field names, such as `=SUM(Revenue) / SUM(Cost)`, and set **Summarize by** to **Custom**. The ratio is then calculated from the totals of each group, which is the correct way: an average of the row ratios would give another, misleading number.

## Step 5. chart it

Select the rows of the pivot table without the Grand Total line and choose **Insert > Chart**. Sheets picks a chart type and opens the chart editor. For our pivot table it drew a column chart with the two measures side by side for each channel:

![Google Sheets with the pivot table of cost and revenue by channel and a column chart built from it: two columns, cost and revenue, for each of Email, Search and Social](https://cdn.owox.ai/www/content-marketing-article/pivots-and-charts-in-google-sheets/screens/4.png/public)

Leave the total out of the selection: as a bar it would be taller than all the others together and flatten them.

In the chart editor, **Setup** holds the chart type, the data range, the X-axis and the series, and **Customize** the titles, colors and axes. Which type fits which pivot table:

| The pivot table has | Chart |
| --- | --- |
| One field in Rows, one or two values | Column or bar chart |
| Dates in Rows | Line chart |
| A field in Rows and another in Columns | Stacked column chart |
| Shares of one total, a few categories | Pie chart |
| Two values to compare for a relation | Scatter chart |

More in [pivot charts](/blog/articles/pivot-charts-google-sheets), [charts for visualization](/blog/articles/charts-for-visualization-google-sheets) and [bar graphs](/blog/articles/bar-graphs-in-google-sheets).

## Keep the report up to date

*   **Changed values.** The pivot table recalculates when the source cells change, and the chart follows the pivot table.
*   **New rows.** The pivot table reads the range it was given. After our pivot table was created, the editor showed the range as B2:F9, so a campaign added in row 10 would be outside it. Check the data range in the editor after adding rows, or give the range without an end row, such as B2:F.
*   **New groups.** A new channel adds a line to the pivot table. A chart whose range ends at the old last line will not show it, so include a few spare rows in the chart range.
*   **Filters for readers.** A [slicer](/blog/articles/slicers-google-sheets) on the sheet lets a reader filter the pivot table and its chart without opening the editor.

## Problems and fixes

**⚠️ Problem:** A category appears twice in the pivot table.

✅ **Solution:** The two spellings differ, often by a space at the end. Clean the source column.

**⚠️ Problem:** A value shows a count where a sum was expected, or 0.

✅ **Solution:** The numbers of that column are stored as text. Convert them to numbers, then set Summarize by to SUM.

**⚠️ Problem:** New data is missing from the pivot table.

✅ **Solution:** It is outside the data range. Extend the range in the editor.

**⚠️ Problem:** The chart has one enormous bar.

✅ **Solution:** The Grand Total is in the chart range. Select the rows without it, or switch off **Show totals** for the row field.

**⚠️ Problem:** The chart lost its shape after the pivot table changed.

✅ **Solution:** The pivot table grew or shrank and the chart range no longer matches it. Edit the data range under Setup in the chart editor.

## Related guides

*   [Pivot tables](/blog/articles/pivot-tables-google-sheets): The editor, grouping, filters and calculated fields.
*   [Pivot charts](/blog/articles/pivot-charts-google-sheets): Charts built on pivot tables.
*   [Charts for visualization](/blog/articles/charts-for-visualization-google-sheets): Chart types and when to use them.
*   [Slicers](/blog/articles/slicers-google-sheets): Filters on the sheet for pivot tables and charts.
*   [Tables](/blog/articles/tables-feature-google-sheets): Named, typed ranges as a source.
*   [QUERY](/blog/articles/query-function-google-sheets): The same summary as a formula.
*   [Exploratory data analysis](/blog/articles/exploratory-data-analysis-google-sheets): Where pivot tables fit in a first look at data.

## The pivot table is quick; the table under it is the work

Once the data is in the sheet, the five steps take minutes. Getting it there is what takes the hours: exports from each ad platform and the CRM, pasted under one another, with columns that have to be matched by hand each time. And the pivot table and its chart show whatever was pasted last.

The [OWOX Data Marts extension for Google Sheets](https://workspace.google.com/marketplace/app/owox_data_marts/94902851409) covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.

![Google Sheets with the OWOX Data Marts extension open beside an ad spend table: the Unified Ad Spend data mart, nine of fourteen columns ticked and a Save & Run button – the rows in the sheet come from a governed data mart, not a pasted export](https://cdn.owox.ai/www/content-marketing-article/pivots-and-charts-in-google-sheets/screens/1.png/public)

What changes for the person who maintains the sheet:

*   **Refresh replaces re-export.** _Refresh current report_ in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
*   **The source is written on the sheet.** The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
*   **No SQL and no warehouse access needed.** The analyst decides which data marts and columns are available. You choose from that list.

The extension does not write or change the analyst’s SQL. The pivot table and the chart stay as they are and recalculate on the refreshed rows. The extension comes with the OWOX Cloud plans of [OWOX Data Marts](https://www.owox.com/app-signup); [spreadsheet reporting](/features/spreadsheet-reporting) describes both ways data reaches a sheet.

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

How do I share a pivot table and its chart with other people?

Share the spreadsheet with the Share button: viewers see the pivot table and the chart, editors can change them. A chart can also be published or downloaded as an image from its three-dot menu, and copied into Google Docs or Slides, where it can be updated from the sheet.

Can I change a pivot table after applying a suggested layout?

Yes. A suggestion only fills in Rows and Values in the pivot table editor. After it is applied, you can add or remove fields, change Summarize by from SUM to COUNT or AVERAGE, add filters and calculated fields, and change the chart type in the chart editor.

What does the data need to look like before I insert a pivot table?

A plain list: one header row with a unique name in every column, one record per row, no blank rows, subtotals or merged cells inside the data, one spelling per category, and numbers and dates stored as real numbers and dates, not as text.

How do I keep a pivot table and its chart up to date?

A pivot table recalculates when its source cells change, and a chart built on it follows. New rows are included only if they are inside the data range of the pivot table: check the range in the editor after adding data, or give a range without an end row, such as B2:F.

How do I create a chart from a pivot table in Google Sheets?

Select the rows of the pivot table without the Grand Total line and choose Insert > Chart. Sheets picks a chart type and opens the chart editor, where the type, the data range and the series can be changed.

What are suggested pivot tables in Google Sheets?

The Suggested section at the top of the pivot table editor lists ready layouts that Sheets derives from the columns of the data, such as the sum of revenue for each channel. Clicking one builds the pivot table, and the layout can then be edited like any other.

## Who wrote this

![Ievgen Krasovytskyi](https://cdn.owox.ai/www/webflow/68404586b341508a789a4aa5_.png/public)

[Ievgen Krasovytskyi](/team/ievgen-krasovytskyi) · Head of Marketing

Ievgen Krasovytskyi is the Head of Marketing at OWOX, leading strategy across content, SEO, product marketing, and AI-powered automation. With deep expertise in analytics infrastructure, data warehouses, and marketing technology, he builds systems that connect marketing performance to business outcomes. Ievgen writes about SaaS growth, analytics workflows, and the future of AI in marketing operations.

[LinkedIn](https://www.linkedin.com/in/ievgen-krasovytskyi-a38a1253/) · [All articles](/team/ievgen-krasovytskyi)

[Google Sheets Tips](/blog/topics/google-sheets-tips)

## Related articles

[Google Sheets Tips · IMPORTDATA in Google Sheets: import a CSV or TSV file from a URL · October 2, 2026](/blog/articles/importdata-google-sheets)

[Google Sheets Tips · COUNT and COUNTA in Google Sheets: differences and examples · October 2, 2026](/blog/articles/count-and-counta-in-google-sheets)

[Google Sheets Tips · IMPORTRANGE in Google Sheets: formula, examples and errors · October 2, 2026](/blog/articles/importrange-google-sheets)

[See all articles →](/blog/articles)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Google Sheets Tips](https://www.owox.com/blog/topics/google-sheets-tips) — /blog/topics/google-sheets-tips.md
- [Ievgen Krasovytskyi](https://www.owox.com/team/ievgen-krasovytskyi) — /team/ievgen-krasovytskyi.md
- [TRIM](https://www.owox.com/blog/articles/trim-clean-t-functions-google-sheets) — /blog/articles/trim-clean-t-functions-google-sheets.md
- [data validation](https://www.owox.com/blog/articles/data-validation-google-sheets) — /blog/articles/data-validation-google-sheets.md
- [table](https://www.owox.com/blog/articles/tables-feature-google-sheets) — /blog/articles/tables-feature-google-sheets.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [pivot tables](https://www.owox.com/blog/articles/pivot-tables-google-sheets) — /blog/articles/pivot-tables-google-sheets.md
- [pivot charts](https://www.owox.com/blog/articles/pivot-charts-google-sheets) — /blog/articles/pivot-charts-google-sheets.md
- [charts for visualization](https://www.owox.com/blog/articles/charts-for-visualization-google-sheets) — /blog/articles/charts-for-visualization-google-sheets.md
- [bar graphs](https://www.owox.com/blog/articles/bar-graphs-in-google-sheets) — /blog/articles/bar-graphs-in-google-sheets.md
- [slicer](https://www.owox.com/blog/articles/slicers-google-sheets) — /blog/articles/slicers-google-sheets.md
- [QUERY](https://www.owox.com/blog/articles/query-function-google-sheets) — /blog/articles/query-function-google-sheets.md
- [Exploratory data analysis](https://www.owox.com/blog/articles/exploratory-data-analysis-google-sheets) — /blog/articles/exploratory-data-analysis-google-sheets.md
- [OWOX Data Marts](https://www.owox.com/app-signup)
- [spreadsheet reporting](https://www.owox.com/features/spreadsheet-reporting) — /features/spreadsheet-reporting.md
- [Google Sheets Tips · IMPORTDATA in Google Sheets: import a CSV or TSV file from a URL · October 2,…](https://www.owox.com/blog/articles/importdata-google-sheets) — /blog/articles/importdata-google-sheets.md
- [Google Sheets Tips · COUNT and COUNTA in Google Sheets: differences and examples · October 2, 2026](https://www.owox.com/blog/articles/count-and-counta-in-google-sheets) — /blog/articles/count-and-counta-in-google-sheets.md
- [Google Sheets Tips · IMPORTRANGE in Google Sheets: formula, examples and errors · October 2, 2026](https://www.owox.com/blog/articles/importrange-google-sheets) — /blog/articles/importrange-google-sheets.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md