• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Bubble chart challenge

Hi Chandoo colleagues. I'm attaching a file with a bubble chart (which I think is appropriate for a certain application we're working on) but I would like to do some modifications to it without using VBA (if at all possible).

I think a lot of what I'm looking for is in the formatting of the chart. There are a set of "Locations" and "Values". I've partitioned these values into certain ranges (although the partitioned values are not actually plotted). I've also "randomised" the values to see if the chart responds.

So, if possible, I'd like to do the following:
1. Can we have the labels "A" to "G" on the horizontal axis?
2. Can we make the sizes of the bubbles equivalent to the sizes of the data values (big numbers = bigger bubbles). It would be good to "display" a zero (or negative value) bubble somehow.
3. Can we have coloured ranges (horizontal bands) on the chart equivalent to the "steps" in D2:G4. If the Step Values change (i.e. different ranges are being analysed) then the coloured bands on the chart should reflect the break points.
4. Can we have the bubbles coloured according to the ranges that they are in (i.e. if a bubble is between 0-20, then the bubble is red?)

I appreciate that this is a lot to ask for, but I'd be grateful for some guidance on this.

Many thanks in advance.


HI vletm. Very good, thank you. I like the idea of the text boxes giving the background colour. Do you know if there is a way to link the size of these text boxes to the range values if the range values change (i.e. if the first range was originally 0-20, could the size of the text box change if this range changed to 0-30 or 0-40)? I've realised that it is not possible to get the text labels on the X-axis since the bubble chart plots numerical values on X and Y axes.

Many thanks for your help


Excel Ninja
Pete Mccann
Of course, those ranges could be as You've written ... but ... eg if value is 20 then ... is it 'red' or 'orange' as well as those other steps.
Sizes of boxes ... if those not vary hourly then the most economy way to handle those ... manually.
... many of those Your ideas could do otherways - eg Your #1 & #4 ... but those would need time... it won't be a chart anymore.
Thanks - yes, I understand. Sometimes "management" change their mind on these sort of things (change of the range break points, etc). If one range changes, then the next range would also change in a corresponding way. I was trying to see if it is possible to automate these sort of changes but, as you say, this can be done manually. Best regards.