Conditional Formatting a Pie Chart?

[Excel Fanboy]

I've created a pie chart with two wedges. The second wedge changes names once a certain number is met. Is there a way to format the chart to change the second wedges color when the name changes?

 

[pgc01]

Hi

You can split the second series in 2, one for each case. Their values will be (Value,0) or (0,Value), depending on the number having been met. Since each series has its own colour it will seem as if the series changes colour when the value is met.

 

[Excel Fanboy]

Thanks for the reply. I've been using the split method for the second series til now, but the people who I create these spreadsheets for have asked me to include data labels so that they can just look at each wedge and now the count without having to click on it or find the table the chart looks to for it's info. Using the split method leaves a phantom zero on the chart. If I can't force the chart to change color, is there anyway to force the chart to ignore a cell and the wedge that cell creates once certain critera are met?

For example, my chart looks at A1 to B3, with A1 showing the current count, A2 showing the count needed to meet goal, and A3 showing the amount over goal. Is there a way to force the chart to ignore A3 until A2 reaches 0, then to ignore A2 from that point on?

 

[pgc01]

... Using the split method leaves a phantom zero on the chart. ...

You are right, if you use the standart chart data labels.

One option is not to use the standart chart data labels. You can use instead 2 textboxes with formulas to display the values of the 2 wedges.

Would this suit your solution?

 

[Excel Fanboy]

Do you mean insert text boxes in the chart area? I wasn't aware you could make formulas work in those text boxes. Or did you mean in cells next to the chart. I wish I could upload a sample sheet so we could visualize the same thing.

 

[pgc01]

I mean a textbox as a object belonging to the chart.

A texbox allows a special simple formula that points directly to a cell, like

=Sheet1!$D$1

With this formula the textbox text will always reflect the value of that cell.

Ex. With the pie chart in Sheet1

- select the chart
- Chart Tools->Layout->TextBox
- draw a textbox inside the chart. You'll notice that although you can move the textbox around inside the chart you cannot make it go outside the chart, because this textbox is an object belonging to the chart
- with the textbox selected click on the formula bar, insert the "=" sign and then click in D1 and Enter. You'll see in the formula bar the formula "=Sheet1!$D$1" that is the formula that determines the text displayed in the textbox
- in Sheet1!$D$1 write for ex. this formula:

="Second Value: "&IF(A2>0,A2,A3)

This will make the textbox in the chart display the value in A2 or the value in A3 depending on the value in A2. Adjust the size of the textbox to accommodate the text.

You can then insert a second textbox to display the text in A1, like

="First Value: "&A1

This way these texboxes replace the default datalabels that you have now and always show what you need.

Does this solve your problem?

 

[Excel Fanboy]

It looks like this will work. Thanks for the help!

 

[pgc01]

You're welcome. Thanks for the feedback.

There could also be solutions with vba but they would be more complex. This one is very simple and if it solves the problem that's great.