Create Pivot Charts in Google Sheets

Unlike Microsoft Excel, there is no “PivotChart” menu item to create pivot charts in Google Sheets. To create a pivot chart, you must first create a pivot table report.

Once you have created a pivot table report, you can then create a pivot chart similar to a regular graph from that report. However, I recommend this only if you no longer need to edit the source data.

If the source data is subject to change in the future, I do not recommend using this method to create pivot charts in Google Sheets.

This is because there is a chance that your pivot table may grow or shrink in size, which could affect your ability to omit the grand total row or column.

What’s the big deal about the grand total row?

If your pivot table has a grand total row, the graph will show that as a data point. We usually remove it manually if our source data is static, meaning that it will not shrink or grow further.

There is one workaround for this problem in dynamic data (growing or shrinking data). You can make an exact copy of the pivot table report and remove the grand totals. Keep it hidden and create a chart from this duplicate pivot table.

This also requires some extra workarounds, such as adding dummy headers and selecting an additional range while creating the graph.

We will cover all of these topics in this Google Sheets tutorial.

Introduction to Pivot Charts in Google Sheets

Charts are graphical representations of data. Pivot charts are no exception. However, pivot charts are created from a pivot table rather than from unprocessed data (raw data).

The purpose of a pivot table is to summarize data without coding formulas. The visual representation of a pivot table will help you see comparisons, trends, and patterns more clearly, and will help you make informed decisions about critical data in your undertaking.

Also, learning to create and customize pivot charts in Google Sheets is essential to generate lively, informative, and customizable dashboard reports.

How to Create a Pivot Chart in Google Sheets

If you have static data, meaning that you will not update the source data anymore, follow these steps:

Select any cell in the pivot table.
Go to the Insert menu and click Chart.
Google Sheets will create a pivot chart that best fits your pivot table data.

Pivot Chart with Static Data in Google Sheets

We do not require the “Grand Total” in the pivot chart, but we do in the pivot table. So, we should not disable it from the pivot table editor. Instead, we will adjust the chart range to exclude it.

Double-click on the created pivot chart to open the editor.
Go to the “Setup” tab and edit the range. As per the above example, it should be E2:G7.

Removing Pivot Table Grand Total Row from Chart

What If I Have a Growing Range in the Pivot Table?

The drawback of the above approach to creating a pivot chart is the static pivot table range E2:G7.

When you add data below row 11 in columns A to C, the pivot table may expand its size. However, this will not be reflected in the pivot chart, as its range is E2:G7.

Another issue is that deleting rows in A2:C10 may shrink your pivot table report. This will cause the grand total data point to reappear in the pivot chart.

The solution to this problem is to use a second pivot table (a copy of the original one without a grand total).

Creating a Pivot Chart from a Duplicate Pivot Table

To create a duplicate of the first pivot table:

Go to cell E1 and copy it (Ctrl + c in Windows and ⌘ + c in Mac).
Go to cell S1 (in a distant column so that the first pivot table can expand in the future) and paste it (Ctrl + v in Windows and ⌘ + v in Mac).

You must remove the grand total rows and columns from this duplicate pivot table by unchecking “Show totals.”

To uncheck “Show Totals” in the pivot editor:

Hover over the new pivot table to see the pencil Edit icon and click it.
In the Pivot Editor, uncheck the Show Totals checkbox.

Can we now create the pivot chart from this pivot table in Google Sheets?

Not yet! You need to add some dummy headers in columns before making the chart. Once we create the pivot chart, we can delete these dummy headers.

This is because Google Sheets’ charts can pick data from rows dynamically but not from columns. So, you need some dummy headers in columns before creating the chart.

Now we are ready to create the pivot chart.

Select S2:W20. The actual pivot table range is S1:U7. I’ve included two additional columns (V and W) and rows from rows # 8 to 20. You can add more rows and columns. But for the columns, you must enter dummy headers.
Apply Insert > Chart.
Within the chart editor, check “Row 2 as headers.”
Now you can delete the dummy headers.
Now add more data in column range A2:C. That will reflect in the pivot chart.

Pivot Chart with Dynamic Data in Google Sheets

This way, we can create a dynamic pivot chart in Google Sheets.

FAQs

Can I hide the duplicate pivot table?

Yes, you can hide the duplicate pivot table. It is handy if you want to keep your sheet tidy.

Once you have created the pivot chart, you can hide the duplicate pivot table by right-clicking the column letter and choosing the hide option.

You can then open the chart editor and check the Include hidden/filtered data checkbox.

What are the supported chart types in a pivot chart in Google Sheets?

There are no restrictions on the chart types that you can use in a pivot chart in Google Sheets. You can use any chart type that is supported by Google Sheets, such as column charts, bar charts, pie charts, line charts, and scatter charts.

Can I create a pivot chart without a duplicate pivot table in Google Sheets?

Yes, you can create a pivot chart without a duplicate pivot table in Google Sheets. To do this, you can use the FILTER function to filter the first pivot table. The following example formula is based on our sample data above. You can enter this formula in cell S2.

=LET(pivot,FILTER(E2:R,E2:E<>"Grand Total"),FILTER(pivot,CHOOSEROWS(pivot,1)<>"Grand Total"))

Conclusion

I have given two options for creating a pivot chart in Google Sheets: one for static data and another for dynamic data. I hope you find them useful.

One more thing before concluding: when you use a duplicate pivot table, place it as far away from the first table as possible. This will allow the first table to expand without returning a #REF error.