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 3 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 quantitative data set.
This chart is very simple and easy to use. Let understand the working of with some examples.
Step-1: Create your dataset and select the data range
Step-2: Go to Insert > Recommended charts and select the bubble chart as shown below.
Step-3: Create an excel bubble chart with below formatting
Step-3.1: Format X-axis
Step-3.2: Format Y-axis
Step-3.3: Format bubble colors
Step-3.4: Add data labels manually. Right click on bubbles and select add data labels. Select one by one data labels 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.
- 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 higher profit margin.
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).
- Chart clearly shows that company EFG is there in the market for 35 years and their 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, MNO size of the bubble is very large when you 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.
- The bubble chart in excel is a better chart when it applied for more than 3 dimension data sets.
- Eye-catching bubble sizes will attract the reader’s attention.
- Visually appearing better than the table format.
- 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 definitely you 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
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 –