How to Make a Pivot Chart in Excel: A Beginner’s Guide

Rootlet

Wed, 04 Nov 2026

A pivot chart is a chart that sits on top of a pivot table. It shows your summary as columns, bars or lines, and it changes whenever the pivot table changes. This guide shows you how to make one in Excel step by step, using the same sample sales data as our pivot table guide.

If you haven't built a pivot table before, start with our beginner's guide to pivot tables. It takes about ten minutes and gives you the pivot table you'll chart here.

What a pivot chart does

Microsoft describes pivot charts as charts that "complement PivotTables by adding visualizations to the summary data". The key difference from an ordinary chart is that the two are linked: changes you make to the pivot table show up in the chart straight away, and changes to the chart show up in the pivot table.

That makes a pivot chart handy for a report you update every month. Add the new rows, refresh, and the chart redraws itself.

Step 1: Get the sample data and a pivot table

We'll use made-up sales for a small stationery shop in March 2026: twelve rows with Date, Region, Product, Units and Sales in pounds. Download the sample data as an Excel file, or copy the table from the pivot table guide.

Build a pivot table on a new sheet with Region in Rows, Product in Columns and Sales in Values. You should see this:

Step 2: Insert the pivot chart

These steps are for Excel for Windows (Microsoft 365), checked against Microsoft's guide to creating a PivotChart:

1. Click any cell inside your pivot table.
2. Go to Insert > PivotChart.
3. Choose Column, then Clustered Column.
4. Click OK.

Excel places the chart on the same sheet. Each region gets a group of three columns, one for each product, and the legend shows which colour is which product.

You can also start straight from your data: click a cell in the data, choose Insert > PivotChart, pick where it should go and click OK. Excel then creates an empty pivot table and chart together, and you choose the fields in the same way.

Step 3: Read what the chart tells you

With the sample data, the chart makes two things obvious at a glance:

• South's columns are the tallest overall (306 in total), and the Midlands' are the shortest (144).
• In the Midlands, the Pen set column (80) towers over Notebooks (40), while in the North and South, Notebooks lead.

The second point is easy to miss in a table of numbers but jumps out of a chart. That's the main reason to chart a pivot table.

Step 4: Rearrange and filter

Because the chart and pivot table are linked, you change the chart by changing the fields:

• Swap the view: move Product to Rows and Region to Columns. Now each product gets a group of columns, one per region. Microsoft notes you can't swap rows and columns through the chart's Select Data box as you can with an ordinary chart, so moving the fields is the way to do it.
• Show one product: drag Product into Filters, then choose Notebook from the drop-down above the pivot table. The chart now shows Notebook sales only: Midlands 40, North 135, South 150.
• Show a total per region: remove Product altogether. You're left with one column per region, which is often the clearest chart for a report.

Step 5: Change the chart type and tidy it up

Right-click the chart and choose Change Chart Type to try a bar, line or pie chart. A pie chart only works with a single set of values, so remove Product first if you want one pie slice per region. Microsoft says you can use any chart type for a pivot chart except xy (scatter), stock and bubble charts.

Add a clear title such as "Sales by region, March 2026" and remove anything the reader doesn't need. Most formatting, including the layout and style, stays in place when you refresh. Trendlines, data labels and error bars are not kept, so add those last.

Step 6: Refresh when your data changes

Like a pivot table, a pivot chart doesn't update when you edit the original data until you refresh. Right-click inside the pivot table and choose Refresh, and the chart follows. If your data is set up as an Excel table (Insert > Table), new rows are picked up automatically on the next refresh.

On a Mac, in Excel for the web or in Google Sheets

• Mac: Microsoft says to create the pivot table first, select a cell in it, then use the Insert tab to add a column, line, pie or radar chart. Other chart types don't work with pivot tables on the Mac.
• Excel for the web: create the pivot table first, select a cell in it, then choose a chart from the Insert Chart drop-down on the Insert tab.
• Google Sheets: select the cells of your pivot table and choose Insert > Chart, as described in Google's help page on charts. Check the chart after you change the pivot table, in case its data range needs adjusting.

Common problems and quick fixes

• The chart shows a "Count of Sales". Excel thinks the Sales column is text. Fix the data, refresh, and change the value back to Sum in Value Field Settings.
• The chart is crowded. Too many fields in Rows and Columns make lots of thin columns. Move one field to Filters, or remove it.
• Your data labels disappeared. They aren't kept on refresh. Add them again once your data is final.

Keep going

Try charting your own data next: monthly spending by category, or sales by month. Put the date in Rows and you'll get a quick trend chart.

New to Excel? Excel for Beginners covers charts and pivot tables, including pivot charts, with practice files to work through.

Ready for more? Intermediate Excel goes further with pivot charts, slicers and one-page dashboards.

Sources

• Microsoft Support. Create a PivotChart.
• Microsoft Support. Overview of PivotTables and PivotCharts.
• Google Docs Editors Help. Add and edit a chart or graph.