What Is Bubble Chart In Excel?
The Bubble Chart in Excel is a form of the Scatter plot. It represents data points as bubbles, with an added dimension of the given values denoted by the size of the bubbles. And while the X and Y axes are value-based, the plot uses another value-based axis (Z) to represent the sizes, which enhances the plot’s data comparison capabilities.
Users can use the Bubble Chart to visually highlight numbers given in a financial dataset as bubbles of different sizes based on their values.
For example, the table below shows a list of products, units sold, prices, and market share data.
To review each product’s market share visually, select cell range A1:D6, then, the generated Bubble Chart is shown below.
As per the given data, Product 5 is the top-selling item, with the maximum number of units sold. On the other hand, Product 1’s bubble is the smallest in size, which indicates that it has the least market share, even though the units of Product 1 sold exceeded that of Product 3.
Since the above Bubble Chart is market share based, the bubble representing Product 3 is the biggest, indicating that its market share is the highest among the five products.
Table of contents
Key Takeaways
- The Bubble Chart in Excel represents a data point as a bubble with 3 dimensions. While the X and Y axis help fix its position, the third dimension (Z) indicates the data point size based on its value.
- This chart helps users to analyze keyword density in SEO, ROI from digital marketing campaigns, and sales reports in the finance domain.
- We can use the critical data elements, such as data labels, axis and chart titles, and bubbles in subtle colors, to make the chart more presentable.
How To Create Bubble Chart In Excel?
We can create the Bubble Chart in Excel in 2 ways, namely,
- Access from the Excel ribbon.
- Use the keyboard shortcut.
Method #1 – Access from the Excel ribbon
Select the required data range to plot → select the Insert tab → click the Recommended Charts option.
The Insert Chart window opens.
Click the “All Charts” tab → select the “X Y (Scatter)” chart → select any of the “Bubble Chart” chart type → click “OK” to complete the action.
The following chart is generated.
[Alternatively, we can select the required data range to plot → select the “Insert” tab → go to the “Charts” group → click the “Scatter (X, Y) or Bubble Chart” drop-down, as shown below.
Then, select the “Bubble” option from the “Bubble” category. Once we click the required Bubble Chart, the following graph will get inserted into our active worksheet.]
The charts we obtain using the two options in the Insert tab might appear slightly different. But once we update the selected data using Select Data in the Design tab while applying the alternative Insert tab option, the two charts will appear identical.
Method #2 – Use the Keyboard Shortcut
Select the required data range to plot, and press Alt + F1 to insert a default chart.
But, as we require the Bubble Chart, click the Design tab → select the Change Chart Type option.
The Change Chart Type window → All Charts tab will open. Click the “All Charts” tab → select X Y (Scatter) chart → select Bubble Chart and the appropriate chart under it. And then click OK to complete the action.
The following table contains a list of projects, their costs, profits, and profit rates. We will review the profit rates of the projects visually using the Bubble Chart Excel 3 variables, where the data includes three variables, Cost, Profit, and Profit Rate.
The steps to create a Bubble Chart are,
- Click on a cell in the above table, or select the entire table, and select Insert → Recommended Charts to open the Insert Chart window.
In the Insert Chart window, click X Y (Scatter) chart category in the “All Charts” tab.
Click OK to close the window, and the below chart appears in the current worksheet.
- The above Bubble Chart in the worksheet is profit-based, and we require a profit rate-based plot. Therefore, to modify and obtain the required Bubble graph, click the chart area to enable the Design tab, and click Select Data from the Design tab to access the Select Data Source window.
In the Select Data Source window, select the Profit in $ option in the Legend Entries field, and click Edit.
The Edit Series window opens.
Update the Series name field to achieve a profit-rate based Bubble Chart Excel 3 variables. And click OK to close the window.
Click OK to close the Select Data Source window.
And the below chart appears.
- Click the chart area to enable the Chart Elements option (‘+’ icon), and check the Axis Titles and Legend boxes.
- Double-click the chart title and axis titles elements in the chart area, one at a time, to update them.
Thus, the required Bubble Chart will be as follows:
Adding data labels and changing the bubble colors will help interpret the plot easily. We will see how to apply such features in the Excel Bubble Chart examples explained in the next section.
Examples
Check out the following Bubble Chart in Excel examples to use the plot in different scenarios.
Example #1
We will see an example of the Bubble Chart in Excel with labels and bubbles in distinct colors.
The table below shows different ad categories, the number of ads created, the cost per ad, and the total revenue generated from each ad category. We will visually review the total revenue generated from the different ad categories.
The steps to create the Bubble Chart in Excel for the above data are,
- Step 1: Click a cell in the above table, or select the entire table range. And follow the path Insert → Recommended Charts to open the Insert Chart window.
In the Insert Chart window, go to All Charts → X Y (Scatter) chart → Bubble Chart, and select the required chart from the given options.
Click OK to close the window, and the below chart appears.
- Step 2: Click the chart area to enable the Design tab, and select the Select Data option.
Click on the Cost Per Ad in $ option under the Legend Entries field, and click Edit in the Select Data Source window.
The Edit Series window opens.
Update the Series name as an excel cell reference to the Total Revenue Generated in $ heading, and click OK to close the window.
And click OK in the Select Data Source window to close it, and we will obtain the required plot.
- Step 3: Right-click a data point or bubble, and select Format Data Series from the context menu.
- Step 4: In the Format Data Series window, check the option under Fill in the Fill tab to view the bubbles in distinct colors.
- Step 5: Click the chart area to enable the Chart Elements option (‘+’ icon), and check the Data Labels box. And click the right arrow against it to select More Options.
The Format Data Labels window opens. Select the first option under Label Options to view label values from chosen cells. This action will open the Data Label Range window.
Enter the ad categories cell range in the data label range field in the Data Label Range window, and click OK.
And uncheck the Y Value option under Label Options to view only the ad categories in the labels, and Click OK to close the pane.
- Step 6: Click the chart area to enable the Chart Elements option, and check the Axis Titles option.
- Step 7: Update the chart and axis titles as explained in step 4 of the previous section to get the below Bubble Chart.
We can see that the Yellow bubble is the largest among all the bubbles in size. Further, it is the farthest along the horizontal axis and the highest point along the vertical axis. Thus, we can infer from the above Bubble Chart that the number of Ad_4 category ads created topped the list, and they generated the highest revenue. Also, the cost involved was high for Ad_4 category ads.
Likewise, the Grey bubble is the smallest in size. It indicates that the Ad_3 category ads generated the lowest revenue, though the total number of ads created under this category and the cost incurred were nominal.
Example #2
The below table shows the expenses, sales, and profits of a set of products. We will compare the profits of the six products visually using the 3D Bubble Chart in Excel.
The steps to plot a 3D Bubble Chart in Excel for the above data are,
- Step 1: Choose the table range or click a cell in the table, and follow the path Insert → Scatter (X, Y) or Bubble Chart → 3-D Bubble Chart.
- Step 2: Click the chart area to enable the Design tab, and select the Select Data option.
The Select Data Source window opens.
Uncheck all the options under the Legend Entries field, and click Add.
The Edit Series window will open where we must update the four fields as depicted below to plot the correct Bubble Chart in Excel. And click OK to close the window.
- Step 3: Click OK in the Select Data Source window to close it.
- Step 4: Right-click the horizontal axis, and select Format Axis from the context menu.
Set the horizontal axis settings to avoid the negative axis values, as shown below.
Likewise, set the vertical axis settings by clicking on the axis and updating the highlighted Format Axis options in the image below.
- Step 5: The chart appears like a 2-D Bubble Chart. So, click the chart area to enable the Design tab, and choose the Change Chart Type option.
And select the 3-D Bubble Chart option in the X Y (Scatter) chart category from the All Charts tab in the Change Chart Type window.
- Step 6: Right-click a bubble, and select Add Data Labels from the context menu.
And click the chart area to enable the Chart Elements option, and click the Data Labels right arrow to select More Options.
Next, choose the first option under the Label Options to display values from the chosen cells.
The Data Label Range window opens. Enter the product names cell range in the Select Data Label Range field.
And uncheck the Y Values option under the Label Options.
- Step 7: Click the chart area to enable the Chart Elements option, and check the Axis Titles box.
- Step 8: Finally, update the chart and axis titles, as explained in the previous sections, to achieve the following 3-D Bubble Chart.
The above chart shows that the Product_6 bubble is the biggest, indicating that its profit is the highest compared to the other products. And its position indicates that its sales and expenses are also the highest.
Likewise, the Product_5 bubble is the smallest size-wise, denoting that the profit from Product_5 is the least. Also, it is the closest to the zero units of the axes, implying that its sales and expenses are the lowest.
Advantages And Disadvantages
The advantages of the Bubble Chart are:
- We can plot three-dimensional datasets.
- The distinct bubble colors and sizes make the chart a good tool for visual analysis.
The disadvantages of the Bubble Chart are:
- The Bubble Chart requires prior knowledge to understand it effectively.
- In some scenarios, the bubbles overlap if their coordinates are close or the same. And this might hide a few data points in the plot, leading to incorrect analysis.
Interpretation And Uses Of The Bubble Chart In Excel
Interpretation of the Bubble Chart
- A bubble in the Bubble Chart represents a single data point with 3 dimensions. While its horizontal and vertical positions are on the X and Y axes, the third dimension (Z-axis) represents its size.
- When we createan Excel Bubble Chart based on a factor such as profitability and market share, the bubble size indicates the specific entity’s contribution to the factor. Thus, we can perform a comprehensive data comparison and understand the relationships between the given numeric variables.
Uses of the Bubble Chart
The uses of the Bubble Chart in Excel are as follows:
- Present sales insights visually in the form of a chart.
- Analyze employee attendance, company revenue, and customer base growth.
- Compares marketing strategies to determine the most cost-effective option.
- Review the impact of ad campaigns and promotions.
- Assess the effect of superior CPC rates on aspects such as website conversions.
Important Things To Note
- Before creating the Bubble Chart in Excel, ensure the source data contains the x values in the first column or row. And the corresponding y and z values should be in the adjacent columns or rows.
- A Bubble Chart works well when the source data contains at least three variables (columns). And each column or row of data should include three numeric values.
- Ensure not to include the column or row headings when selecting the data range to plot the Bubble Chart. Otherwise, the plotted graph might be incorrect.
Frequently Asked Questions (FAQs)
The Bubble plot in Excel is in the Charts group of the Insert tab and create it in the following ways,
• Use the Recommended Charts option or directly select the Bubble Chart under the Scatter (X, Y) or Bubble Chart option.
• Otherwise, press the keyboard shortcut Alt + F1 to insert a default Column chart. And then, use the Change Chart Type option from the Design tab to set the required Bubble Chart from the Change Chart Type window.
We can use an Excel Bubble Chart with multiple series in the following way.
For example, the following tables contain a set of products, their expense, sales, and profit data at two firms. We will compare the profits from the two firms’ products.
The steps to create a Bubble Chart are as follows:
• Step 1: Click on a cell outside the two tables, and follow the path Insert → Scatter (X, Y) or Bubble Chart → Bubble Chart.
We will see a blank chart. Click the empty chart area to enable the Design tab, and select the Select Data option.
• Step 2: In the Select Data Source window, click Add under Legend Entries to add a data series for Firm A.
The Edit Series window opens, where we must update the four fields, as depicted below, and click OK.
Next, click Add again to perform the same process for creating a data series for Firm B.
Click OK to close the Select Data Source window. And we will obtain the required Bubble Chart.
• Step 3: Click the chart area to enable the Chart Elements option (‘+’ icon), and check the boxes against the required chart elements. And click the Data Labels right arrow to select More Options.
The Format Data Labels window will open. Select the first option under Label Options, which will open the Data Label Range window.
Enter the cell range of products in Firm A as the data label range, and click OK.
And uncheck the Y Value option under Label Options to display only the products in the data labels.
Next, click a data label on one of the Firm B bubbles, and repeat the above process to display only the products in the data labels.
• Step 4: Finally, update the chart and axes titles to achieve the below Bubble Chart for multiple series.
The largest Blue and Orange bubbles in the above plots are products P2 and P5 in firms A and B, respectively. It indicates that these two products registered the highest profits in the respective firms.
On the other hand, their positions along the horizontal and vertical axes show that their sales and expenses are also relatively high.
Further, the bubbles’ positions indicate that the profits at Firm A are better than Firm B’s profit figures. Also, the sales and expenses are higher at Firm A than at Firm B.
We can use a Bubble plot in Excel in the following scenarios:
• The source data contains at least three values in rows or columns in the order of x, y, and z values.
• The source data includes multiple series for comparison.
Download Template
This article must help understand the Bubble Chart in Excel, with its formula and examples. We can download the template here to use it instantly.
Recommended Articles
This has been a guide to Bubble Chart In Excel. Here we learn how to create Bubble Charts along with examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply