Bubble chart in excel is actually a type of scatter plot, in scatter plot we have data points on the chart to show the values and comparison whereas in bubble charts we have bubbles replacing those points to show the comparison and similar to the scatter plots bubble charts have data comparison on both horizontal and vertical axes.
Bubble Chart in Excel
A bubble chart in excel is used when we want to represent three sets of data in a graphical way. Out of those three data sets used to make the bubble chart, it shows two axis of the chart in a series of XY coordinates, and a third set shows the data points. With the help of an excel bubble chart, we can show the relationship between different datasets.
- This chart is an advanced scatter chart. It is a type of data visualization that tells the story of 3 dimensioned data.
- Every bubble chart in excel consists of 3 data sets. X-axis coordinate, Y-axis coordinate, and the bubble size data set. So with the help of the X & Y axis, you can visualize a third data set through the image of bubbles.
How to Create a Bubble Chart in Excel?
In a scenario where we want to use multiple bar charts to share results, we can create a bubble. Use an excel bubble chart if you want to show your three types of the quantitative data set.
This chart is very simple and easy to use. Let us understand the working of with some examples.
Example 1
Below are the steps to create bubble chart in excel –
- Create your dataset and select the data range
- Go to Insert > Recommended charts and select the bubble chart, as shown below.
- Create an excel bubble chart with below formatting
- Format X-axis
- Format Y-axis
- Format bubble colors
- Add data labels manually. Right-click on bubbles and select add data labels. Select one by one data label and enter the region names manually.
(In excel 2013 or more, you can select the range, no need of entering it manually)
So finally, your chart should look like the below chart.
An additional point is when you move the cursor on the bubble, it will show the entire information related to that particular bubble.
Interpretation
- The highest sold region is the USA but since we are showing the profitability in bubble USA’s bubble looks small due to a 21.45% profitability level. So that shows profitability in this region very low even though the sales volume is very high when compared to other regions.
- The lowest sold region is Asia, but the size of the bubble is very high thanks to the superior profitability level compared to other regions. So this will help to concentrate more on the Asia region next time due to the higher profit marginProfit MarginProfit Margin is a metric that the management, financial analysts, & investors use to measure the profitability of a business relative to its sales. It is determined as the ratio of Generated Profit Amount to the Generated Revenue Amount. read more.
Example 2
Step-1: Arrange the data and insert a bubble chart from the insert section.
Step-2: Follow the same steps are shown in example 1, and your chart should look like the below chart. (For formatting, you can do your own numbers).
Interpretation
- Chart clearly shows that company EFG is there in the market for 35 years, and its market value is 1575, and the revenue is 350.
- Company MNO is there in the market for 20 years, and last year’s revenue is 200, and the market value is 988. But IJK is there in the market for 10 years and achieved 195 as revenue. But in the graph company, the MNO size of the bubble is very large when compared to the company. Since we are showing the market value in bubble size, we see a massive change in the size of the bubble even though the revenue difference is only 5.
Advantages
- The bubble chart in excel was a better chart when it applied for more than 3 dimension data sets.
- Eye-catching bubble sizes will attract the reader’s attention.
- It visually appears better than the table format.
Disadvantages
- It may be difficult for a first time user to understand very quickly.
- Sometimes gets confused with the bubble size.
- If you are a first-time user of this chart, you definitely need someone’s assistant to understand the visualization.
- Excel 2010 and earlier versions adding data labels for large bubble graphs is a tedious task. (In 2013 and later versions, this limitation is not there).
- Overlapping of bubbles is the biggest problem if the two or more data points have similar X & Y values, the bubble may overlap, or one may be hidden behind another.
Things to Consider When Creating a Bubble Chart in Excel
- Arrange the data where you can apply the bubble chart in excel instantly.
- Decide which data set you to want to show as a bubble. For this to happen, first, you need to identify the targeted audience.
- Always format your X & Y axis to a minimum extent.
- Don’t look for fancy colors that may look ugly at times.
- Play with the background color of the chart to make it look nicely and professionally.
Bubble Chart in Excel Video
Recommended Articles
This has been a guide to Bubble Chart in Excel. Here we discuss its uses and how to create Bubble Chart in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- Chart Templates in Excel
- Area Chart in Excel
- Top 4 Must-Know Investment Banking Charts
- AutoCorrect in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
