by contextures.com
After you create a pivot table, you can insert a pivot chart, based on that pivot table. However, if you change the layout of the pivot chart, it will also change the pivot table’s layout.
For example, if I remove City from the chart in the screen shot below, the City field will also be remove from the pivot table.
Continue reading “Changing Pivot Chart Affects Excel Pivot Table”
I’ve updated one of the pivot chart sample files on my Contextures website. On the main sheet, there are two pivot charts, showing survey responses by department, and by years of service.
At the top of the sheet, you can select a question from the dropdown list, and view the survey results for that question.
The quickest way to create a chart in Excel is by using a keyboard shortcut. With the following shortcuts, you can create a chart on a separate sheet, or place it on the same sheet as the data.
These shortcuts work for worksheet data, or pivot table data. In this example we’ll create quick Pivot Charts from a pivot table.
To create an embedded pivot chart, on the same sheet as the pivot table, follow these steps:
To see the steps for creating a pivot chart with shortcuts, please watch this short video tutorial.
_________________
In an Excel file, you might have a couple of pivot tables on different worksheets. If you create a pivot chart for one of those pivot tables, you might spend a long time setting it up, with specific formatting and design settings.
It would be nice to copy that chart, and use it for another pivot table, but you can’t alter the source data for a pivot chart.
When you create a pivot chart from a pivot table, the numbers on the chart’s axis are in the same format as the pivot table’s numbers. In the screen shot below, the numbers are in General format, with no comma and no decimals.
This short video shows how to change the number format for the pivot chart only, or change the number format for both the pivot chart and the pivot table. There are written steps below the video.
To change the number format in both the Pivot Table and the Pivot Chart, you can change a setting in the pivot table value field. For example, if you want to add a comma separator, follow these steps
In some cases, you might want a different number format in the pivot chart, rather than making it the same as the pivot table. In this example, you’ll format the pivot chart to show the numbers as thousands, so the numbers take less room.
Follow these steps to change the pivot chart number format, without affecting the 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.
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. You can hide the first pivot table that’s connected to the pivot chart and use the second pivot table as for printing reports.
If you require several charts based on the same pivot table, but with different layouts, create multiple pivot tables based on the original pivot table. Create one pivot chart from each of the secondary pivot tables, and rearranging one pivot chart won’t affect the others.
___________
When you create a pivot chart, the series order is automatically applied. In this example, the product categories had been manually sorted in the pivot table. In the pivot chart legend, the products are in the same order: Crackers, Snacks, Bars and Cookies.
In a longer list of items, you might like the series sorted alphabetically, so they’re easier to find in the list.
To change the sort order, follow these steps:
NOTE: Changing the series order in the Pivot Chart will also affect the Pivot Table on which it is based.
_________________
After you create a column chart from a pivot table, you might want to change it so the chart is a combination chart type. You’d like most of the series to remain as columns, and one of the series to be a line.
In Excel 2007 there are no Combination Chart types that you can choose, as there were in the Excel 2003 Chart Wizard. However, in any version of Excel you can create your own combination charts.
In this example, the chart is a Clustered Column chart type, with the series showing the sales of each category in each city.
You’d like to change the Cookies series to a line, so it stands out from the other categories.
In the pivot chart, right-click on one of the Cookies columns.
In the shortcut menu that appears, click Change Series Chart Type
In the Change Chart Type dialog box, click the Line chart type, and click one of the Line subtypes, then click OK.
The chart is now a combination chart, with columns for Bars, Crackers and Snacks, and a line for Cookies.
___________________________
For more information on pivot tables, see the Pivot Table Topics on my Contextures web site.
___________________________
In Excel 2003, if you select a cell in the pivot table and click the Chart Wizard button on the Standard toolbar, a default pivot chart on a chart sheet is created. In some cases you might prefer to step through the Chart Wizard so you can set the options as you create the pivot chart.
Note: After selecting a pivot table as the data range, you can’t return to Step 2 of the Chart Wizard. To use a different data range, create a new chart.
___________________________
For more information on pivot tables, see the Pivot Table Topics on my Contextures web site.
