Excel Bubble Chart Map

I have been using teylin's excellent solution of the above. It has a great use in the oil & gas industry. I would like to be able to construct lines between various bubbles of my choice, the thickness of which would represent the degree of interdependence between them. The degree of interdependence between bubbles could simply be input numbers off a scale from 1 - 10. I enclose an example of the point that I have reached with this solution. Example of a bubblemap without connector lines between bubbles
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

In order to connect the bubbles with lines, you would need to create a XY chart that overlays the Bubble chart. Unfortunately, Excel does not allow a Bubble chart to be combined with any other chart type, so this approach is not easily done.

You could create an independent XY chart that displays the lines to connect the bubbles (the data for the lines can easily be derived from the data for the bubble chart), make the bubble chart background transparent, make the two charts the same size, with the same X and Y axis min and max and then overlay one chart with the other.

Doing this will be a major PITA, because nothing will really fit and you will have to constantly tweak and fiddle to get it right. It may be possible for someone more versed in charting VBA than me to come up with the parameters to create exactly identically sized plot areas for the two charts, but positioning them so they align perfectly will be yet another matter.

As an idea, the concept works, but I wouldn't be too keen on doing it in practice.

As to formatting the line thickness depending on degree of interdepencence: Line thickness is not something that is influenced by the tabular chart data. It would need a macro to evaluate the numbers and set the thickness for each line.

I don't want to discourage you, but this task is not easily performed in Excel.

cheers, teylyn

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MbeavisAuthor Commented:
Hi Teylyn

Thank you for the extremely quick response, most appreciated. This looks like an excellent idea , I will give it a try.
At least I know if you cannot do it it cannot be done :)


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.