Pivot Charts in Excel: Charts That Follow the Data
A pivot chart is a chart wired to a pivot table. Change the grouping, change the filter, refresh the data — the chart follows. The trick is knowing what it will not do, because that is where the wrong number gets published.
Short answer
The link is the point
A normal chart is bound to a range. If a month is added to the data, the range is one column short and the chart silently omits the newest month — the failure mode this whole approach exists to remove.
| Normal chart | Pivot chart | |
|---|---|---|
| Bound to | A cell range | A pivot table |
| A new month of data arrives | The range may not include it | It appears on refresh |
| Grouping changes | Rebuild the chart | Change the pivot field layout |
| Filter applied | Filter the underlying cells | Filter the pivot table, or a slicer |
| Show a different measure | Repoint the series | Drag a different field into Values |
| Where it breaks | Silently, when the range is wrong | Visibly or not at all — but it can only show what the pivot table holds |
Which chart for which question
| The question | Chart | Why |
|---|---|---|
| How has this moved over time? | Line | The eye reads slope well; a line makes the direction obvious |
| Which categories are biggest? | Horizontal bar, sorted descending | Long category names like vendor or ledger head stay readable, and sorting makes the ranking instant |
| What share of the total is each part? | Stacked bar, or a pie for three to five slices only | Beyond five slices a pie cannot be compared visually |
| How do two measures move together? | Combo chart with a secondary axis | Amounts in lakhs and quantities in hundreds need different scales |
| How does the mix change month to month? | Stacked column | Shows both the total and the composition moving |
| Where is the concentration risk? | Bar with a cumulative percentage line | The curve shows how few categories make up most of the value |
The pie chart rule
Building one
Build the pivot table first
Insert the chart
Turn the field buttons off
Sort by value, not by name
Set the axis number format
Move it to where it will be read
The blank month gap
This is the failure readers notice first, and it has two separate causes that look identical.
| Situation | What the chart shows | The fix |
|---|---|---|
| The month exists in the data but every amount is blank | A point plotted at zero, or a gap depending on the chart | PivotTable Options → Layout & Format → For empty cells show: 0 |
| The month does not exist in the data at all | A missing point, and a line that jumps across it | Enable Show items with no data on the row field, then set empty cells to 0 |
| The month is present but as text in some rows | A separate text group appears alongside the real months | Fix the date column — grouping only works on real dates |
| The period column was grouped by calendar year | March and April sit in different years | Add a financial year column to the source and group by that |
Show the gap, or show the zero — but decide
One slicer, several charts
This is the step that turns a sheet of charts into something people use rather than read.
Build the pivot tables you need
Build a chart on each
Insert one slicer
Connect it to every pivot table
Add a timeline if there are real dates
Line the charts up on one sheet
| Control | What it looks like | Best when |
|---|---|---|
| Report filter | A drop-down above the pivot table | One person uses the file and knows what they set |
| Slicer | A panel of buttons showing the current selection | Somebody else reads the file and needs to see the filter state |
| Timeline | A date-range slider | The data has real dates and the question is a period |
| Slicer with Report Connections | One panel controlling several charts | A dashboard with more than one chart on it |
The traps
- A value field set to Count. The chart looks plausible and the numbers are wrong. Check the pivot table, not the chart.
- Twelve months in Columns on a bar chart. The chart has one series per month and becomes unreadable. Months belong on the axis, not in the legend.
- Too many categories. A bar chart of ninety vendors shows nothing. Filter to the top ten by value, and report the rest as a single “other” row.
- Formatting the cells underneath. It is lost on refresh. Axis and series formats set on the chart itself survive.
- A chart on a hidden or working sheet. Nobody sees it, and when the workbook is reviewed nobody knows it exists.
- Deleting the pivot table and keeping the chart. They are one object in practice; removing the pivot table removes the chart's source.
- Assuming the chart refreshed because the pivot table did. They refresh together, but only when one of them is refreshed. Confirm the pivot table looks right before sending the chart.
What the chart cannot tell you
A chart answers “how much” and “which is bigger”. It does not answer “is this right”. The total plotted on the axis is the total of the rows in the pivot table, and if those rows came from an unreconciled comparison, the chart is an accurate picture of a wrong number.
Where the underlying question is a comparison — what the vendor says against what the books say, what the return says against the ledger — the figures should be reconciled before they reach a pivot table. That is the step Piloteq Automate is built for, and the reason reconciliation comes before reporting in any month-end routine.
If you would rather not build this by hand
When the chart needs a reconciled figure underneath
A pivot chart is only as good as the data in its pivot table. Piloteq Automate produces the reconciled, agreed numbers first — matching two records sets with the rules you set and listing every exception — so the chart shows a figure you can defend.
- ✓Saved rules applied the same way each period
- ✓Amount and date tolerances you control
- ✓Exceptions listed with a reason
- ✓Results that feed straight into a summary
Frequently asked questions
What is the difference between a chart and a pivot chart?+
A normal chart is bound to a fixed range of cells. A pivot chart is bound to a pivot table: when you change the pivot table's fields, filters or grouping, the chart changes with it. That link is the whole advantage — and the reason the chart cannot show something the pivot table does not contain.
Why does my pivot chart show a gap for a month with no data?+
Because the month does not exist in the source data at all, so the pivot table has no row for it and the chart plots a gap. Two settings fix it: in PivotTable Options → Layout and Format, set For empty cells show to 0, and enable Show items with no data on the row field. The first handles months present but blank; the second handles months missing entirely.
How do I connect a slicer to more than one pivot chart?+
Right-click the slicer and choose Report Connections, then tick every pivot table the slicer should control. Each pivot chart built on one of those pivot tables then follows the same selection. This is the mechanism that turns a page of separate charts into a dashboard.
Why does my pivot chart formatting keep disappearing?+
Formatting applied to the chart area, the plot area and the series usually survives; formatting applied in a way that depends on the current field layout can be lost when the fields change. If you need a specific look preserved, apply formatting to the chart itself rather than to cells, and avoid re-arranging the fields after formatting — or save the formatting as a chart template and reapply it.
Should I use a pie chart for expense categories?+
Only when there are three to five categories that add up to a meaningful whole. Beyond about five slices the reader cannot compare them, and the smallest slices are indistinguishable. A sorted horizontal bar chart with the values labelled answers the same question far more clearly, and it survives categories being added next month.
Can a pivot chart show two different measures, such as amount and quantity?+
Yes, but they need a combo chart with a secondary axis, because a quantity in the hundreds plotted against an amount in the lakhs will make one series invisible. Right-click the series, choose Change Series Chart Type, set that series to a line and tick Secondary Axis.