After you create a pivot table in Microsoft Excel, you can insert a pivot chart, based on that table. Then, if necessary, you can rearrange the field layout, apply filters, or change one of the series, to make a combination column line chart. Create a dynamic chart title based on a pivot table report filter.
After you create a pivot table, you can insert a pivot chart, based on that pivot table.
In this example, the chart shows sales data, per city, over two years. At first, all the chart columns are the same color, because there is only one series. To create columns with different colours, the field arrangement is changed.
Watch this short video, to see the steps.
Change the Pivot Chart Layout
When the chart was created, the Year and Region fields were both in the Rows area. The chart had only one series, so all the columns were the same color.
To create columns with different colors, move one of the field names into the Column area in the PivotTable Field List.
To move a field, follow these steps:
That creates a series for each year, and shows one year in blue, and the other in orange.
In the next screen shot, the City field has been moved to the Legend (Series) box, with OrderYr remaining in the Axis (Categories) box.
That creates a series for each city, and shows city's columns in a different color..
If you rearrange the fields in a pivot chart layout, the related pivot table changes too. It doesn't matter if the pivot chart is on a new sheet, or the same worksheet as the pivot table - they stay connected.
Unfortunately, there’s no setting you can change if you want the pivot chart and pivot table to work independently.
Tip: If the source data changes, you can refresh either the pivot table or the pivot chart, and both will update. Right click on the chart, away from the columns and plot area, and click the Refresh command.
As a workaround, you can create a second pivot table in your Excel workbook, based on the first one, and arrange it as you’d like. Then, when you change the pivot chart, only the original pivot table is affected.
In the screen shot below, the pivot chart is based on a different pivot table, from the same source table, on the HiddenPT sheet.
Watch this short video, to see an example of this workaround.
Watch this short video, to see how to create a column line pivot chart, with the line on a secondary axis. Written iinstructions are below the video.
Follow these steps to create a combination column line pivot chart, based on an existing pivot table.
Create a Pivot Chart
Change to Combination Chart
Create and Filter Pivot Charts
In this video you can see how to create and filter an Excel pivot chart, by using the field buttons that are on the chart.
There are written steps, and a full transcrips, below the video.
If you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript
Video Transcript: Use Field Buttons to Filter Pivot Chart
Pivot charts have improved in Excel 2010 and I'm going to show you the new filtering feature which is much easier than it was in Excel 2007.
Insert Pivot Chart
To insert a pivot chart, I'm selecting a cell in the pivot table.
And then, on the Ribbon, under Pivot Table Tools, I'll click Options.
And in the Tools group, click PivotChart
I'm going to select a Column chart type
And the first one here, which is the Clustered Column
And click OK
That inserts a chart right on the active worksheet.
I can point to one of the corners of the chart and make it a bit smaller
Filter Pivot Chart
And to filter now, I can use any of the field buttons, that are on the chart.
So if I only want to see a specific product or date, then I can click the arrow and filter right in the pivot chart.
So perhaps I just want to see April 7th
And that filters both the chart and the pivot table.
Remove Pivot Chart Filters
To remove the filters, I can go back, and click All
And that shows all the data again.
Show or Hide Field Buttons
There's also a setting on the Ribbon, if you go to PivotChart Tools, and click Analyze.
There is a Field Buttons setting, that you can show or hide specific buttons, that are on the chart, or hide all of them.
How to Filter Pivot Chart
After you create a pivot chart, you can use the chart's field buttons to filter the data that the chart displays.
To apply a pivot chart filter, follow these steps:
Show or Hide Field Buttons on Pivot Chart
For some pivot charts, you might want to remove some, or all of the field buttons.
To toggle the pivot chart field buttons between the Hide and Show settings, follow these steps:
Types of Pivot Chart Field Buttons
There are four different types of field buttons for pivot charts:
This screen shot shows the four field button types in an example pivot chart.
Show/Hide Specific Field Buttons
To show/hide specific field buttons, click the bottom part of the Field Buttons command, which has a drop down arrow.
The drop down menu opens, and the following field button options are listed:
Change Field Button setting
Some of the field button settings might have a check mark, and some might not.
To change a field button setting:
Chart Title from Report Filter
Instead of adding a static title to your Pivot Chart, use a worksheet formula to create a dynamic chart title. Watch this video to see how to set up the formula, and then link the chart title to the formula cell.
Download the sample file in the next section, to follow along with the video. The written steps are below the video.
Pivot Table With Report Filter
In this example, there is a pivot table that shows quantities sold over 2 years, in four different food categories.
In the steps below the screen shot, you'll see how to:
Create a Pivot Chart
To create a pivot chart from the food sales pivot table, follow these steps:
A pivot chart is added to the worksheet, showing the 2 years of data.
Add a Chart Title
There’s no title on the chart, so follow these steps to add a title:
Move the Chart Title
A generic title was added to the pivot chart, with the text “Chart Title”. You'll change the title later, so it shows the region name.
For now, you'll just move the title to a better location in the chart.
Pivot Table Region Filter
There are four regions in the food sales data – East, West, Central and South.
First, check that the “Select Multiple Items” option is turned on, so you can select one or more of the regions.
Then, based on what you selected in the Report Filter, cell C2 will show one of these results:
In the screenshot below, Central was selected in the Region filter, and its name appears in cell C2.
Chart Title Text Options
Instead of typing a new chart title in the Title box, you can create a chart title formula, in a cell on the worksheet.
That formula will help you build a dynamic chart title, that will show details on what was selected in the Region filter.
In this example the formula will create a chart title that:
Here is the Region information that should appear in the pivot chart title:
Create the Formula
To check for those 3 Report Filter options, you’ll build a nested IF formula.
Enter the following formula in cell G2 (I’ve added line breaks, so it’s easier to read):
That formula starts with the text string =”Annual Sales — ”
Format and Move Formula Cell
Next, you can follow the steps below, to make the formula result look like a worksheet title.
Link Chart Title to Formula Cell
The final step is to link the pivot chart title to the formula cell.
Follow these steps to do this final step:
The pivot chart title now shows the result of the formula in cell G1.
Test the Pivot Chart Title
To make sure that the formula is working correctly, make a few changes with the Region Report Filter:
Next, select (All) in the Region Report Filter, to show data for all the regions. The chart title should change to “Annual Sales – All Regions”.
To make the pivot chart look even better, you can add a couple of final touches:
Then, follow these steps to hide some or all of the field buttons:
Then, move the Chart Title and Legend, if necessary, so they fit in the blank space above the columns in the chart plot area.
Tip: You could also add a Pivot Table Slicer for the Region field, so it's easy for people to select a region. Leave the Region field in the Report Filter area too, so the formula can get the information from that cell.
Pivot Chart Title: To follow along with the Pivot Chart Title video, download the sample pivot chart tutorial file. The sample file contains the completed formula and pivot chart. You can delete those, and build your own. The zipped Excel file is in xlsx format, and does not contain any macros.
Last updated: April 25, 2023 3:41 PM