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 chart
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.
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 fields into the Column area in the PivotTable Field List.
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. Unfortunately, there’s no setting you can change if you want the pivot chart and pivot table to work independently.
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.
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.
Download the sample pivot chart tutorial file
For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter.
Last updated: November 18, 2016 9:02 AM
Contextures RSS Feed