How to Create a Pivot Chart in Excel

You have spent an hour building a pivot table that finally shows exactly what you needed which can be monthly sales broken down by region, product category, and sales rep. The numbers tell a clear story. But the moment you share it with your manager or drop it into a presentation, the response is always some version of the same thing: can you make this into a chart?

Numbers in a table require effort to interpret. A well-built chart communicates the same information in seconds. Someone glancing at a bar chart showing the Northeast region towering above every other region understands the story instantly without reading a single cell. That same insight buried in a pivot table requires finding the right row, reading the number, and mentally comparing it to every other number in the column.

A pivot chart solves this by connecting a visual chart directly to your pivot table so they work together as one object. When you filter the pivot table by region or change the date range, the chart updates automatically to reflect exactly what the pivot table is showing. You do not rebuild the chart every time the data changes. You change one filter and everything updates together.

This guide walks through creating a pivot chart in Excel step by step, from preparing your data to formatting a chart that is ready to drop into any presentation.

What Is a Pivot Chart in Excel?

A pivot chart is a chart that is directly connected to a pivot table. It visualizes the same data the pivot table is summarizing, updates automatically when the pivot table changes, and shares the same filters so adjusting one adjusts both simultaneously.

Think of the pivot table and the pivot chart as two views of the same information. The pivot table is the detailed view for people who want to read exact numbers. The pivot chart is the visual view for people who want to understand patterns and comparisons at a glance. They live together, they update together, and filtering one filters both.

The difference between a pivot chart and a regular Excel chart is that a regular chart is a static snapshot. If your underlying data changes you need to update the chart manually or rebuild it. A pivot chart is dynamic. It stays connected to the pivot table and reflects whatever that table is currently showing, including any filters, groupings, or date ranges you apply.

Step by Step: Creating a Pivot Chart in Excel

Step 1: Prepare Your Data

Before creating anything, your source data needs to be in the right format. Every column needs a header in the first row. There should be no blank rows or blank columns in the middle of the data. Every column should contain one type of data consistently, dates in the date column, numbers in the revenue column, text in the region column.

A clean dataset for this example looks like this:

Date          Region       Category      Sales Rep     Revenue    Units
2024-01-05    Northeast    Electronics   Sarah Mills   4200       14
2024-01-06    Southeast    Clothing      James Park    1850        9
2024-01-07    Midwest      Electronics   Anna Chen     3100       11
2024-01-08    Northeast    Furniture     David Lee     5600        4
2024-01-09    West         Clothing      Sarah Mills   2200       12

Click anywhere inside your data and press Ctrl+T to convert it to an Excel Table. This is not strictly required but it means the pivot table automatically picks up new rows of data when you refresh it, which saves you from having to update the data source range manually every time new data arrives.

Step 2: Insert a Pivot Table

Click anywhere inside your data. Go to the Insert tab on the ribbon and click PivotTable. The Create PivotTable dialog appears. Excel automatically detects your data range. Select New Worksheet so the pivot table gets its own clean sheet and click OK.

A blank pivot table appears on the new sheet with the PivotTable Fields panel on the right side showing all your column headers as available fields.

Step 3: Build the Pivot Table

Drag fields into the four areas of the PivotTable Fields panel to define what the pivot table summarizes:

Drag Date into the Rows area. Excel may automatically group dates by month or quarter depending on your version. If it does not, right click any date in the pivot table, select Group, and choose Month to group by month.

Drag Region into the Columns area so each region becomes a separate column.

Drag Revenue into the Values area. Excel defaults to Sum of Revenue which is exactly what you want for a sales analysis.

Your pivot table now shows total revenue by month in rows and by region in columns. Every combination of month and region has its own cell showing the sum of all transactions that match both criteria.

Step 4: Insert the Pivot Chart

Click anywhere inside the pivot table to make sure it is selected. Go to the Insert tab on the ribbon. In the Charts group you will see a PivotChart button. Click it.

The Insert Chart dialog opens showing all available chart types. This is where you choose the right chart type for what your data is communicating.

For comparing revenue across regions by month, a Clustered Bar chart or Clustered Column chart works well. The months appear along one axis and the colored bars or columns represent each region, making it immediately clear which region performed best in each month.

Select Clustered Column and click OK. The pivot chart appears on the same sheet as the pivot table, already populated with your data, already formatted with a legend showing region colors, and already connected to the pivot table so any filter change updates both simultaneously.

Step 5: Move the Chart to Its Own Sheet

A chart sitting on top of a pivot table makes both harder to work with. Right click the border of the chart and select Move Chart. In the Move Chart dialog select New Sheet, give it a name like Sales Chart, and click OK. The chart moves to its own dedicated sheet and the pivot table stays cleanly on its own sheet. Both remain connected.

Step 6: Choose the Right Chart Type for Your Data

The chart type you choose determines what story your data tells. Excel offers several chart types from the PivotChart button and the right one depends on what you are trying to communicate:

Clustered Column is best for comparing values across categories side by side. Use it when you want to show how different regions or products compare within the same time period.

Stacked Column is best for showing how individual parts contribute to a total. Use it when you want to show total revenue per month while also showing how much each region contributed to that total.

Line chart is best for showing trends over time. Use it when the direction of change over months or quarters is more important than the comparison between categories at any single point.

Pie chart is best for showing how one category breaks down as a percentage of a total. Use it only when you have a small number of categories and want to show proportional contribution rather than absolute values.

Bar chart is the horizontal version of a column chart. Use it when your category labels are long text that would overlap on a vertical axis.

To change the chart type after insertion, right click the chart area and select Change Chart Type. The same Insert Chart dialog opens and you can switch to any type without rebuilding the chart.

Step 7: Use Slicers to Filter Both Table and Chart Together

Slicers are the feature that makes pivot charts genuinely powerful for presentations and dashboards. A slicer is a visual filter button panel that sits on the sheet and filters both the pivot table and the pivot chart simultaneously with a single click.

Click anywhere inside the pivot table. Go to the PivotTable Analyze tab on the ribbon and click Insert Slicer. A dialog shows all your available fields. Check Region and Category and click OK. Two slicer panels appear on the sheet.

Click Northeast in the Region slicer. Both the pivot table and the pivot chart immediately update to show only Northeast data. Click Electronics in the Category slicer and both update again to show only Northeast Electronics. Click multiple items by holding Ctrl while clicking to show several regions or categories at once.

For presentations this means you can walk through the data interactively without rebuilding a single chart. Click a region, the chart updates. Click a category, the chart updates again. The entire analysis becomes a live interactive dashboard controlled by clicking buttons.

Step 8: Format the Chart for Clarity

A chart that communicates clearly is almost always simpler than the default Excel output. Work through these formatting steps to clean up the chart:

Add a descriptive title by clicking the default Chart Title text and typing something specific like Monthly Revenue by Region 2024. A specific title tells the viewer exactly what they are looking at without having to study the chart to figure it out.

Remove gridlines if they add visual clutter without adding information. Click any gridline to select all of them and press Delete. For charts with precise values the gridlines help. For charts used in presentations they often just add noise.

Add data labels to show exact values on each bar or column. Right click any bar in the chart, select Add Data Labels, and Excel places the numeric value at the top of each bar. This is useful when you want viewers to read specific numbers without switching to the pivot table.

Adjust colors to match your brand or presentation theme by right clicking a data series, selecting Format Data Series, and choosing a fill color. Making each region a distinct easily distinguishable color helps viewers connect the legend to the correct bars quickly.

Resize the chart by clicking and dragging the corner handles until it fills the available space cleanly without overcrowding.

Pivot Chart vs Regular Chart in Excel

Feature	Pivot Chart	Regular Chart
Connected to pivot table	Yes, automatically	No, static data range
Updates when data changes	Yes, on refresh	Only if source range updates
Responds to filters and slicers	Yes	No
Built from summarized data	Yes	Uses raw data directly
Best for dashboards	Yes	Only for static reports
Requires a pivot table	Yes	No
Can change chart type	Yes	Yes

Common Limitations

Pivot charts do not support all chart types. Scatter plots, bubble charts, and stock charts cannot be created as pivot charts in Excel. If you need one of these chart types you need to build a regular chart from the pivot table data by copying the values out of the pivot table and charting them separately. The tradeoff is that the regular chart will not update automatically when the pivot table changes.

Moving or deleting the pivot table breaks the pivot chart. Because the pivot chart is directly linked to its pivot table, deleting the pivot table removes the data source for the chart and it stops functioning. If you need to reorganize your workbook, always move the pivot table and chart together rather than moving one independently.

Pivot charts can look cluttered with too many categories. A pivot chart showing twelve months of data across eight regions produces 96 bars in a clustered column chart, which is unreadable. Use slicers to filter to a meaningful subset or switch to a stacked column or line chart that handles many categories more gracefully when the number of series is large.

Refreshing is not automatic. When your source data changes, the pivot chart does not update until you manually refresh the pivot table. Right click anywhere inside the pivot table and select Refresh, or go to the PivotTable Analyze tab and click Refresh. For workbooks connected to external data sources you can set up automatic refresh on a schedule through the connection settings.

Common Mistakes to Avoid

Not cleaning the source data before building the pivot table. Blank rows, inconsistent category names like Northeast and north east in the same column, and mixed data types in a column all produce incorrect pivot table summaries and therefore incorrect pivot charts. Clean the data first and the chart will be accurate.

Putting too many fields into the chart at once. A pivot chart showing revenue broken down by region, category, sales rep, and month simultaneously is impossible to read. Start with two dimensions, the most important ones for the question you are answering, and add complexity only if the chart remains readable.

Using a pie chart for more than five categories. A pie chart with eight or ten slices becomes meaningless because the slices are too small to compare visually. Use a bar or column chart whenever you have more than four or five categories and save the pie chart for simple proportional comparisons with a small number of segments.

Forgetting to refresh after data updates. If your source data changes and you present the chart without refreshing, you are showing outdated numbers. Build a habit of right clicking and refreshing every time you open a workbook that contains pivot tables and pivot charts connected to data that changes regularly.

Leaving the default chart title. The default title is usually Chart 1 or Sum of Revenue, which tells the viewer nothing useful. Always replace it with a title that describes specifically what the chart is showing, what time period it covers, and what the key insight is if space allows.

Pivot Chart Cheat Sheet

Task	How to Do It
Insert pivot chart	Click inside pivot table, Insert tab, PivotChart
Change chart type	Right click chart, Change Chart Type
Move chart to new sheet	Right click chart border, Move Chart, New Sheet
Add slicer	PivotTable Analyze tab, Insert Slicer
Add data labels	Right click a bar, Add Data Labels
Refresh pivot chart	Right click pivot table, Refresh
Change chart colors	Right click data series, Format Data Series
Edit chart title	Click Chart Title text and type new title
Remove gridlines	Click gridlines, press Delete
Filter chart with slicer	Click items in slicer panel

A pivot chart takes everything useful about a pivot table and makes it immediately understandable to anyone who sees it. The numbers that required careful reading become visual patterns that communicate in seconds. The filters that required changing pivot table settings become slicer buttons that anyone can click without knowing how pivot tables work.

The workflow is always the same. Clean your data. Build the pivot table with the right rows, columns, and values. Insert the pivot chart from the PivotTable Analyze or Insert tab. Choose the chart type that matches what you are trying to communicate. Add slicers for interactive filtering. Format the title, colors, and labels until the chart tells its story without requiring any explanation.

The most important decision in the entire process is choosing the right chart type for your data. Column charts for comparisons. Line charts for trends. Stacked charts for part to whole relationships. Pie charts only when you have very few categories and proportion is the story. Get the chart type right and the formatting almost takes care of itself.

FAQs