Free Microsoft Excel 2013 Quick Reference

Variables Colors In Bubble Chart

With the bubble charts I can do the X and Y and the size of the bubble.
I'd like to be able to also control the color of the bubble based on a variable. I am looking for either a product add in or workaround

Looking to reproduce the effect of the tracking dashboard chart shown here:

Thank you.

Post your answer or comment

comments powered by Disqus
Hi All!

I am creating bubble charts, in which the size of the bubble relates to the cost of the item. I was wondering if their is any *automatic* way of having the bubbles be Red (Greater than $800) Yellow (Greater than $400) or Green (Less than $400). I would prefer to have the 3 different colors than 3 differents sizes. So if it is possible to change colors than I would like all of the sizes to be equal! Is this possible?

Thank you for any help!


Dear Community,

This is my first thread in this forum and I hope someone can help me (i attached the xls file):

I am trying to create from a table (Sheet1) a bubble chart which should represent 3 quantitative dimensions (x: Risk, y: Benefit, z (bubble size) : Budjet) and one qualitative dimension (attractiveness).
I also want the data labels to be displayed on the chart.

Attractiveness should be represented through the colors of the bubbles:
e.g. green for "high" attractiveness
yellow for "medium" attractivenss
red for "low" attractiveness.

I could achieve something but it is not exactly what I want:

I found a "VBA Conditional Formatting of Charts by Series Name"(see below). This allows to color the bubbles according to the cell color of the respective data serie.
This works fine but I have to generate manually a bubble chart (Sheet 2) with multiple series, specifying each data serie and its respective x,y,z values in the wizard (I have 250 entries!). The reason why i have to do it manually is that otherwise excel generates only a bubble chart with 1 data serie (Sheet3)!

Does anybody has a solution?


I'm trying to create a standard color scheme for my departments 3d modeled (CAD) parts.
I want to make a table with RGB values that control the colors in a chart.
This would be a great "tool" to organize and experiment with different colors.
How can I do this?

I've seen pie charts inserted into bubble charts, but I'd like to just modify the bubble color in one series to incorporate a 4th continuous variable. So input data are four continuous variables, two for the axes, one for bubble size, and another for shading. Any ideas welcome!

We are preparing a bubble chart in which the x-axis shows scores from a
survey, the y-axis shows relative importance, and the z-dimension shows the
percentage point change in the score from the previous period. We have set
the default color of the bubbles to grey (which means no significant change),
and would like to make the significant changes colored in red (negative) or
green (positive). We have already done the stat testing, and can mark the
significance testing with a -1 (negatively significant), 0 (insignificant),
and +1 (positively significant). We would like to be able to automate this
(i.e., not do manually), as we have at least 100 different charts we need to
create. Is there any way to automate this? The data array looks like this:

2006 Score Importance %point change
54 .27 -2
77 .59 8
82 .47 3
68 .78 -5

So we would have a grey bubble for row 1 and 3, a red bubble for row 4, and
a green bubble for row 2. Any suggestions?

I have data in the attached spreadsheet - column A and B that I highlighed in blue. I want to plot these in a chart that I haven't been able to locate. I want to create a bubble chart -where each ticker (column A) is its own circle or bubble and the size depends on how big it is as a % in column B. Does anyone know how to plot this?


Im trying to create a simple 3D Bubble Chart macro, and as most people who've never dealt with this before (i think), i've hit a wall with BubbleSizes.

I've attached the file im trying to get to work. The VBA is quite simple but an error occurs at BubbleSizes.
I've read that BubbleSizes takes a different reference style than xValues and such, but haven't had any luck trying to change that.

For those who just want to see the code without getting the file:

    Dim myChtObj As ChartObject 
    Dim rngChtData As Range 
    Dim rngChtXVal As Range 
    Dim iColumn As Long 
     ' make sure a range is selected
    If TypeName(Selection)  "Range" Then Exit Sub 
     ' define chart data
    Set rngChtData = Selection 
     ' add the chart
    Set myChtObj = ActiveSheet.ChartObjects.Add _ 
    (Left:=250, Width:=375, Top:=75, Height:=225) 
    With myChtObj.Chart 
         ' make an XY chart
        .ChartType = xlBubble3DEffect 
         ' remove extra series
        Do Until .SeriesCollection.Count = 0 
         ' add series from selected range
        With .SeriesCollection.NewSeries 
            .Name = ActiveSheet.Range("B2:B13") 
            .Values = ActiveSheet.Range("D2:D13") 
            .XValues = ActiveSheet.Range("E2:E13") 
            .BubbleSizes = ActiveSheet.Range("C2:C13") 
        End With 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I've read Andy Pope say BubbleCharts are a real pain in VBA, and man is he right.
Someone please shine some light on this


I have a series of quarterly dates ranging from Q2'08 through Q4'12 (column C). I would like to "translate" those dates in to a more easily read/understood mmm-yy format for a bubble chart (column D). Is there a way to do this that does not include me manually translating the dates from Q1'09 to Mar-09? I am open to having the bubble chart display the quarterly dates but I have not been successful in that endeavour.

I have made a pivot table and then made a chart using the same. The data series picks up colors automatically in this chart. Now I am able to go and change these colors on the chart but the problem is that when I refresh the data in pivot table then the pivot chart again uses its original automatic color selection.

Please advise a solution on this problem

I'm trying to add another data series in a bubble chart on another Y axis. So I'll have the data series (X,Y1,bubble size) for the bubbles and another data series (X, Y2) which I want to represent as a line chart.

Is it possible to mix chart types on a bubble chart? Thanks for any help


I would like to draw a bubble chart with category axis eg.
with following data:

mon sun 5
mon rain 4
tue sun 1
tue cloudy 2
tue rain 7
wed sun 3
wed rain 8
wed cloudy 4

So on the x-axis I would like to see the labels mon, tue, wed; on the
sun, cloudy, rain and the size of the bubbles is given by the numbers.

Is this possible in Excel XP and how to proceed?

Thx for your attention,


Mijn Postvak In wordt beschermd door SPAMfighter
14511 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis vandaag nog!


Would you please help me out in creating multiple data series in bubble chart.

I have four parameters as
Series: name
X axis: value
Y axis: value
Size : value

I can select the individual series and edit all those parameter for
individual series, but it's very cumbersome task? do we need to arrange our
data in some order to get the direct multiple data series in a bubble chart.

Please let me know your comments.... waiting for reply !!!

I have data in the folowing form: value(x,y) where x and y are coordinates
(integer)and the value is a value between 0 and 0.5.

For example: 0.2(3,4) means, that the value at x=3 and y=4 is 0.2.

I need a two-dimensional bubble chart. This chart should show bubbles at
(x,y) with radius value. In my example at x=3 and y=4 a bubble with 0.2

The built in bubble chart is not able to do it.

Do you have an idea how to realize it (without writing a whole VB code ?)

Thank you in advance,



I would like to draw a bubble chart with category axis eg.
with following data:

mon sun 5
mon rain 4
tue sun 1
tue cloudy 2
tue rain 7
wed sun 3
wed rain 8
wed cloudy 4

So on the x-axis I would like to see the labels mon, tue, wed; on the
sun, cloudy, rain and the size of the bubbles is given by the numbers.

Is this possible in Excel XP and how to proceed?

Thx for your attention,


Mijn Postvak In wordt beschermd door SPAMfighter
14511 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis vandaag nog!

I need colors in a bar chart to change automatically when
a certain number (i.e., days) is acchieved. For examples
if a project has less than 100 days the bar line in the
chart is green. Once, the project goes over 100 days the
same bar turns red. Can I do this in Excel?

Hello All,

This is my first post so I'm hoping some other bright minds can help me with this. I was hoping to create a bubble chart with the bubble color according to whether the criteria is buy or sell. Here is an example of the information:

Side PctADV Bps
Buy 0.0627 26.43
Sell 0.0407 25.93
Sell 0.1567 17.57
Buy 0.0852 96.49
Sell 0.0778 5.65

I would like the Y axis to be PctADV and the X axis to be Bps with the size of the bubble based on the Bps value. I was hoping to have buys be one color and sells be another. Is there any way to do?

For a Bubble Chart I have 105 rows with 4 columns of data. For example one row is

Name Metric A Metric B Metric C
Joe 7 15 60

How do I get all 105 series mapped via source data without having to go one by one?


Hello Everyone:

I am willing to pay USD$35 by PayPal for some VBA code that makes a scatter chart look exactly like a bubble chart.

Why do I want this? Well, I have some information that I want to present in bubble-chart-style, and I have some other information that I want to overlay in scatter chart and stacked column chart style. However, Excel 2007 does not allow genuine bubble charts to be combined with any other types of charts, and I've been advised that the only solution is to create a VBA-enabled scatter chatter that simply resembles a bubble chart -- which can in turn be combined with other types of charts.

I am attaching a screenshot of the types of charts that I want to build. You will see that they combine bubble chart elements with scatter chart elements (the weighted avg. star; the soft horizontal lines at 0% and 100%), as well as stacked column elements (on the right).

Once I am able to reproduce the bubble-chart-look with a scatter chart, I think I can handle everything else by myself.

Rather than send you my spreadsheet (it's very big and some of the data is proprietary), I would ask that you deliver a sample spreadsheet with embedded code. The spreadsheet shoud contain a small amount of data contained in columns that is charted as a pseudo bubble chart, and if you want, some other data contained in other columns charted as an overlaid scatter chart. Five data points each is fine.

I will then adapt the VBA code to the specific environment of my spreadsheet. You should expect that I'll have a question or two during the adaptation process.

The only additional criteria I can think of are:

- the VBA code must be Excel 2007-compatible
- the bubbles (or circles) must draw as smoothly as a genuine bubble chart -- no jaggies or aliasing that you wouldn't encounter with a bubble chart

Let me know if you're interested!



Dear Chart guru,

I am going to make a bubble chart using vba. The chart consists of x,y points showing where a lamp is located in the ceiling. I want to make a conical projection and depending on the spacing, the light projection in the floor will overlap.

My question: Is it possible to make the bubbles in the bubble chart overlap in different color? Let's say if we assign a color to 1 bubble (each bubble - representing the light projection) as white. Can we make a region where 2 bubbles overlap to be yellow, a region where 3 bubbles overlap to be red, etc (instead of being on the top of each other)?

I hope I make myself clear. If this is possible, I will start working and I might post additional questions here. Thank you in advance.


I am wondering if it is possible to use color as a 4th variable in surface charts.

I have a standard 3-dimensional surface chart, with x, y, and z values.

Further, for each (x,y) value I have a 4th dimension (call it u), ranging from 0 to 1.

I would love to be able to set the color (or gradient) based on these 0 to 1 values. This is like taking the contour map of (x,y,u) and laying it over the surface chart of (x,y,z).

I am not at all certain this is possible, but hopefully it is at least clear what I want, and perhaps there is some trickery I can go through to make it happen.

Thank you for your time!

I have a list of values like these for example (varaible 1):
1 2 3 2 4 3

Which are plotted on a bar chart that has green bars. This is the only chart I'm working with.

I also have a list of corresponding values (variable 2) that goes like this:

1 -1 -1 0 1 -1

What I'd like to do is if variable 2 is negative, turn the color of the bar red, if it is positive, leave it green, and if it is 0, make it yellow. So in example above, the lengths of the bars in the chart would correspond to variable 1: 1, 2, 3, 2, 4, 3, and stay in the positive territory of the chart and the colors of the bars would be:

green red red yellow green red

How can I do this?

Thank you for your help.


I need a quick & easy way to track the progress of a group of similar projects with various customers. (I know that Bubble Charts are not the ideal project management tool, but my boss wanted something very visual and this is what I came up with!)

I have created a Bubble Chart with the following basic elements:
X = Phase of the project (1, 2, or 3)
Y = Annual Business with the Customer ($)
Z = Value of project ($)

In addition, I need to:
1- color each bubble green, yellow, or red (represents risk level)
2- add labels to each bubble (there is only one bubble per customer)

I have managed to make the colors work (by creating 3 series) but I have been unable to figure out the labels. I have searched this board and Googled but have not found a solution that works with the 3 series.

I'm using Excel 2003 and cannot add any 3rd party add-ons. My VBA skills are relatively basic (no pun intended), so please be gentle.

Any suggestions?


Hi all,
This place seems, by far, to be one of the most active excel forum. Thats why i post this thread on an issue i just cant resolve.

i am working with a set of data that i programmaticaly will place chart in MS Excel (to do that ill be using the Java Excel API)

I would like to construct a table that a chart will depend on to draw the data. The data is almost like in the notorius Peltier example (see except it includes an additional column named Employee. So the 3D dataset is Unit pr Employee pr Week.
Now the variable in the data is Employee (they come and go)

So, my question is: given this data, how would one go about and get this represented in a dynamic way, where every new employee gets its color in a stacked column charttype.

See for an example (Notice The blanks in the legend)

I hope its enough description. Any suggestion is most welcome. Cheers

I cannot figure out how to get a bubble chart to do what I want. I have two
variables, Impact vs Cost. Each of the two variables is coded as "Low",
"Medium", or "High". The third variable, which would be the size of the
bublles in the bubble chart, is the number of items for each of the nine
cells in the resulting 3 X 3 grid. Here's the source data:

High 1 0 1
Impact Medium 1 2 1
Low 4 1 1
Low Medium High
I just can't figure out what to put into the data sources panel as the X,
Y, and Sizes variables in order to get a chart with bubbles and the right
labels. I only get numbers on the axis (1, 2, and 3) instead of High,
Medium, Low. I tried something like ={"High", "Medium", "Low"} but that
didn't work. Anyone know how to pull this off? Or that it is impossible and
I should stop trying? Thanks in advance.

No luck finding an answer? You could always try Google.