Shahriar Abrar Rafid
This article shows how to change the bubble size in a Scatter Plot in Excel. If you need to display the relationship between two sets of quantitative data, Excel provides you with the option to construct a Scatter Plot. Here, we will take you through some easy and convenient steps for how to change the bubble size in a Scatter Plot in Excel.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
What Is a Scatter Plot?
A Scatter Plot also referred to as an X-Y Graph, is a chart showing the correlation between two variables. A scatter graph has horizontal and vertical axes. Those axes contain values that are used to plot numerical data. Generally, the independent variable is placed on the x-axis and the dependent variable is placed on the y-axis. On the graph, values near the junction of the x and y axes are displayed as single data points. The primary objective of a Scatter Plot is to show the degree of connection between these two variables.
There are 7 types of Scatter Plots in Excel. They are as follows.
- Simple Scatter Plot
- Scatter with Smooth Lines & Markers
- Scatter with Smooth Lines
- Scatter with Straight Lines & Markers
- Scatter with Straight Lines
- Bubble Chart
- 3-D Bubble
In this article, we’ll work with both the Scatter Chart and the Bubble Chart.
4 Steps to Change Bubble Size in Scatter Plot in Excel
In a Scatter Plot, we use markers on data points of the intersection of the values of the x-axis and y-axis. Generally, we apply small bubbles as the markers. Do you know that these bubbles’ sizes can be changed?
Suppose, we have a Sales Report of a certain company. It includes the Number of Products, Sales, and Percentage of Market Share.
Now, we’ll plot a Scatter Chart from this dataset. Then, it will change its bubble size using the Marker Options.
Let’s go down further to find out more.
⭐ Step 1: Insert Scatter Plot
We can change the bubble size of a Scatter Plot. Follow the steps below.
- At first, select cells in the B4:C10 range.
- Secondly, go to the Insert tab.
- Then, select Insert Scatter (X, Y) or Bubble Chart > Scatter Chart.
- It creates a Scatter Plot just like the one below.
⭐ Step 2: Use Marker Options
We can see that the markers used in the above chart are circles or bubble-shaped. But we want to increase the size of these bubbles.
- Now, double-click on any marker.
- At this moment, the Format Data Series task pane opens.
- Then, click on the Fill & Line icon.
- After that, select the Marker section.
- Later, expand the Marker Options menu.
- Next, choose Built-in from the options and set the Size to 15.
- At this point, return to the chart. The size of the markers has been changed already.
⭐ Step 3: Add Axis Titles
Adding Axis Titles to the chart makes it easier to interpret the chart.
- Firstly, go to the Chart Design tab.
- Secondly, select Add Chart Element from the ribbon.
- From the drop-down list, select Axis Titles > Primary Horizontal.
- Similarly, add the Primary Vertical title too.
- Then, edit and give suitable Axis Titles relevant to the dataset.
⭐ Step 4: Add Chart Title
The Chart Title gives us a brief summary of the displayed information. In this section, we’ll add it to our chart.
- For instance, add Chart Title to the chart.
- Firstly, go to the Chart Design tab.
- Secondly, select Add Chart Element from the ribbon.
- From the drop-down list, select Chart Title > Above Chart.
- Then, edit and give a suitable Chart Title relevant to the dataset.
Read More: How to Create a Bubble Chart in Excel with Labels (4 Easy Ways)
How to Change Bubble Size in a Bubble Chart in Excel
The Bubble Chart is a tool for three-dimensional data visualization. With the Bubble Chart, you may plot the third variable in addition to the standard two variables (x and y). The first two variables are represented by coordinates, whereas the third is by the bubble’s size. We can also change the bubble size of a Bubble Chart. Let’s jump into the different methods.
1. Scaling Bubble Size to Change Bubble Size in Scatter Plot in Excel
We can change the bubble size manually from the Format Data Series task pane.
Steps:
- Initially, select cells in the B6:D10 range.
- Secondly, go to the Insert tab.
- Later, select Insert Scatter (X, Y) or Bubble Chart > Bubble.
- At this moment, a Bubble Chart opens beside the dataset.
In the chart above, there is a negative value in the vertical axis that we don’t need. So, we’ll remove it.
- Primarily, double-click on the vertical axis.
- At this point, we can see the Format Axis task pane.
- Then, click on the Axis Options icon.
- Next, set 0 as the Minimum value in the Bound category.
- Through our previous actions, the Bubble Chart looks like the one below.
Now, the negative part is no longer visible.
- Then, double-click on any data point.
- It opens the Format Data Series task pane.
- Initially, click on the Series Options icon.
- Secondly, expand the Series Options menu.
- Then, write down 200 in the box of Scale bubble size to.
- It is clearly visible that the bubble of this chart has become bigger than before.
2. Adding a New Series to Change Bubble Size in Scatter Plot in Excel
The bubble size in Excel’s Bubble Charts is fixed based on the largest bubble in the chart. By double-clicking on any series, you may adjust the maximum size by choosing a preset multiplier like 200 percent on the Format Data Series task pane like our previous method.
But the problem arises when comparing several charts with different bubble size data. When you are using a similar bubble size multiplier, the largest bubble in each chart has the same size. Even though the bubble size numbers are different. The third variable is no longer appropriately displayed. Thus, the entire benefit of a Bubble Chart over a Line Chart or Scatter Chart invalidates.
Follow us below to grasp this topic entirely.
Suppose we have a Region-wise Sales Report of two states: Texas and Florida. This report includes the Sales Quantity and Profit percentages for the corresponding Weeks.
From the dataset, we can notice that we keep the Sales Quantity of each state the same. But the Profit in Florida is three times of Texas.
Now, we’ll plot two different Bubble Charts from this dataset.
Steps:
- In the beginning, insert the chart just like we did in Method 1.
Here, it is visible that the bubble size in the 2nd chart is not three times that of the 1st one. So, these charts aren’t giving us the right presentation of data.
- In this instance, add a new row of data in each table like the image below.
In row 10, we added the same values in each table.
- Now, right-click on the Bubble Chart.
- Then, select the Select Data option.
- However, it opens the Select Data Source dialog box.
- So, click on the Add button in the Legend Entries (Series) section.
- As a result, the Edit Series dialog box opens.
- Then, select the relevant cells for the text boxes as in the image below.
- After that, click OK.
- Thus, we added a new series named Series 2 to our 1st Bubble Chart.
- Finally, click OK.
- Similarly, add a new series for the chart of Florida.
- At present, the charts look like the below.
- Blue bubbles are for our Series 1. They are of our main dataset. Now, the proportion of the size of the bubble in these 2 charts is correct. Just look at the Profit of Week 1 in each chart. The bubble of the 2nd chart is obviously 3 times that of the 1st one.
- At this moment, click on the orange bubble of chart 1.
- As a result, the Format Data Series task pane opens.
- Initially, click on the Fill & Line icon.
- Secondly, choose No fill from the Fill section.
- Correspondingly, do this to the orange bubble of the 2nd chart.
- Momentarily, our charts look like the image below.
Now, they are perfectly proportional according to the two different graphs.
Read More: Create Bubble Chart in Excel with Multiple Series (with Easy Steps)
Conclusion
Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website ExcelDemy to explore more.
