Sign up ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.


I've an excel template. This template I populate with data by running a program I've written. The excel template has a pivot table on a sheet that get's data from another sheet that I've updated. The pivot table has two columns and a connected chart.

The first column one is "Status" and the other is "Count of x". The first column contains a word like Implemented, Rejected, Issued, Accepted etc. All these words are known. The second column contains a number.

The rows in the pivot table varies. Sometimes there are only Implemented and rejected and sometimes more rows with values.

What I want

I want to be able to specify the legend key and connected pie chart color based on column one's text. So that Implemented is always green, rejected is always red etc.

If it's possible to write code that checks if the value is equal to one of the words and set the color based on those, that would be interesting.


There is a bug. If the excel get's macro enabled when the chart page is active excel remembers the color formatting. If another page is active when the user enables macro then the color formatting is forgotten by excel.

share|improve this question

2 Answers 2

Not tested (I don't have Excel 2010 and charting there is not the same as Excel 2007) but I think the following worth trying, unless you get a better offer.

  1. Ensure your source data contains each of the Status options. (To force at least a single instance of each into the pivot table driving the pivot chart. Don't hide blanks.)
  2. Conditionally format the pivot table to suit chosen colour scheme (so it matches with pie).
  3. Create your pie chart with colouration to suit and save as template.

Pitfall could be having to reimpose the template format each change, but that's little more than a pivot table refresh.

share|improve this answer

The solution above (creating a template) doesn't work if you want the values to be set based on the series name, rather than the series number, because Excel scrambles it when the template is applied, and I need the colors to remain with the series name regardless of whether that series shows up this month or not. This shouldn't be this difficult. I guess I will have to go the VBA route but I'm not sure I understand the code.

share|improve this answer
Welcome to Super User! This is really a comment and not an answer to the original question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. – DavidPostill Jul 1 at 15:40

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.