How to Change a Pivot Chart

After you create a pivot table, 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

Pivot Chart Layout

After you create a pivot table, you can insert a pivot chart, based on that pivot table. If you need help with creating a pivot chart, follow the instructions here.

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 fields 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. Unfortunately, there’s no setting you can change if you want the pivot chart and pivot table to work independently.

pivot chart layout

As a workaround, you can create a second pivot table, 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, so it isn't visible. Then, use the second pivot table for printing reports. In the screen shot below, the pivot chart is based on a different pivot 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.

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

Get the Sample File

Get the sample pivot chart tutorial file

More Pivot Table Tutorials

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table

Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.

Get weekly Excel tips from Debra


Last updated: July 11, 2021 11:52 AM
Contextures RSS Feed