Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create a Matrix Chart in Excel (2 Common Types)

If you are looking for ways to create a matrix chart in Excel, this article is for you. A matrix chart is very useful for showing multiple series of data easily in a graph. So, let’s start with the main article to know the details of the procedures to create a matrix chart.

Download Workbook


2 Ways to Create a Matrix Chart in Excel

Here, we have the records of the selling prices, cost prices, and profits of some of a company’s products. By using this data range we can create 2 types of matrix charts; Bubble Matrix Chart, and Quadrant Matrix Chart. In this article, we are going to illustrate the necessary steps for creating these 2 types of charts.

how to create a matrix chart in Excel

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


Type-01: Create a Matrix Bubble Chart in Excel

The procedures for creating a Matrix Bubble Chart will be discussed in the following steps of this section. The selling prices, cost prices, and profits of the 5 products; Orange, Apple, Kiwi, Walnut, and Raspberry will be arranged through the bubbles in this chart to visualize them easily.

matrix bubble chart


Step-01: Creating Additional New Data Ranges

To create 5 different series for the 5 available products in the Bubble chart we will need 2 additional ranges.
➤ In the Additional Range 1, you can add two columns; one is containing the product names and the other is containing the serial number of the products.

additional range

➤ For the Additional Range 2 after entering the product names in the first column, you have to add 3 extra columns (as we have 3 sets of values in the Selling Price, Cost Price, and Profit columns). Make sure that the serial numbers in these columns are arranged in reverse orders.

additional range


Step-02: Inserting Bubble Chart to Create a Matrix Chart in Excel

In this step, we will insert a Bubble chart for the 3 sets of values and then rearrange the bubbles with the help of the two additional ranges.
➤ Select the range of values (C4:E8) and then go to the Insert Tab >> Charts Group >> Insert Scatter (X, Y) or Bubble Chart Dropdown >> Bubble Option.

how to create a matrix chart in Excel

After that, the following Bubble chart will be created.

inserting bubble chart

➤ To rearrange the bubbles select the chart and right-click on it.

how to create a matrix chart in Excel

➤ Then choose the option Select Data from various options.

inserting bubble chart

After that, the Select Data Source dialog box will open up.
➤ Select the already created series Series1 and click on Remove.

inserting bubble chart

➤ After removing Series1 click on Add to include a new series.

how to create a matrix chart in Excel

Then the Edit Series wizard will pop up.
➤ For Series X values select the serial numbers of the Additional Range 1 of the Bubble sheet and then for Series Y values select the serial numbers in the three columns of Product Orange of the Additional Range 2.

inserting bubble chart

Series bubble size will be the selling price, cost price, and profit of the product Orange and then press OK.

inserting bubble chart

In this way, we have added a new series Series 1.
➤ Click on the Add button to enter another series.

inserting bubble chart

➤ For Series X values select the serial numbers of the Additional Range 1 and then for Series Y values select the serial numbers in the three columns of Product Apple of the Additional Range 2.
➤ The Series bubble size will be the selling price, cost price, and profit of the product Apple and then finally press OK.

how to create a matrix chart in Excel

Then the new series Series2 will appear.

inserting bubble chart

Similarly, complete all of the 5 series for the 5 products and press OK.

inserting bubble chart

Then you will get the following Bubble chart.

how to create a matrix chart in Excel


Step-03: Removing by Default Labels of Two Axes

After rearranging the bubbles in the chart we will have some default labels that will not be used in this chart so we have to remove them.
➤ Select the labels on the X-axis and then right-click on them.

how to create a matrix chart in Excel

➤ Choose the option Format Axis.

removing by default labels

After that, the Format Axis pane will appear on the right side.
➤ Go to the Axis Options tab >> expand the Labels option >> click on the dropdown symbol of the Label Position box.

removing by default labels

➤ From various options choose None.

removing by default labels

Then the Label Position will be changed to None.

how to create a matrix chart in Excel

In this way, we have removed the labels of the X-axis and do this similar process for Y-axis also.

removing by default labels

Finally, we have discarded all of the default labels from the chart.

removing by default labels


Step-04: Adding Two Extra Ranges for New Labels of Axes

To add our desired new labels for this chart we will add two extra ranges in this step.
➤ For the X-axis label, we have entered a 3-row and 3-column data range. Where the first column contains serial numbers, the second column contains 0 and the last column is for the bubble width (0.001 or whatever you want).

how to create a matrix chart in Excel

➤ Similarly, create the Additional Range 4 for the labels of the Y-axis. Here, the first column contains 0, the second column contains the serial numbers in reverse order and the last column is for the bubble widths which is 0.001.

additional range


Step-05: Adding New Series for Labels to Create a Matrix Chart in Excel

➤ To add the new 2 series to the chart Right-click on the chart and then choose the Select Data option.

how to create a matrix chart in Excel

➤ Click on Add in the Select Data Source dialog box.

adding new series

After that, the Edit Series wizard will pop up.
➤ For Series X values select the first column of the Additional Range 3 and for Series Y values select the second column and choose the third column for the Series bubble size.
➤ Finally, press OK.

adding new series

In this way, we have created the new series Series6 and now press Add to enter another series.

adding new series

➤ In the Edit Series dialog box, for Series X values select the first column of the Additional Range 4, for Series Y values select the second column and choose the third column for the Series bubble size.
➤ Finally, press OK.

how to create a matrix chart in Excel

In this way, we have added Series7 for Y-axis labels.

adding new series


Step-06: Adding New Labels

➤ Click on the chart and then select the Chart Elements symbol.

how to create a matrix chart in Excel

➤ Check the Data Labels option.

adding new labels

After that, all of the data labels will be visible on the chart.

adding new labels

➤ Select the labels of the X-axis and then Right-click here.

adding new labels

➤ Click on the Format Data Labels option.

how to create a matrix chart in Excel

Afterward, the Format Data Labels pane will be visible on the right side.
➤ Go to the Label Options Tab >> expand the Label Options Option >> check the Value From Cells Option.

adding new labels

After that, the Data Label Range dialog box will open up.
➤ Select the column headers of the values in the Select Data Label Range box and then press OK.

adding new labels

Then, you will return to the Format Data Labels part again.
➤ Uncheck the Y Value from the Label Options and scroll down to the downside to see all of the options of the Label Position.

adding new labels

➤ Select the Below option.

adding new labels

In this way, we will be able to add our desired X-axis labels.

adding new labels

➤ Now, select the Y-axis labels and then Right-click here.

how to create a matrix chart in Excel

➤ Click on the Format Data Labels option.

adding new labels

Afterward, the Format Data Labels pane will be visible on the right side.
➤ Uncheck the Y Value option and click on the Value From Cells option among various Label Options.

adding new labels

After that, the Data Label Range dialog box will open up.
➤ Select the product names in the Select Data Label Range box and then press OK.

adding new labels

Then, you will be taken to the Format Data Labels part again.
➤ Click on the Left option under the Label Position.

adding new labels

Finally, we will have the name of the products on the Y-axis labels.

how to create a matrix chart in Excel


Step-07: Adding Labels for Bubbles

➤ Select the bubbles with the number 5 and then Right-click on it.

how to create a matrix chart in Excel

➤ Choose the Format Data Labels option.

adding labels for bubbles

After that, the Format Data Labels pane will open up in the right portion.
➤ Check the Bubble Size option and uncheck the Y Value option.

adding labels for bubbles

After that, the labels of the bubbles will be converted into the values of the Selling Prices, Cost Prices, and Profits.

adding labels for bubbles

➤ You can remove the chart title by clicking on the Chart Elements symbol and then unchecking the Chart Title option.

adding labels for bubbles

The final outlook of the chart will be like the following figure.

how to create a matrix chart in Excel


Similar Readings


Type-02: Create a 4-Quadrant Matrix Chart in Excel

Here, we will be creating the other type of Matrix chart which is the 4-Quadrant Matrix chart. One thing is to remember that here you can only create a chart for 2 sets of values. So, we will use the selling prices and the cost prices of the 5 products to make a Quadrant chart.

Quadrant matrix chart


Step-01: Inserting Scattered Graph to Create a Matrix Chart in Excel

➤ Select the range of values (C4:D8) and then go to the Insert Tab >> Charts Group >> Insert Scatter (X, Y) or Bubble Chart Dropdown >> Scatter Option.

how to create a matrix chart in Excel

After that, the following graph will appear.

insert scattered graph

Now, we have to set the upper bound and lower bound limits of the X-axis and Y-axis.
➤ Firstly, select the X-axis label and then Right-click here.

insert scattered graph

➤ Choose the Format Axis option.

how to create a matrix chart in Excel

Afterward, you will get the Format Axis pane on the right side.
➤ Go to the Axis Options Tab >> expand the Axis Options Option >> set the limit of the Minimum bound as 0.0 and the Maximum bound as 5000.0 because the maximum Selling Price is 4996.

insert scattered graph

Then, we will have the modified X-axis labels with new limits and we don’t need to modify the Y-axis labels as the upper limit of this axis is here 3500 which is close to the maximum Cost Price of $3,197.00.

how to create a matrix chart in Excel


Step-02: Creating Additional Data Range

For adding the 2 lines to have 4 quadrants we have to add an additional data range here.
➤ Create the following format of the data table with two portions for the Horizontal and the Vertical and the two columns for the two coordinates X and Y.

additional data range

➤ For the Horizontal part add the following values in the X and Y coordinates.
X → 0 (minimum bound of X-axis) and 5000 (maximum bound of X-axis)
Y → 1750 (average of the minimum and maximum values of the Y-axis → (0+3500)/2 → 1750)

additional data range

➤ For the Vertical part add the following values in the X and Y coordinates.
X → 2500 (average of the minimum and maximum values of the X-axis → (0+5000)/2 → 2500)
Y → 0 (minimum bound of Y-axis) and 3500 (maximum bound of Y-axis)

how to create a matrix chart in Excel


Step-03: Addition of Four Points in Graph to Create Quadrant Lines

➤ Select the graph, Right-click here, and then choose the Select Data option.

adding 4 points

Afterward, the Select Data Source wizard will open up.
➤ Click on Add.

how to create a matrix chart in Excel

After that, the Edit Series dialog box will appear.
➤ For Series X values select the X coordinates of the horizontal part of the Quadrant sheet and then for Series Y values select the Y coordinates of the horizontal part.
➤ Press OK.

adding 4 points

Then the new series Series2 will be added and to insert a new series for the vertical line click on Add again.

adding 4 points

➤ In the Edit Series dialog box, for Series X values select the X coordinates of the vertical part of the Quadrant sheet, and then for Series Y values select the Y coordinates of the vertical part.
➤ Press OK.

adding 4 points

In this way, we have added the final series Series3 also, and then press OK.

adding 4 points

Finally, we will have 2 Orange points indicating the horizontal part and 2 Ash points indicating the vertical part.

how to create a matrix chart in Excel


Step-04: Inserting Quadrant Lines to Create a Matrix Chart in Excel

➤ Select the 2 Orange points and then Right-click here.

creating quadrant lines

➤ Then choose the Format Data Series option.

how to create a matrix chart in Excel

Afterward, you will have the Format Data Series pane on the right portion.
➤ Go to the Fill & Line Tab >> expand the Line Option >> click on the Solid line option >> choose your desired color.

creating quadrant lines

➤ To hide the points, go to the Fill & Line Tab >> expand the Marker Options Option >> click on the None option.

creating quadrant lines

In this way, the horizontal line will appear in the chart.

creating quadrant lines

Similarly, create the vertical separator line also by using the 2 ash points.

how to create a matrix chart in Excel


Step-05: Inserting Data Labels

To indicate the data points with the name of the products we have to add the data label first.
➤ Select the data points and then click on the Chart Elements symbol.

adding data labels
➤ Check the Data Labels option.

how to create a matrix chart in Excel

After that, the values of the points will appear beside them and we have to convert them to the name of the products.
Right-click after selecting these data points.

adding data labels
➤ Click on the Format Data Labels option.

adding data labels

After that, you will have the Format Data Labels pane on the right side.
➤ Check the Value From Cells option from the Label Options.

how to create a matrix chart in Excel

Afterward, the Data Label Range dialog box will open up.
➤ Select the name of the products in the Select Data Label Range box and then press OK.

adding data labels

➤ Then uncheck the Y Value option and check the Left option as the Label Position.

adding data labels

Finally, the outlook of the Quadrant Matrix Chart will be like the following.

how to create a matrix chart in Excel


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice


Conclusion

In this article, we tried to cover the steps to create a Matrix Chart in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section. For exploring more Excel-related articles you can visit our ExcelDemy site.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo