How to Create a Bubble Matrix in Excel (with Easy Steps)

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.

Creating Helper Range : Create a Bubble Matrix in Excel

In Helper Range 2, you will insert the company names and their serial number in descending order in 3 columns.

Creating Helper Range : Create a Bubble Matrix in Excel

Helper Range 3 consists of a serial number followed by 0s and 0.001s.

Creating Helper Range : Create a Bubble Matrix in Excel

Helper Range 4 consists of 0s followed by a serial number in descending order and 0.001s.

Creating Helper Range : Create a Bubble Matrix in Excel

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

Inserting a Bubble Matrix in Excel

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.

Removing Default Axes : Create a Bubble Matrix in Excel

After removing the default axes, the bubble chart will look like the following image.

Removing Default Axes : Create a Bubble Matrix in Excel


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.

Inserting Data to Create a Bubble Matrix Chart in Excel

Select Data Source dialog box will appear.

  • Click on the Add button.

Inserting Data to Create a Bubble Matrix Chart in Excel

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.

Inserting Data to Create a Bubble Matrix Chart in Excel

  • 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.

Inserting Data to Create a Bubble Matrix Chart in Excel


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.

Inserting Data to Create a Bubble Matrix Chart in Excel

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.

Inserting Data to Create a Bubble Matrix Chart in Excel

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.

Inserting Data to Create a Bubble Matrix Chart in Excel

  • Finally hit OK in the Select Data Source dialog box.

Inserting Data to Create a Bubble Matrix Chart in Excel


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.

Adding New Axes

  • In the Format Data Labels dialog box, check the Value From Cells

Adding New Axes

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.

Adding New Axes


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.

Adding Labels for Bubble Sizes to Create a Bubble Matrix Chart in Excel

  • Now right-click on the numbers inside the bubbles of the company, Jesper.
  • Select Format Data Labels from the context menu.

Adding Labels for Bubble Sizes to Create a Bubble Matrix Chart in Excel

  • 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.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo