Friday Challenge – Conditionally Control Excel Bubble Chart Colors

Hi All,

Sorry this is a little late.  But better late then never Smile.

Here is this week’s Friday Challenge (on Sunday.  I will post my results on Tuesday to give you more time):

The challenge is how can you create an Excel chart that conditionally changes the colors in an Excel Bubble chart.  Digest the request below:

“I’ve used Excel 2010 to create a bubble chart plotting the risk-reward analyses of a portfolio of research projects. X-axis is the potential reward of the project, y-axis the probability of success, z-axis (bubble size) is the budget.
I would like to control the color of the bubbles by a fourth, qualitative text attribute of the project type. So each project (bubble) would be characterized as either “New Product”, “Optimization” or “Tech Transfer”.
Can this be done by conditional formatting? If you could point me in the right direction, it would be most appreciated.   – Cheers,  John”

Here is our “fake” data set that I created based on the request:

2 Type X Reward Y Probability Z Bubble Size
3 New Product 8 8 80
4 New Product 9 10 29
5 Optimization 1 9 13
6 New Product 10 0 62
7 Tech Transfer 9 9 52
8 Optimization 7 7 12
9 Tech Transfer 9 10 22
10 New Product 2 0 0
11 New Product 4 7 29
12 Tech Transfer 8 2 30

So give this some thought and think of how you can set this up in an Excel spreadsheet.

Leave me comments on how you would handle this problem.  Also, if you have a challenge that you are looking to see if it can be done in Excel, leave a detailed request in the comments below.




  1. If this is just a one-time project, I would simply sort the data by “Type” and then select the data of each type separately. Then you can change the color of each series.
    If this is an ongoing project, then you would have to set up three separate tables (one for each type) and use a formula like =IF($A3=$G$1,B3,NA()) to populate the three different tables (obviously the formulas will change for each table. I will send a workbook.


    • Your spreadsheet looked great. I never thought of the sort and separate as I tend to think of formulas first. Awesome job and thanks for the comment. Steve=True


Please enter your comment!
Please enter your name here