TL;DR
- A pivot chart is the visual version of a PivotTable, and the two stay linked: change one and the other follows.
- On Windows, click a cell in your data, then Insert > PivotChart. On Mac, build the PivotTable first, then insert a chart from it.
- Clean source data (headers in row 1, no blank rows, one data type per column) prevents most problems.
- Pivot charts exclude scatter, stock and bubble types, and some manual formatting can disappear on refresh.
- Practice builds the skill faster than reading, so try each step on a real sheet.
Table of Contents
What a pivot chart is and how it relates to a PivotTable
According to Microsoft's overview of PivotTables and PivotCharts, a PivotChart is an interactive graphical representation of the data summarized by a PivotTable. The two are connected. Change the layout or data in the PivotTable and the chart reflects it immediately, and the reverse is true as well.
Think of the PivotTable as the engine and the pivot chart as the dashboard gauge. The table does the grouping and adding up: sales by region, hours by project, tickets by month. The chart turns those totals into columns, lines or pies that people understand in seconds.
If you have never built a PivotTable, spend ten minutes on that first. Our short lesson, PivotTables: a summary in thirty seconds, shows the idea in a few clicks, and Working with PivotTables goes further into reporting.
Pivot chart vs. regular chart
A regular chart plots cells you point it at. A pivot chart plots a PivotTable. That sounds small, but it changes how you work:
| Regular chart | Pivot chart |
| Data source | Any cell range you select | A PivotTable (or a PivotTable Excel builds for you) |
| Filtering | Manual, or by hiding rows | Filter buttons on the chart and the PivotTable field list |
| Changing what is shown | Edit the range in Select Data Source | Drag fields in the PivotTable Fields list |
| Chart types | All types | No scatter, stock or bubble |
| Best for | A fixed set of numbers | Data you want to slice many ways |
The limits in that last rows come from Microsoft's own description, which we cover below.
Before you start: prepare your data
Most pivot chart frustration comes from the source data, not the chart. Microsoft recommends worksheet data with column labels in the first row, a single data type in each column, and no blank rows or columns in the range, as described in its PivotTable overview. The same page notes that an Excel Table is a suitable source, because refreshing the PivotTable automatically picks up new and updated rows in that Table.
A quick checklist before you insert anything:
- Give every column a unique, short header (Region, Product, Order Date, Revenue).
- Make sure numbers are stored as numbers, not text. A column of amounts that left-aligns is a warning sign.
- Delete blank rows and fully empty columns inside the range.
- Remove subtotal rows and grand totals from the data. The PivotTable will calculate its own.
- Convert the range to a Table with Ctrl+T so it grows as you add records.
How to create a pivot chart in Excel
There are two routes, depending on whether you already have a PivotTable.
From your raw data (Windows)
Microsoft's Create a PivotChart page gives these steps for Windows:
- Select a cell in your source table.
- Choose Insert > PivotChart.
- Choose where the PivotChart should go (a new worksheet is the cleanest choice for a first attempt).
- Select OK.
- In the field list, select the fields you want to display.
You do not strictly need to build a PivotTable by hand first. Microsoft states that in Excel you can create an interactive PivotChart
without creating a PivotTable first. Excel still builds the supporting table behind the scenes, and you will see it if you look at the sheet.
From an existing PivotTable
If the summary table is already on the sheet, select any cell in it, choose Insert > PivotChart, pick a chart type, and select OK. This is the route most people take, because it lets you shape the numbers first and then chart them.
On a Mac
The order differs. Microsoft says that on Mac you first create the PivotTable, select a cell within it, then insert a column, line, pie or radar chart, and change or move fields in the PivotTable Fields list afterward. The steps are on the same Create a PivotChart page. Menus shift slightly between Excel versions, so if a button is missing, look under the Insert tab and the PivotTable Analyze tab.
A worked example
Say you have an Excel Table with 2,000 rows of orders: Order Date, Region, Product, Salesperson and Revenue. You want to know which region brings in the most revenue and how it splits by product.
- Click inside the Table and choose Insert > PivotChart.
- Place it on a new sheet.
- Drag Region to Axis (Categories), Revenue to Values, and Product to Legend (Series).
- Choose a clustered or stacked column chart.
You now have one column per region, with each column divided by product. Drag Order Date to Filters and you can show a single quarter or year. Nothing in the source data has changed; you only changed the view.
Add, rearrange and remove fields
The PivotTable Fields list is where a pivot chart gets its flexibility. Fields appear in four areas: Filters, Axis (Categories) or Rows, Legend (Series) or Columns, and Values.
- Add a field: tick its box in the list, or drag it into an area. Text fields usually land in Rows or Axis, and numeric fields usually land in Values.
- Rearrange: drag a field from one area to another. Moving Product from Legend to Axis turns a stacked column chart into a chart with products along the bottom.
- Remove a field: untick its box or drag it out of the list. The data is not deleted, it simply stops being used.
- Change the calculation: click the field in Values and choose Value Field Settings to switch between Sum, Count, Average and others.
Because chart and table are linked, every one of these moves updates both. If a chart suddenly looks wrong, check the field list first. A numeric field set to Count instead of Sum is a very common cause of a chart that shows tiny, odd values.
Need a metric that does not exist in your data, such as profit margin or revenue per unit? Our guide to a calculated field in a pivot table shows how to create, edit and fix one so the chart can plot it.
Filter and sort what the chart shows
Microsoft notes that you can use the PivotChart Filter Pane to sort and filter the underlying data, and that changes in the PivotTable layout and data carry through to the chart, as the overview page explains. In practice that means three tools:
- Field buttons on the chart. Click the arrow on a button such as Region and untick the items you want hidden.
- The Filters area. Place a field like Year there to switch the whole chart between periods.
- Slicers and timelines. These are clickable buttons and a date bar. Microsoft's dashboard guidance says they let dashboard users filter PivotTables and charts quickly, and that you can refresh when data is added or updated, so you only build the report once.
Computerworld's
walkthrough of PivotTables and PivotCharts shows the live link nicely: collapse or filter categories in the PivotTable and the matching chart changes with it.
Refreshing after the data changes
A pivot chart does not recalculate the moment you edit a source cell. Right-click the PivotTable and choose Refresh, or use Data > Refresh All. If your source is an Excel Table, new rows are included on refresh, which is the main reason to convert your range to a Table before you begin.
What pivot charts cannot do
Knowing the limits saves you an afternoon. Per Microsoft's overview:
- A pivot chart cannot change its source range through Select Data Source, or switch row and column orientation there. You make those changes in the PivotTable.
- XY scatter, stock and bubble chart types are not available.
- Some specialized formatting, such as trendlines, data labels and error bars, may not survive a refresh.
On the plus side, the same page says pivot charts show series, categories, data markers and axes just like standard charts, and you can change the chart type, titles, legend placement, data labels and location.
Practical workarounds:
- Finish your PivotTable layout before you polish the chart formatting, so you do not redo it.
- Pick a chart type that suits the data. Columns compare categories, lines show change over time, and pies work only when there are a few slices adding up to a whole.
- If you need a scatter plot, build it from a regular chart on copied values instead.
Make a pivot chart readable
A pivot chart that is technically correct can still be hard to read. A few habits help:
- Limit categories. Twenty columns invite squinting. Filter to the top items or group dates by month or quarter.
- Sort the axis. Descending by value makes rankings obvious.
- Remove clutter. Hide field buttons you do not need and delete gridlines that add nothing.
- Title the chart with the finding, not the field names: "West region leads revenue" says more than "Sum of Revenue".
- Be consistent with color. If Product A is blue in one chart, keep it blue everywhere.
From one chart to a dashboard
Once you have two or three pivot charts, the natural next step is to place them on one sheet with slicers that control them all. One practical point from Microsoft's dashboard guidance: PivotTables cannot overlap each other, so leave enough room for them to expand or shrink as values are filtered, added or removed.
For layout principles, an Excel KPI dashboard design guide is a good companion for deciding which metrics deserve space and how to arrange them. For the Excel mechanics, see our own walkthrough on how to build dashboards in Excel.
Common problems and quick fixes
| Problem | Likely cause | Fix |
| Chart shows counts instead of totals | Numeric field set to Count | Value Field Settings > Sum |
| New rows are missing | Source is a plain range, or no refresh | Convert to a Table, then Refresh All |
| Formatting vanished after refresh | Manual trendline or label formatting lost | Reapply, or format at the chart-type level after the layout is final |
| Dates appear as separate days | Dates not grouped | Right-click a date in the PivotTable > Group > Months, Quarters, Years |
| Field list missing | Nothing selected | Click inside the chart or PivotTable |
| Blank category in the chart | Empty cells in the source column | Fill the blanks or filter out (blank) |
Which should you use: PivotTable, pivot chart or formulas?
A PivotTable gives exact figures you can scan and copy. A pivot chart helps when you need to show a pattern to someone else. Formulas such as SUMIFS are better when you need one specific number in one specific cell, for example inside a report template. Many analysts use all three: formulas for fixed report lines, a PivotTable for exploring, and a pivot chart for the picture that goes in the slide or the dashboard.
Our perspective
> We built LogicExcel around one belief: you learn Excel by doing it, not by watching someone else click. Pivot charts are a perfect example. The steps fit on a card, but fluency comes from dragging a field into the wrong area, seeing the chart break, and understanding why.
>
> That is why our exercises give instant feedback in an Excel-like environment and explain each mistake instead of just marking it wrong. There is no account to create and nothing to install, so you can open an exercise, try a formula or a layout, and learn from the result in the same minute.
>
> If you are an analyst tidying monthly reports, a manager who wants to build your own summaries, or a career changer who needs practical fluency, start with clean data and one question you want answered. Build the PivotTable, chart it, then break it on purpose. That habit is what turns a tutorial into a skill.
>
> Thomas Houza
Practice pivot charts for free
Reading about field lists only goes so far. Our 95 free Excel exercises let you type formulas and get instant feedback with no sign-up, which builds the muscle memory that pivot work depends on. If you are newer to spreadsheets, start with Excel exercises for beginners.
Want a structured path? The Excel courses run from Excel I to Excel IV, and the free Excel test for job interviews helps you check yourself before an assessment that includes PivotTables.
FAQ
What is the difference between a pivot chart and a PivotTable?
A PivotTable is a grid that summarizes your data into totals, counts or averages. A pivot chart is the graph of that summary. They are linked, so changing the layout of one updates the other.
Do you need a PivotTable to create a pivot chart?
Not always. Microsoft says Excel can create an interactive PivotChart without you creating a PivotTable first. On Mac, though, the documented steps start with a PivotTable, so building the table first is the safest route on both systems.
Where do I find the pivot chart in Excel?
Click a cell in your data, open the Insert tab and choose PivotChart. Once a pivot chart is selected, extra PivotChart Analyze and Design tabs appear on the ribbon, and the field list opens on the side.
Why can't I choose a scatter chart for my pivot chart?
Pivot charts exclude XY scatter, stock and bubble types. If you need one, copy the summarized values to a new range and build a regular chart from them.
Is it better to use VLOOKUP or a PivotTable?
They do different jobs. VLOOKUP retrieves a matching value for a row, while a PivotTable summarizes many rows into groups. If your goal is a chart of totals by category, a PivotTable and pivot chart are the right tools.
Sources
