Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Background

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.

Thanks

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

1 Answer 1

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

Your Answer

 
discard

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.