How-to Dynamically Change Excel Bubble Chart Colors
HTML-код
- Опубликовано: 27 авг 2013
- Step-by-Step Tutorial at: www.exceldashboardtemplates.co...
This tutorial will show you how to conditionally format bubble colors in an Excel chart using the NA() function.
Not quite what I was looking for today but still learned a couple new excel trips/tricks. Thanks for posting.
Jody Hickmott Hi Jody, thanks for the kind comment. Glad you were able to learn something new :) Steve=True
This is brilliant, it really helped me achieved what i wanted, Thank you very much for this very clear tutorial.
geoffroy gaborieau Thanks Geoffroy for the great comment. So glad to help and glad you think the tutorial is very clear. Much appreciated. Steve=True
Thanks Jonathan!
Thank you, this was very helpful!
Thanks Dave, you are welcome.
How to show on chart type as horizontal axis and reward as secondary horizontal axis and probability as vertical axis?
Hi, Thanks for the video.
in my case I have several Y and Z values for each X value.
How would you recommend to do such chart?
Neat! Thank you.
THIS WAS AWESOME THANKS.
You are welcome Aion. Thanks for the comment. Steve=True
Thank you. Clear and easy to replicate.
Glad it was easy. Thanks Anthony. Steve=True
really interesting, Thank you
Maybe you could help me as I need that my Y axis to provide a list of themes and not numbers, could that be possible?
Bernardita Cardenas Hi Bernardita, I don't think this is possible in the bubble chart type. Sorry. Steve=True
Thanks this has been very helpful. Now my only issues is how to get names of each line on the bubbles? I had already created another bubble chart that has all my bubbles as there own individual series but it doesnt allow me to group by color. This way allows me to group by color but I cant get my names to generate with the bubbles. Any ideas?
Joshua, I am not sure I completely understand. Leave me a comment on my website with your contact information and I will send you an email so that you can send me an example spreadsheet. Steve=True
Can you do a bubble chart within a bubble chart??
Thank you so much! How can i change the color of an entire series so it is not always blue/red/green etc?
Hi Daniel, thanks for the great comment! To pick the color that you want, you will want to right click on the series that you want to change in the chart and then select the Format Series... menu item. Then change the Fill color to solid fill (instead of Automatic) and pick the color you want on the color menu. Hope this helps. Steve=True
exceldashboards Thanks you so much, this will help me a lot at work!
Hi, this is good. how we can display customized values in tool tips in bubble chart?
Hi Balaji, I am not sure you can modify the tool tips as I think it relates to the data points in the chart.
Thanks - was able to figure it out once you showed me what microsoft was doing when selecting multiple columns.... x,y,s,y,s,y,s,y,s...... one y,s per series
Thanks for the comment Malcolm. Glad to help. I appreciate that you found the reasoning behind it helpful as well. Some people want to get right to the solution and it is refreshing to see that the explanation also helped. Steve=True
Hello I have a bubble graph where I have to plot the size of the bubble as the impact of the particular variable.
But in case of negative impacts for example -1> -5 so the size of the bubble with value -1 should be greater than the size of the bubble with impact -5 but how can I do this?
Hi JG. So would you have positive and negative sizes? i.e. -5, -1, 1, and 5?
a) If so, should the -5 be the same size as a 5 or the smallest and 5 the largest?
...a1) If yes to this, then i would add the ABS(smallest value)+1 (i.e. -5+5+1) to the Size variable for all data points to make is show up on the chart as the smallest value and your largest value will also adjust accordingly (I.e. 5+5+1), then use custom labels for the values to show them as negatives.
...a2) If no to this, then just use the ABS() function as described below.
b)If not, and all are negative values, you can simply use the ABS() function to make them >0 and then add custom labels for the Negative values.
exceldashboards thank you so much for the reply . In my graph the size of the data point with value -5 should be the smallest and +5 the largest. I will try this solution and update you :)
sir are you showing us or doing your work???
I didnt get how did you choose three different colours
You can double click on the series you wish to color and change the color in the Format Data Series dialog box.
Thanks for this helpful tutorial! Here are two questions:
1. How to replace the numbers of one axis, x or y axis, with names of the category?
(I know bar chart may be used instead, but I'd like to use bubble chart please)
2. How to generate a scale or legend for the size of the bubbles? Similar to the one shown here: blog.revolutionanalytics.com/2010/11/how-to-make-beautiful-bubble-charts-with-r.html
Mabel Ting Wong Hi Mabel. 1) A bubble chart is like an XY chart, so it doesn't show categories. You could a line on the axis and give them custom labels based on a range. 2) Excel controls the size of the bubbles based on your data.
+exceldashboards Thank you so much! I ended up creating several bubbles (in a different colour) with specified values to 'generate a scale' :)
+Mabel Ting Wong That is a great concept! Thanks for letting me know your great solution. Steve=True
the problem is that you don't explain the case. you just explain how to do it,
Sorry shahilagh, it was a long video. Here is the use case that you can find on my website: "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?"
i have the same question too. :)