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

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Create a Bubble Matrix in Excel


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.


How to Create a Bubble Matrix in Excel: 7 Easy Steps

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: How to Create Bubble Chart in Excel


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

Consequently, a bubble chart will be created as the following image.

Read More: How to Create a 3D Bubble Chart in Excel


Step-03: Removing Default Labels of Two Axes

After inserting a bubble chart, it will have some default axes that 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

Read More: How to Create 4 Quadrant Bubble 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 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.


Download Practice Workbook


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 as soon as possible. Goodbye!


Related Articles


<< Go Back To Bubble Chart in Excel | Excel Charts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo