Pivot charts are an extension of pivot tables. A pivot chart is always linked to a pivot table, and you can’t change one without changing the other. To illustrate this connection, I’ll start by creating a new pivot table and pivot chart. The first thing to notice is that both pivot tables and pivot charts change Excel’s interface when selected. When you select a pivot table, you’ll see the PivotTable Tools menu appear on the ribbon, with Analyze and Design tabs. When you select a pivot chart, you’ll see the PivotChart Tools menu appear on the ribbon, with Analyze, Design, and Format tabs. The Analyze tab has controls for naming the pivot chart, accessing chart options, field settings, controls for inserting slicers and timelines, refresh and data source settings, clearing and moving the chart, calculated fields and items, and controls to toggle on and off the field list and field buttons. The Design and Format tabs for a pivot chart are the same as you’ll see when you select a regular chart. As with many other things in Excel, you can access several useful commands by right-clicking a pivot chart. Both a pivot table and pivot chart trigger the field list you’ll see when a pivot table is selected. When I click away, this list disappears, and when I click back it reappears. Notice the title of the window and the names in the field list are slightly different when the chart is selected. Row Labels are called Axis fields, and Column Labels are referred to as Legend Fields. This is just to make it easier to understand how pivot table fields are connected to the chart. Any change I make the to the pivot chart is reflected in the pivot table and vice versa. If I add a field to the columns area in the pivot table, it shows up in the Legend of the chart. In fact, I can use the Switch Row/Column button, common to all charts, to swap fields in both pivot chart and pivot table. In most respects, a pivot chart is just like any other chart. You can format axes, and remove elements, change formatting, and so on. One key difference is that pivot charts don’t have the Chart Filter menu you normally see when a chart is selected. To filter data in a pivot chart, you can work directly field buttons (if they are visible) or by filtering the pivot table itself. Now if you delete a pivot table connected to a pivot chart, the chart reverts to a normal chart. You’ll see the data has been stored in the SERIES formulas in the chart.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
