How to make a chart from a PivotTable? How to use pivot chart

Key points of this article

First think clearly about what questions the chart is going to answer, compare, look at trends or proportions, and then decide on the chart type.
Pivot charts are generated directly from pivot tables, and the charts will change synchronously when you drag fields or filter.
When there are too many categories, filter or merge the subcategories into "Others" first, and then make the diagram easier to read.
After finishing, check a few numbers against the pivot table, refresh after the data is updated, and confirm that the chart changes accordingly.

When doing data analysis, many people have used pivot tables to summarize thousands of rows of details into dozens of rows of results, but just looking at the table still requires row-by-row comparison. Turning the summary results into a chart, the gap between high and low, changing trends and proportions of each part can be seen at a glance. This is a straightforward step in data visualization. There is a special chart in Excel called a Pivot Chart, which is directly generated based on a Pivot Table, and fields and filters are linked.

This article is written for people who already know how to build pivot tables and want to continue making graphs. It does not talk about how to set rows, columns and value fields, but only talks about three things: how to generate a pivot chart from a pivot table, how to choose the chart type according to the content to be expressed, and how to check whether the chart has changed after the data is updated.

After reading, you should be able to choose the appropriate graph based on your summary results and make a graph within ten minutes that can be shown to others.

Which chart is recommended for pivot tables? One sentence conclusion

One-sentence conclusion: first think clearly about what questions the graph wants to answer, then choose the chart type, and finally use a pivot chart to generate it, so that the graph and summary table are always consistent.

your situation	Recommended practices	reason
Compare the numerical levels of each category, such as sales in each region	Clustered column chart; use bar chart when category names are long	The length of the column directly corresponds to the numerical value, which is convenient for comparison and sorting.
See how the same metric changes over months or quarters	Line chart	Polylines can clearly show rises, falls and fluctuations
Look at the proportion of each part to the whole, there are not many categories	Pie or donut chart, with no more than 5 to 6 categories	The sector angle represents the share, and it is difficult to distinguish when there are too many categories.
We need to look at both the total amount and the changes in each type of composition over time.	stacked column chart	The same column can reflect both total volume and segment proportions.

Before you start: Confirm these points first

The structure of the pivot table has been finalized: The pivot chart only reads the results in the pivot table. If the row, column fields and sorting are still changing, the graph will change repeatedly. It is recommended to adjust the table first and then start drawing.
The value field must be summarized in a correct way: the value field may default to count instead of sum or average. If you choose the wrong aggregation method, the numbers on the graph will mislead readers. Before making the chart, confirm it in the pivot table and write the summary method in the chart title or comments.
The number of categories should be moderate: If there are twenty or thirty columns or sectors in one picture, it will be difficult for readers to distinguish them. You can first use field filtering to retain only important categories, or merge categories with a small proportion into "Others".
Think clearly whether the data will be updated: The pivot chart will refresh along with the pivot table. If the detailed data will increase, you need to refresh it later and confirm that the data source range contains the new rows. If the data source range is fixed, new data may not appear in the graph.
Confirm whether special graphics are needed: Pivot charts support limited chart types. XY charts such as scatter charts usually cannot be generated directly from pivot tables. If your analysis requires it, consider alternatives in the FAQ below.

Steps to convert PivotTable to PivotChart in Excel

Select any cell in the pivot table: The cursor must be located in the pivot table area, and the tabs related to the pivot table will appear in the ribbon.
Find the analysis tabs related to the Pivot Table: When the cursor is in the Pivot Table, a set of tabs related to the Pivot Table will appear in the top ribbon, where you can usually find the entrance to generate charts.
Find the entrance to generate a Pivot Chart: In this set of tabs or the area where charts are inserted, find the function to generate a Pivot Chart. The two entrances have the same function, just choose one.
Select the chart type in the dialog box: Categories such as column charts, line charts, and pie charts are listed on the left side of the dialog box. After selection, a preview will be displayed on the right side. First select the type that is close to the content you want to express, and then click OK.
Check the generated chart and field layout: Field buttons will appear around the chart. After adjusting the rows, columns or value fields of the pivot table, the chart will change synchronously without the need to re-create it.
Use the buttons on the chart to filter: Click the field buttons on the chart to filter by region, year and other conditions. Charts and pivot tables will be filtered simultaneously to facilitate viewing a certain part of the data.
Modify title and data labels: Double-click the title and change it into a sentence that can directly explain the conclusion, such as "Comparison of sales in various regions in a certain year." When adding data labels, be careful not to overlap the text with each other. If necessary, only key values can be displayed.
Copy or Save Results: Charts can be copied to reports or documents for use. If you will continue to analyze, it is recommended to save the workbook and put the pivot table and charts in an easy-to-find location.

Check each item after completion

Check items	How to see	What to do if there is a problem
Are the values consistent with the pivot table?	Pick a few columns or sectors at random and compare them with the numbers in the pivot table	Check the summary method of the value field, change it to the required sum, count or average and then check
Is the order of categories reasonable?	Check whether the horizontal axis or legend is arranged according to business meaning	Sort row fields in a pivot table and the chart will update accordingly
Is the chart type appropriate?	Can the reader tell the conclusion within ten seconds?	Use column charts for comparisons, line charts for trends, and pie charts or donut charts for proportions.
Whether to synchronize data after updating	After modifying the detailed data, refresh the pivot table and see if the graph changes.	When the graph does not change, check whether the data source range contains new rows
Are the titles, units and legends complete?	Check whether the unit, time range and summary method are stated	Supplement the title and axis units to avoid misunderstanding by readers.

📊 Core comparison

There are two common ways to make charts, each with its own emphasis.

Way	advantage	limitations
pivot chart	Linked with the pivot table, field buttons can be used to filter directly, and charts can be synchronized after adjusting the pivot table.	Chart types are limited. XY charts such as scatter charts generally cannot be generated directly.
Normal chart (summary results based on pivot table)	You can choose almost all chart types, and the layout and color matching are more flexible	It is not linked with the pivot table. After the data is updated, the data area needs to be manually adjusted or re-created.

If it is just daily summary and comparison, it is preferred to use a data perspective chart. If it needs to be used for a formal report and has special requirements for chart type or layout, you can copy the summary results next to the pivot table and then use the normal chart function to complete it.

❓ Frequently Asked Questions (FAQ)

1. What is the difference between a pivot chart and an ordinary chart?

The pivot chart uses the pivot table as the data source, and the chart automatically follows after adjusting the fields or filtering. Ordinary charts rely on fixed cell ranges, and you need to update the range yourself after the data changes.

2. Can a scatter chart be made in a pivot table?

Pivot charts generally do not support XY charts such as scatter plots. You can copy the summary results of the pivot table into an ordinary numerical range, and then use the scatter chart in the insert chart function to generate it. This kind of chart is no longer linked to the pivot table, and needs to be adjusted manually after the data is updated.

3. What should I do if the numbers on the chart do not match the pivot table?

First check how the value field is summarized to see if it is a sum, count, or average. Then check whether there are still filter conditions in effect on the pivot table or chart. After confirming that everything is correct, refresh the PivotTable.

4. The chart is messy. Why?

Common reasons are too many categories, or the chart type doesn't match what you want to express. You can use filters to retain key categories, or merge subcategories into "Others." Use column charts for comparative data and line charts for trends. The charts will be much clearer.

📝 Summary

The key to making a pivot chart is to first think clearly about what you want to express, then choose an appropriate chart type, and finally use a pivot table to generate the chart. After you are done, check the numbers against the pivot table. Remember to refresh the data after it is updated. In this way, the summary results can be turned into a diagram that readers can understand at a glance.

Open OmniDoc and work with your spreadsheet file