Contextures

Pivot Tables > 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, 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.

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.

insert pivot chart

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.

insert pivot chart

That creates a series for each year, and shows one year in blue, and the other in orange.

insert pivot chart

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

insert pivot chart

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.

pivot chart layout

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

    insert pivot chart

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

    pivot chart on worksheet

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

    pivot chart combo column line

  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.

    pivot chart combo column line

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

    pivot chart combo column line

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

pivot table with region filter

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.

create pivot chart clustered column

A pivot chart is added to the worksheet, showing the 2 years of data.

 pivot chart clustered column

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

add title to pivot chart

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

move chart title

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.

move chart title

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.

move chart title

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

formatted formula cell above chart

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

link chart title to formula cell

The pivot chart title now shows the result of the formula in cell G1.

completed chart title

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

test dynamic chart title

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

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)

show or hide 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.

completed pivot chart with dynamic title

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

Last updated: August 10, 2022 8:15 PM