Excel Chart Coloring Issues

July 17, 2013 8:15 AM

I've got three pie charts in excel that show the same cut of information (location of sales) for three time periods: current month, current year YTD, last year YTD, with each chart fed by tables that rank locations by total sales for each time period. I want the color for any given location to be the same in each chart, regardless of its rank for each of the three time periods. How do I do that?

I know I could do it by going and manually changing the colors for each slice in the chart, but I produce a significant amount of these reports and the manual process isn't practical. We have over 30 locations, but on each chart, we show the top 10 locations plus "other", so I also don't have the same set of ten locations each time -- it's a subset of the 30+.

The tables are populated by referencing a pivot table, so it's dynamic each time I refresh my data query.

Any suggestions would be very much appreciated! I'm using Excel 2010 and have only a basic knowledge of VB. I have access to powerpivot, but just got it and haven't used it all that much. Thanks!

posted by undercoverhuwaaah to Computers & Internet (1 answer total)

This article seems to give instructions to do what you want to do, except for bar charts rather than pie charts. It does require VB, though, and I'm not expert enough in that to know how easily it could be adapted.
posted by Johnny Assay at 9:00 AM on July 17, 2013