Home > Pivot > Charts > Pivot Chart

How to Change a Pivot Chart

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.

Pivot Chart Layout

After you create a pivot table, you can insert a pivot chart, based on that pivot table.

• Note: If you need help with creating a pivot chart, there are step-by-step instructions on the Pivot Chart Source Data page.

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:

1. Click on the Pivot Chart, to select it.
2. In the PivotChart Fields window, drag the OrderYr field from the Axis box (Categories) to the Legend (Series) box.

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..

Changing Pivot Chart Layout Affects Pivot Table

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.

• Put the pivot table that’s connected to the pivot chart on a separate sheet, or on a new worksheet, so it isn't visible.
• Then, use the second pivot table, that isn't connected to the pivot chart, for printing your reports.

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.

Create a Column Line Pivot Chart

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

1. Select any cell in the pivot table
2. On the Excel Ribbon, click the Insert Tab
3. In the Charts group, click Column, then click Clustered Column

   

4. A column chart is inserted on the worksheet, and it is selected -- there are handles showing along the chart's borders.

   

Change to Combination Chart

1. Right-click on the chart, and click Change Chart Type
2. In the Change Chart Type window, at the left, select the Combo category.
3. At the top of the window, click the Clustered Column - Line on Secondary Axis option

   

4. If you want to change the series that shows a Line, or is on the Secondary Axis, select those settings at the bottom of the window.

   

5. When finished, click OK, to see the modified pivot chart on the worksheet.

   

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.

Video Transcript

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

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:

• In the pivot chart, click the drop down arrow on the field button you want to use
  • In the screen shot below, I clicked the arrow on the Region field button
• A pop-up menu appears, with commands for sorting and filtering the pivot chart
• At the bottom of the menu, in the list of pivot items:
  • add a check mark, for any item you want to show in the pivot chart
  • remove the check mark, for any item you want to hide in the pivot chart
• After making your selections, click the OK button, to close the menu and apply the selected filters

Show or Hide Field Buttons on Pivot Chart

For some pivot charts, you might want to remove some, or all of the field buttons.

• Why Hide?: If you want the pivot chart to stay in its current configuration, hiding the field buttons can deter other people from making changes to the pivot chart.
• Why Show?: If you want other people to freely change the pivot filters, leave all of the field buttons showing.

To toggle the pivot chart field buttons between the Hide and Show settings, follow these steps:

• Click anywhere on the pivot chart, to select it
• On the Excel Ribbon, under PivotChart Tools, go to the Show/Hide group, at the far right
• Next, click the top or bottom part of the Field Buttons command
  • Top: If you want to show/hide all of the field buttons at once, click the top part of the Field Buttons command
  • Bottom: If you want to show/hide specific field buttons, click the bottom part of the Field Buttons command, which has a drop down arrow. There are details for this command in the section below the screen shot.

Types of Pivot Chart Field Buttons

There are four different types of field buttons for pivot charts:

1. Report Filter Field Buttons
2. Legend Field Buttons (pivot table Column fields)
3. Axis Field Buttons (pivot table Row fields)
4. Value Field Buttons

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:

1. Show Report Filter Field Buttons
2. Show Legend Field Buttons (pivot table Column fields)
3. Show Axis Field Buttons (pivot table Row fields)
4. Show Value Field Buttons
5. Hide All

Change Field Button setting

Some of the field button settings might have a check mark, and some might not.

• If a setting has a check mark, it is currently turned ON
• If a setting has NO check mark, it is currently turned OFF

To change a field button setting:

• Click on a field button setting option, to toggle that setting
  • from ON to OFF
  • or from OFF to ON

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.

Video Timeline

• 00:00 Intro
• 00:19 Add a Pivot Chart
• 01:14 Add a Chart Title
• 01:57 Region Filter
• 03:01 Create a Formula
• 03:22 Formula Rules
• 04:02 Start the Formula
• 06:52 Link Title to Formula Cell
• 07:51 Get the Sample File

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.

• There is one field, Region, in the Report Filter area
• Select a region name from the filter's drop down list, to see sales for that region.

In the steps below the screen shot, you'll see how to:

• Create a chart from this pivot table
• Show the selected region's name in the chart title

Create a Pivot Chart

To create a pivot chart from the food sales pivot table, follow these steps:

• Select a cell in the pivot table
• On the Excel Ribbon, click the Insert tab
• In the Charts group, click Recommended Charts
• Click OK, to accept the recommended chart layout – a Clustered Column chart.

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:

• With the pivot chart selected, click the Design tab on the Excel Ribbon
• At the left, click Add Chart Element
• Then, click Chart Title, and click the “Centered Overlay” option
  • That adds a title, without making the plot area smaller

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.

• Drag the chart title up, to position it in the blank space above the plot area

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:

• (All) – if no filter has been applied yet, or if (All) was selected
• Region Name – if a single Region was selected
• (Multiple Items) – if 2 or more Regions were selected, but not all Regions

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:

• starts with the text, “Annual Sales — “
• followed by information from the Report Filter cell, C2.

Here is the Region information that should appear in the pivot chart title:

• If cell C2 contains "(All)" – show the text “All Regions”
• If cell C2 contains a Region Name – show the selected Region name
• If cell C2 contains "(Multiple Items)" – show the text “Multiple Regions”

Create the Formula

To check for those 3 Report Filter options, you’ll build a nested IF formula.

• The formula cannot go directly into the chart title,
• First, you’ll build the title formula in a worksheet cell
• Next, you'll link the chart title to the formula cell.

Enter the following formula in cell G2 (I’ve added line breaks, so it’s easier to read):

• =”Annual Sales — ” &
  IF(C2=”(All)”,”All Regions”,
  IF(C2=”(Multiple Items)”,”Multiple Regions”,C2))

That formula starts with the text string =”Annual Sales — ”

• Next, if cell C2 contains “(All)”, the formula result ends with “All Regions”
• Or, if cell C2 contains “(Multiple Items)”, the result ends with “Multiple Regions”
• Otherwise, show the selected Region name, from cell C2

Format and Move Formula Cell

Next, you can follow the steps below, to make the formula result look like a worksheet title.

• First, format the formula cell to make it bold font, and a bigger size.
• (optional) Move the formula up to the top row
  • I've moved the formula up to cell G1
• Then, move the chart up, so it is just below the formula cell.
• Or, if you don’t want to see the worksheet formula, move the chart up to cover cell G2

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:

• Click on the chart’s title, to select it.
  • NOTE: Don’t click inside the chart title, just on the border.
  • If you see the cursor flashing in the text, click on the title border, to exit the text editor.
• Next, click in the Formula bar, and type an Equal Sign (=)
• Click on cell G1, which contains the pivot chart title formula
• Finally, press Enter, to complete the link formula in the chart title

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:

• First, select an additional Region, so that cell C2 shows “(Multiple Items)”. The chart title should change to “Annual Sales – Multiple Regions”.
• If necessary, move the Chart Title so it is centred above the plot area

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”.

Final Touches

To make the pivot chart look even better, you can add a couple of final touches:

• Right-click the Legend, and click Format Legend
• In the Format Legend Task Pane, select Top as the position
• Remove the check mark for the setting “Show the legend without overlapping the chart”
• Close the Format Legend Task Pane

Then, follow these steps to hide some or all of the field buttons:

• On the Excel Ribbon, click the Analyze tab
• At the far right, click the arrow for Field Buttons
• Click on “Show Legend Field Buttons” to toggle that setting
  • OR, to hide all the field buttons, click on “Hide All”, at the bottom of the list (Note: You’ll need to adjust the plot area size, etc., if you turn off all the 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.

Get the Sample File

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.

More Pivot Table Tutorials

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Clear Old Items in Pivot Table