Automatic colour coding for bubble chart data points

[dr3969]

I'm working in Excel 2007 and have created a bubble chart. I've automated the x- and y- axis data, bubble sizes and data labels, but I also want to automatically colour the bubbles either green, red or yellow depending on a text-based criteria (e.g. "strong" = green, "medium" = yellow, "weak" = red).

Is there any way this can be automated? I currently do it manually whenever I change the chart, which is very time-consuming...

Thanks!

 

[Steve=True]

Hi Dr3969,

Welcome to the Forum!

Yes you can do this, but it requires the creation of a new chart series for each color.

NA() is your friend for these dummy series.

You would create the Green Series to look something like this:

=if(criteria=Strong,data to plot,NA())

NAs will not be plotted.

Then select this series in the chart and change the color to what you want it to be.

If there are no values, then no green bubbles will show. do this for each series

Hope this helps

Steve=True
www.exceldashboardtemplates.com

 

[manujain3710]

Hi

I am following your tutorials very closely . It is nice to have such initiatives to help people in their excel work. Today I stuck in a bubble chart of excel. It has to have 5 things in it. X axis Y axis bubble size bubble color and lastly bubble no. Can this be done in excel? Can you please tell me how?

Here is the snapshot of what i m asking for
https://drive.google.com/file/d/0B7sypgXAW9q9eTFsRmFtSTc1bFE/edit?usp=sharing

Hi Dr3969,

Welcome to the Forum!

Yes you can do this, but it requires the creation of a new chart series for each color.

NA() is your friend for these dummy series.

You would create the Green Series to look something like this:

=if(criteria=Strong,data to plot,NA())

NAs will not be plotted.

Then select this series in the chart and change the color to what you want it to be.

If there are no values, then no green bubbles will show. do this for each series

Hope this helps

Steve=True
www.exceldashboardtemplates.com