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

Get FREE Advanced Excel Exercises with Solutions!

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:
=IF(\$B6=G\$4,\$D6,NA())
• 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:
=IF(ISNA(G6),NA(),\$E6)
• 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.

## 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF