Excel Bubble Chart Size Based on Value (2 Suitable Examples)

A Bubble Chart is effective in visualizing and presenting 3 dimensions of data in a graph. Along with that, they represent the relationship among different values. In a Bubble chart, the X values are represented on the horizontal axis, the Y values are represented on the vertical axis, and the Z values are represented by the Bubble size. The higher the Z values, the larger the Bubble size will be. In this article, we will create an Excel Bubble chart size based on value. To do so, we will demonstrate to you 2 easy methods.

Adjust Bubble Chart Size Based on Value in Excel: 2 Methods

The following table contains the Product, Cost Price, Selling Price, and Profit columns. Using this table, we will create an Excel Bubble chart and go through 2 methods to adjust the bubble size based on value. Here, we used Excel 365. You can use any available Excel version.

1. Using Context Menu Bar to Adjust Bubble Chart Size Based on Value

In this method, we will use the Format Data Series option to create an Excel Bubble chart size based on value. To do so, we will go through the following steps.

Step-1: Inserting Bubble Chart

In this step, we will insert a Bubble chart.

First, we will select the cells from C5 to E9.
After that, we will go to the Insert tab.
Next, from Insert Scatter (X, Y) or Bubble Chart >> select a Bubble chart.

Afterward, you can see the Bubble chart.

At this point, we will click on the Chart Title to edit the Chart Title.

Therefore, you can see the Bubble chart with Chart Title.

Step-2: Using Format Data Series Option

In this step, we will use the Format Data Series option to change Excel Bubble chart size based on value.

In the beginning, we will right-click on the bubbles on the Bubble chart.
Furthermore, we will select the Format Data Series option from the Context Menu.

A Format Data Series dialog box will appear on the right side of the Excel sheet.

Afterward, from Series Options >> set Scale bubble size to as 150.
You can set the Scale bubble size to any size according to your choices.

Hence, we can see the bubble size of the Bubble chart has increased.

As a result, the chart looks more visible now.

Step-3: Adding Data Labels to Bubble Chart

In this step, we will add Bubble size from the Label options, which is the Z Values, in this case, it is the Profit in the Bubble chart. Therefore, we can read the value of the bubble.

First of all, we will click on the Bubble chart.
After that, from Chart Elements >> click on the rightward arrow of the Data Labels.
Then, select More Options.

A Format Data Labels dialog box will appear on the right side of the Excel sheet.

Next, go to Label Options.
After that, from Label Options >> unmark Y Values and mark Bubble Size.

As a result, you can see the Bubble chart with Bubble Size from the Label options.

2. Use of Paste Special Feature to Adjust Bubble Chart Size Based on Value

Here, we have two tables of Group-1 and Group-2. You can see that the Total Match and Played Match columns in both groups are the same. However, the No. of Wins column is different for both groups. Here, we will create a Bubble chart for both groups. However, both the Bubble chart will contain the same size of bubble. This is because the Scale bubble size to is set to 100 for any Bubble chart created from different data.

Let us first show you that the Bubble charts of these two groups are the same.

Steps:

First, we will insert a Bubble chart for Group-1 by following the Step-1 of Method-1.

Therefore, you can see the Bubble chart in the following picture.

After that, we edit the Chart Title and we follow Step-3 of Method-1 to add Bubble Size from the Label options, in this case, which is the No. of Wins to the Bubble chart.

Hence, you can see a Bubble chart of Group-1 with Bubble Size from the Label options.

Next, we insert a Bubble chart for Group-2 by following the Step-1 of Method-1.
Moreover, we edit the Chart Title and we follow Step-3 of Method-1 to add Bubble Size from the Label options, in this case, which is the No. of Wins to the Bubble chart.

Therefore, you can see a Bubble chart of Group-2 with Bubble Size from the Label options

Here, you can easily notice that in spite of the chats having different numerical Bubble sizes, they look similar. Along with that, the size of the bubble for maximum value is the same. Therefore, the two charts are identical.

Now, we want to create an Excel Bubble chart size based on value. To do so, we will go through the following steps.

Step-1: Inserting Bubble Chart for Group-1

In this step, we will insert a Bubble chart for Group-1.

First, we will insert a Bubble chart for Group-1 by following the Step-1 of Method-1.

Step-2: Adding Data of Group-2

In this step, we will add Group-2 data to the Bubble Chart, and to do so, we will use the Paste Special feature.

First of all, we will select the cells of Group-2 from C15 to E19, and copy these cells by pressing CTRL+C.

You can also select these cells and right-click, and then select Copy from the Context Menu.

After that, click on the Bubble chart.
Next, go to the Home tab.
Moreover, from the Paste option >> select Paste Special.

At this point, a Paste Special dialog box will appear.

Make sure that New Points are marked under Add cells as option.

Along with that, make sure Categories (X Labels) in first column is also marked.

Then, click OK.

Therefore, you can see in the Bubble chart, that inside the Group-1 bubble, the Group-2 bubble has been created. You can see the size of the Group-2 bubbles is smaller than Group-1 bubbles.

Step-3: Hiding Group-1 Data Labels

In this step, we will hide the larger bubbles of Group-1, so that the Bubble chart only shows smaller bubbles of Group-2.

In the beginning, we will select the bubbles on the Bubble chart.
Next, click on a single large bubble.
After that, right-click on the selected single bubble.
At this point, click on Fill from the Context Menu.

Moreover, select No Fill from the Fill option.

Repeat the following procedure for other large bubbles as well, and hide all the large bubbles.

Hence, you can see the Bubble chart now shows smaller bubbles for Group-2.

Step-4: Adding Data Labels

In this step, we will add Bubble size from the Label option to the Bubble chart. As the Bubble chart now shows the bubble of Group-2, we will keep the Bubble size of Group-2 and delete the Bubble size of Group-1.

First, we add Bubble size to the Bubble chart by following the Step-3 of Method-1.

Therefore, you can see in the Bubble chart, that there are two Bubble sizes. One is for Group-1, and another is for Group-2. Next, we will delete the Bubble size of Group-1.

Afterward, we will select the Bubble size of the chart.
Then, we will click on the Bubble size we want to delete.

Here, we click on the value 450 which is a Bubble size value for Group-1.

At this point, press the DELETE button.

In a similar way, we delete all the values of Bubble size for Group-1.

Therefore, we can now only see the values of Bubble Size from the Label options for Group-2.

Hence, we have created a Bubble chart size based on value.

Practice Section

You can download the above Excel file to practice the explained methods.

Things to Remember

While inserting a Bubble chart, make sure the heading of the data is unselected, otherwise, the chart might not show an accurate result.
As the Bubble chart overlaps bubbles, it is sometimes difficult to read the values of the bubbles. Therefore, it is not convenient to insert a Bubble chart with a huge number of data entries.
Do not add colors that make the Bubble chart messy and unattractive.

Download Practice Workbook

Bubble Chart Size Based on Value.xlsx

Conclusion

Here, we tried to show you 2 methods to create an Excel Bubble chart size based on value. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Goodbye!

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

Get FREE Advanced Excel Exercises with Solutions!