Mrinmoy Roy
Using Bubble Matrix Charts, you can easily see data in three dimensions. In comparison to a two-dimensional scatter plot, they are particularly helpful for identifying connections between data points that might not be entirely clear. A Bubble Matrix Chart is a two-dimensional scatter plot where the data points are represented by bubbles. The value of a third data point is represented by the size of the bubbles. In this article, I will show you how to create a Bubble Matrix Chart in Excel with easy steps.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
What Is a Bubble Matrix?
A Bubble Matrix is a sort of graph that shows information using three dimensions of data. Similar to a Scatter Graph, the bubble matrix has bubble sizes controlled by a third variable. The size of the bubble is used to represent this variable.
When demonstrating relationships between three variables, particularly when the third variable is a measure of importance, bubble matrices are frequently utilized.
A bubble matrix, for instance, might be used to illustrate the connection between a company’s Market Cap, Debt-to-Equity Ratio, and Stock Price. The Market Cap of the company would be represented by the size of the bubble.
It’s important to pick the appropriate scale for the bubbles while making a Bubble Matrix. It may be challenging to see patterns in the data if the bubbles are too small. The patterns in the data could be hidden if the bubbles are too big.
Steps to Create a Bubble Matrix in Excel
I will use the following dataset of Annual Profit Growth Record to show you create a bubble matrix chart in Excel. In the Company column, there are 4 company names. In the following 3 columns, they have their annual profit growth in percentage.
Step-01: Creating Helper Ranges
Before inserting a bubble matrix chart, you need to create the following 4 helper ranges first.
These helper ranges are required to create the matrix of bubbles. Using values from these ranges for different axes will present the chart in a meaningful manner.
In Helper Range 1, you will have company names and their serial number.
In Helper Range 2, you will insert the company names and their serial number in descending order in 3 columns.
Helper Range 3 consists of a serial number followed by 0s and 0.001s.
Helper Range 4 consists of 0s followed by a serial number in descending order and 0.001s.
Read More: Excel Bubble Chart Size Based on Value (2 Suitable Examples)
Step-02: Inserting a Bubble Matrix Chart
To insert a bubble matrix chart,
- Select your dataset first.
- Then go to the Insert
- In the Charts group, click on the Insert Scatter (X, Y) or Bubble Chart drop-down.
- In the Bubble section, choose the Bubble
After that, you will get a bubble chart generated as the following image.
Read More:Â How to Change Bubble Size in Scatter Plot in Excel (with Simple Steps)
Step-03: Removing Default Labels of Two Axes
After inserting a bubble chart, it will have some default axes which you need to remove.
To remove the default axes,
- Right-click on the axes first.
- Then select Delete from the context menu.
After removing the default axes, the bubble chart will look like the following image.
Step-04: Inserting Data to Generate Bubble Matrix Chart
To generate bubbles using your dataset,
- Right-click on the bubble chart first.
- From the context menu choose Select Data.
Select Data Source dialog box will appear.
- Click on the Add button.
Then Edit Series dialog will appear.
- Leave the ‘Series name’ box empty.
- In the ‘Series X values’ box, select the serial numbers from the Helper Range 1.
- After that fill the ‘Series Y values’ box with the serial number of the company, Jespar from the Helper Range 2.
- Fill the ‘Series bubble size’ box with the profit growth rate of the company Jespar, from the main dataset.
- Finally, click OK.
- Repeat the same process for the company Tyson, Nymp, and Astrix.
When you are done adding all the company’s data, the Select Data Source dialog box will look like the following image.
Step-05: Inserting Extra Two Helper Ranges for New Labels of Axes
Now you need to import the data from Helper Range 3 and Helper Range 4. They are necessary to generate new axes.
To import data,
- Again, click on the Add button in the Select Data Range dialog box.
The Edit Series dialog box appears again.
- Leave the ‘Series name’ box empty.
- In the ‘Series X values’ box, insert the range of the SL No. column of the Helper Range 3.
- After that fill the ‘Series Y values’ box with the range of the Col1 column of the Helper Range 3.
- Now insert the range of the Col2 column of the Helper Range 3 in the ‘Series bubble size’
- Finally, click OK.
To import the data from the Helper Range 4,
- Again click on the Add button in the Select Data Source dialog box.
In the Edit Series dialog box,
- Leave the ‘Series name’ box empty.
- In the ‘Series X values’ box, insert the range of the Col1 column of the Helper Range 4.
- After that fill the ‘Series Y values’ box with the range of the Col2 column of the Helper Range 4.
- Now insert the range of the Col3 column of the Helper Range 4 in the ‘Series bubble size’
- Finally, click OK.
- Finally hit OK in the Select Data Source dialog box.
Step-06: Adding New Axes
To add a new horizontal axis,
- Right-click on the little bubbles below the yellow bubbles.
- Then select Format Data Series.
- In the Format Data Labels dialog box, check the Value From Cells
Data Label Range dialog box appears.
- Insert the range C4:E4.
- Then click OK.
Repeat the same process to insert a vertical axis.
- This time insert the range of company names in the Data Label Range dialog box.
- Then click OK.
After that, the bubble matrix chart with new axes will look like the following picture.
Step-07: Adding Labels for Bubble Sizes
To add labels to the bubbles in the chart,
- Click on the bubbles of the company, Jesper.
- Now click on the plus icon at the top-right corner of the bubble chart.
- Then select the Data Labels command from the Chart Elements.
- Now right-click on the numbers inside the bubbles of the company, Jesper.
- Select Format Data Labels from the context menu.
- Deselect Y Value in the Format Data Labels dialog box.
- Then select the option Bubble Size in the Format Data Labels dialog box.
The profit growth rate of each company will be integrated inside the bubbles of the bubble matrix chart.
Now the final bubble matrix chart will look the this:
Advantages of Bubble Matrices
- Allows for easy comparison of data.
- Helps to track changes over time.
- Helps to spot patterns and trends.
Disadvantages of Bubble Matrices
- It may be difficult to interpret if there is a lot of data.
- Can be time-consuming to create.
Practice Section
You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the topics discussed in this article.
Conclusion
To sum up, I have discussed steps to create a bubble matrix in Excel. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
