Concept
1. Create one
Click inside a pivot → PivotTable Analyze → PivotChart → Clustered Column → OK. (Or Insert → PivotChart directly from the Table to create both at once.)
The chart is the pivot drawn: change the pivot, the chart follows; filter with a slicer, the chart follows.
2. Pivot layout → chart layout
| Pivot area | Becomes in the chart |
|---|---|
| Rows | categories along the axis |
| Columns | separate series (legend, colours) |
| Values | bar/line heights |
| Filters / slicers | what's included |
Region in Rows + Category in Columns → clustered columns: 3 regions, 2 colours. Move Category to Rows under Region → one series with nested axis labels.
3. Choosing the type
- Trend over months → Line (Date grouped by Month in Rows).
- Compare regions → Column or Bar (sort the pivot largest to smallest first: right-click → Sort).
- Share of a whole with 2–4 parts → maybe a pie (but read Module 5 first). Change anytime: PivotChart Analyze/Design → Change Chart Type.
4. Clean it up
- Hide field buttons: PivotChart Analyze → Field Buttons → Hide All (they look messy in dashboards; slicers do the filtering).
- Delete gridlines, add data labels, give a title that states the point ("North brings 48% of sales").
- Number format of labels follows the pivot's number format — set it in Value Field Settings → Number Format.
5. Interactive dashboard pattern
[ Region slicer ] [ Category slicer ] [ Timeline ]
[ Sales by Month - line ] [ Sales by Region - bar ]
[ Top products - bar ] [ KPI cells (GETPIVOTDATA, Lesson 7) ]
Connect every slicer to every pivot (Lesson 5), place the pivots themselves on a hidden "calc" sheet, and keep only charts and slicers on the dashboard sheet.
6. Limits
- Not available as pivot charts: scatter, bubble, stock, histogram, box & whisker, waterfall, treemap, sunburst, funnel, map. Workaround: make a normal chart from cells that reference the pivot (or from a dynamic array, Module 6).
- Pivot charts always show all items in the pivot — use pivot filters (e.g. Value Filters → Top 10) to limit them.
- Some formatting resets on refresh if the series changes; apply styles after the layout is final.
Common mistakes
Leaving field buttons and default "Total" titles in dashboards. Unsorted bar charts. Expecting a pivot scatter chart.