Pivot Table Charting Help

March 30, 2006 4:21 AM

Help me with creating two bar charts from two columns in one pivot table, please?

I'd like to create a single worksheet that has two charts on it. One chart will show a breakdown of product groups sold in the last year while the other will show the same kind of breakdown for the previous year. I can get the data in to two columns in a pivot table. I have no control over the format of the source data and there is too much of it to massage it into shape.

One difficulty is that I will be running this as a report for a number of different customers. They do not all stock the same product groups. For example, customer 1 stocks widgets a,b,c and d but customer 2 only stocks widgets a and b. This means that the pivot tale will always be a different length so a manual chart for each is out of the question.

posted by dantodd to Computers & Internet (4 answers total)

Can't be done. What you can do is create an exact copy of the pivot table, and base the second chart on the copy.
posted by grateful at 7:04 AM on March 30, 2006

I think this can be done, and easily. There are several ways of doing it, but by far the simplest would be to use a PivotChart. If you haven't seen this before, its a chart that uses the data in the pivot table as its source. It will automatically take account of changes in the pivot table size.

However I don't think you can create two charts from one table. You are going to have to create two PivotTables, one for each year.

1.Right click on the pivot table, and select PivotChart
2.Fiddle about with the chart settings until it looks right. I presume you want a bar or column chart.
3.Go to Chart/Location and change the location to a worksheet.
4.Repeat for the second PivotTable.

thats it.

Personally I would put both years on one chart, as that would make the data easier to compare, but I assume you have your reasons.

Be aware that a bug in Excel means that pivot charts do not retain all their formatting when the data is refreshed. In particular they return their colour scheme to the default chart colour palette. If you want to change the colours, you have to change the chart fill colour palette in the tools/options/color dialogue. This will affect all your charts.

Finally, I assume you are putting all the clients in a page field in the pivot table, so you can change the client easily. This will also appear as a dropdown on the chart, so you can change the referenced client on the chart sheet too.
posted by Touchstone at 8:56 AM on March 30, 2006

Response by poster: Touchstone, you are correct - I have the customer as a page field. If I use two pivot table, is there a way to only change the page field in one location for both charts?

Thanks for the tip on the colours, that was going to be my next question! mrexcel.com has a tip that allows you to give the same categories the same colour even if it doesn't have all of the possible categories.

Also, when I wrote bar chart, I meant pie chart. I have found this type of chart to make my point the clearest when presenting to customers.
posted by dantodd at 1:44 PM on March 30, 2006

I'm not aware of any way to link the page fields, at least without using VBA. You'd write a macro to change the page field on the second chart, triggered by the refresh pivot event. I'm no VBA expert, but I'm sure it could be done. Your best bet is to post on an excel forum, ozgrid or mr excel both good.

Alternatively you could use a donut chart. Two pie charts one inside the other, with a hole in the middle. If you've got many categories, this can be a bit hard to understand though, but have a look. Or two stacked bars side by side, one for each year. This would mean that you would need only one chart.

Sorry not to be more help.
posted by Touchstone at 3:11 AM on March 31, 2006