How to Color Excel Bubble Chart Based on Value (2 Easy Methods)

In this tutorial, you will learn some valuable techniques to color your Excel Bubble Chart based on its value in the dataset. Because complex datasets are hard to comprehend, bubble charts are an effective way to make these types of data more presentable. Moreover, the bubble charts in Excel are visually more attractive than table formats.

Color Excel Bubble Chart Based on Value: 2 Easy Methods

1. Color Bubble Chart in Excel Using Single Series Data Values

This method of creating bubble chart is quite simple. Also, you can set the color of the bubbles based on value with just a few clicks. The dataset has the ages and heights of four students. Let us see how we can create a color-formatted bubble chart from this dataset.

Steps:

• Firstly, drag and select the dataset.
• Secondly, go to the Insert tab and click on the Insert Scatter (X, Y) or Bubble Chart dropdown.
• Now, from the dropdown options select the Bubble option.

• As a result, Excel will generate a bubble chart with a single color.

• Now, to change the color of the different bubbles, right-click on any of them and select Format Data Series.

• Afterward, go to the Fill and Line option in the new window.
• Then, select the Vary colors by point option.

• Consequently, Excel will format the bubbles with different colors based on their point values.

Read More: Excel Bubble Chart Size Based on Value

2. Use Excel ISNA and NA Functions

We can use these two functions to get values from the dataset and set the color of the bubble chart based on those values. The ISNA function in Excel checks whether a cell contains a #N/A error. If any of the cells contain the #N/A error, then this function returns the value TRUE otherwise it gives FALSE. Similarly, the NA function returns the #N/A error. In the below dataset, we have two categories of bubbles. The first category has id A and the second one with id B. All of the bubbles have an X coordinate and a Y coordinate. The Bubble column denotes the size of each bubble.

Steps:

• To begin with, copy all the values of the first X column to the second X column.

• Now, click on the cell G6 and type in the following formula:

• Then, press Enter.

• After that, copy the formula in cell G6 to the cells below by dragging the Fill handle.

• Next, click on cell H6 and enter the following formula:

• Now, as we did previously, just copy the formula of cell H6 to the remaining cells below using the Fill handle.
• Similarly, copy the formula of columns G and H to columns I and J respectively.

• Following this, go to the Insert tab and click on the Insert Scatter (X, Y) or Bubble Chart dropdown.
• There, select the 3-D Bubble option.

• Immediately, you will get an empty chart box.
• Now, right-click on this empty box and choose Select Data.

• As a result, a new window with the name Select Data Source opens.
• Now, in this window, click on the Add button.

• Here, set the series name to A.
• Then, for the Series X values, select the cells from F6 to F9.
• Also, for the Series Y values, select cells G6 to G9.
• Similarly, for the series bubble size, select cells H6 to H9.
• Now, press OK.

• Next, right-click on the chart and choose the Select Data option as we did previously.
• Here, click on the Add button to add a data series for category B.
• Following this, in the Edit Series window, set the Series name to B.
• Then, select cells F6 to F9 for the Series X values field.
• Similarly, select cells I6 to I9 for the Series Y values option.
• And lastly, for the Series bubble size field, select cells J6 to J9.
• Then, simply press OK.

• Thus, you can see the two data series that you just created.
• Here, press OK again.

• Now, navigate to the Chart Design tab.
• Under this tab, select the Change Chart Type option.

• Here, you will see the Change Chart Type window.
• Under this window, select the X Y(Scatter) options and then select 3-D Bubble.

• Finally, you will get a bubble chart where each category of the bubble is colored in a different way.

Read More: How to Create Bubble Chart for Categorical Data in Excel

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

I hope you learned some valuable features of the Excel Bubble Chart. Formatting the bubbles in your chart will make them more readable. Also, this helps a lot to relate the chart with their respective dataset. Although we have used some simple datasets for this tutorial, you can take these methods to create colored bubble charts for more complex 3 dimensional data. If you have any queries, please let me know in the comments.

Related Articles

• Create Bubble Chart in Excel with Multiple Series
• How to Create Bubble Chart with 2 Variables in Excel
• How to Create Bubble Chart in Excel with 3 Variables

<< Go Back To Bubble Chart in Excel | Excel Charts | Learn Excel