s is possible in Excel to automatically colour code bubbles in a bubble chart and combine with some sort of line

Question

Hi all

I recently took part in a fauna survey of a meadow, where we reviewed one 1m square sites in a number of locations across the meadow to assess its health.

I would like to present this data using the actual X and Y coordinates of the survey points and then have some representation of the over all health at that point. I thought a good way to do this would be using a bubble plot. With the coordinates being the X and Y and the health being the size of the bubble. That way we could get a visual representation of the health across the site and potentially be able to compare future surveys be adding other data series for other surveys . The idea being that this might help us understand if some areas of the meadow healthier than others eg northwest corner or how things change over time.

To help make this clearer I would like to do a couple of extra things to the chart and I just wondered if someone could given me a steer on how to do the following if possible:

1. I would like to draw the meadow boundaries onto the same plot using the x and y coordinates so either using a scatter or line plot. So this would require a combo of scatter/line and bubble is this possible unless I can connect a separate series of bubbles with fixed size of 1% with lines
2. Is it possible to get the actual bubble to colour code on the values in them ie RED if health under 30% to green if heath over 80% so that it is even clearer which are the healthier meadow areas.

Answer 1

1. Perhaps use a picture as background for the chart.
2. See the demo workbook at https://www.dropbox.com/scl/fi/a361l4lxezortjy4uwjlt/Bubble.xlsx?rlkey=5dmhofmt7iguc5wg7afhxgbb8&dl=1. I used some dummy data. I created three columns with formulas to separate the three groups, so that they can be shown as separate series, each with its own colour.

Answer 2

Hello Chris Knight

Thank you for posting your question in the Microsoft Q&A forum.   

About your first request: By design, bubble chart in excel cannot combine with other chart style. If you want to draw the meadow boundaries into the same chart, you’ll need to draw manually and insert the chart as a picture. Or you can use Power PI as a workaround.  

You can give feedback on Excel · Community if you want this feature for Excel to appear in the future. The Product Team is in-charge of the feedback site, and they are constantly checking the comments and feedback from our customer. Many features have been developed or improved based on users' comment.    

About the second request: You can create a table like this: 

In cell D2, enter this formula: =IF(C2<30,"Red",IF(C2<=80,"Yellow","Green")). Then drag it down to fill the rest of the column. 

After you got the information for Color column, you can create a bubble chart with these steps: 

1. Add a column for Bubble Size: can be the same as Health(%) or scale if needed

2. Create three separate bubble charts or add three series to the same chart: 

• Right-click the chart → Select Data → Add Series 
• For each series, select the appropriate X, Y, and Bubble Size values. 

3. Format each series: 

• Click on the bubbles → Format Data Series 
• Change Fill Color to Red, Yellow, or Green accordingly. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.