How to Make a Pivot Chart in Excel

Creating a chart in Excel is simple enough, but a PivotChart takes your data visualization to a whole new level. Instead of a static picture of your data, a PivotChart is an interactive visual that updates dynamically as you slice, filter, and rearrange its underlying PivotTable. This article will show you exactly how to prepare your data, create your first PivotChart, and customize it into an interactive report.

What Exactly is a PivotChart?

A PivotChart is directly linked to a PivotTable. Think of it this way: the PivotTable summarizes your data, and the PivotChart visualizes that summary. When you filter data in the PivotTable (for example, showing sales for only one region), the PivotChart instantly updates to reflect that change. Likewise, if you use the filters on the chart itself, the PivotTable data will change too.

This dynamic relationship makes PivotCharts one of the most powerful tools in Excel for building interactive dashboards and reports. You can quickly explore your data from different angles without ever needing to create a new chart from scratch.

First, Prepare Your Data

Before you can build a great PivotChart, you need clean data. Garbage in, garbage out is true for all analysis, and it’s especially true here. Your raw data should be organized in a simple tabular format with a few key rules:

• No Blank Rows or Columns: Ensure your dataset is a single, continuous block of data. Avoid having empty rows or columns cutting through your table.
• Unique Column Headers: Every column must have a distinct header in the first row. These headers become the fields you use to build your PivotTable and PivotChart.
• Consistent Data Types: Keep data within each column consistent. A column for dates should only contain dates, and a column for revenue should only contain numbers.

Here’s a sample of sales data that's perfectly formatted for a PivotTable:

OrderDate | Region | Sales Rep | Product | Units | Revenue 1/15/2024 | North | Sarah | Alpha | 50 | $5,000 1/20/2024 | West | Michael | Beta | 30 | $4,500 2/5/2024 | North | Sarah | Gamma | 25 | $3,750 2/11/2024 | East | David | Alpha | 40 | $4,000 …and so on.

Pro Tip: Turn your data range into an official Excel Table by clicking anywhere inside it and pressing Ctrl + T. This makes your data range dynamic. When you add new rows of data later, the PivotTable will automatically include them when you refresh, saving you from manually adjusting the source range.

How to Make a PivotChart: A Step-by-Step Guide

You can't create a PivotChart without first creating a PivotTable. Let's walk through that process first, then we'll add the chart.

Step 1: Create a PivotTable

With clean data, this part is easy.

1. Click any single cell inside your data table.
2. Go to the Insert tab on the Ribbon and click PivotTable.
3. A dialog box will appear. Excel usually autodetects your data range (especially if you formatted it as a Table). Confirm that the range is correct and choose to place the PivotTable in a New Worksheet. Click OK.

You’ll now see a blank PivotTable on a new sheet and the PivotTable Fields pane on the right side of your screen. This pane is your control center, and it’s where you’ll build your summary. It’s organized into four areas:

• Filters: Use this to apply a high-level filter to your entire table.
• Columns: Fields dragged here will become the column headers of your table.
• Rows: Fields dragged here will appear as rows down the side of your table.
• Values: This is for fields you want to calculate (like sum, count, or average). This is almost always a numeric field, like Revenue or Units Sold.

Let's build a simple summary. From the Field List, drag and drop the fields into these areas:

• Drag Region into the Rows area.
• Drag Product into the Columns area.
• Drag Revenue into the Values area. Excel will automatically default to Sum of Revenue.

Instantly, you have a tidy summary table showing the total revenue for each product, broken down by region.

Step 2: Insert the PivotChart

Now for the fun part. With your PivotTable built, adding the chart takes just a few clicks.

1. Click anywhere inside your newly created PivotTable.
2. Go to the PivotTable Analyze tab on the Ribbon (this tab only appears when a PivotTable cell is selected).
3. In the Tools section, click PivotChart.
4. The Insert Chart dialog box will open. Here you can choose from various chart types like Column, Bar, Line, Pie, and more. For this data, a Clustered Column chart is a great starting point.
5. Select your preferred chart style and click OK.

Voila! Excel automatically generates a chart that visually represents your PivotTable. You'll immediately notice interactive grey buttons on the chart itself for Region and Product — these are live filters you can use directly on the chart.

Customizing Your PivotChart to Build a Dashboard

Your basic PivotChart is now ready, but the real power comes from formatting and adding interactive controls. This is how you transform a simple chart into a user-friendly dashboard.

Fine-Tune Chart Elements

When you select your chart, a + icon appears on the top right. Click it to add, remove, or modify key chart elements:

• Chart Title: Give your chart a meaningful title. "Sum of Revenue" is descriptive but not very professional. Try "Revenue by Product and Region."
• Data Labels: Add the actual numeric values on top of or inside the columns to make the chart easier to read.
• Axis Titles: Label your X and Y axes for clarity.
• Legend: The legend (showing which color represents which product) is already there, but you can move it to the top, bottom, or side.

Using Slicers for Interactive Filtering

While the field buttons on the chart work for filtering, Slicers are much more elegant and intuitive. A Slicer is basically a set of large, interactive buttons you can use to filter your report.

1. Click on your PivotChart to select it.
2. Go to the PivotChart Analyze tab.
3. Click Insert Slicer.
4. A dialog box will show all the fields from your source data. Check the box for Sales Rep and click OK.

A floating Slicer box will appear with a button for each sales rep. Now, click on a rep's name - watch as both the PivotTable and PivotChart instantly filter to show data for only that individual. You can select multiple reps by holding down the Ctrl key. This is the cornerstone of building a dynamic Excel dashboard.

Using Timelines for Filtering by Date

If your data includes a date field, a Timeline is the perfect tool for filtering by time periods. It's similar to a Slicer but designed specifically for dates.

1. Click on your PivotChart.
2. Go back to the PivotChart Analyze tab and click Insert Timeline.
3. Check the box for your date field (in our example, OrderDate) and click OK.

A Timeline control will appear, letting you easily filter your data by years, quarters, months, or even specific days with a simple slider. It’s an incredibly professional way to analyze trends over time.

Refreshing Your Data

Remember, your PivotChart is based on your original source data. If you add new sales records to your source table, a PivotChart won’t see them automatically. You need to refresh it.

To do this, simply right-click anywhere on the PivotTable or PivotChart and select Refresh. If you used the Ctrl + T Table feature, all new data will be pulled in and your visuals updated instantly.

Final Thoughts

Mastering PivotCharts means moving beyond static reports and toward making live, explorable data analysis accessible to anyone. By preparing your data correctly and pairing your chart with interactive tools like slicers and timelines, you can build powerful dashboards that reveal insights with just a few clicks.

While Excel is fantastic for deep-dive analysis on a single dataset, bringing together data from different platforms — like Google Analytics, Salesforce, and your ad accounts — can feel like a constant battle of exporting CSVs. To solve this, we built Graphed . It allows you to connect all your marketing and sales data sources in one place, then use simple, natural language to create the exact real-time dashboards you need in seconds, freeing you from the manual work so you can focus on the insights.