Tanjima Hossain
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.
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.
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.
➤ 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.
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.
After that, the following Bubble chart will be created.
➤ To rearrange the bubbles select the chart and right-click on it.
➤ Then choose the option Select Data from various options.
After that, the Select Data Source dialog box will open up.
➤ Select the already created series Series1 and click on Remove.
➤ After removing Series1 click on Add to include a new series.
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.
➤ Series bubble size will be the selling price, cost price, and profit of the product Orange and then press OK.
In this way, we have added a new series Series 1.
➤ Click on the Add button to enter another series.
➤ 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.
Then the new series Series2 will appear.
Similarly, complete all of the 5 series for the 5 products and press OK.
Then you will get the following Bubble chart.
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.
➤ Choose the option Format Axis.
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.
➤ From various options choose None.
Then the Label Position will be changed to None.
In this way, we have removed the labels of the X-axis and do this similar process for Y-axis also.
Finally, we have discarded all of the default labels from the chart.
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).
➤ 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.
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.
➤ Click on Add in the Select Data Source dialog box.
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.
In this way, we have created the new series Series6 and now press Add to enter another 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.
In this way, we have added Series7 for Y-axis labels.
Step-06: Adding New Labels
➤ Click on the chart and then select the Chart Elements symbol.
➤ Check the Data Labels option.
After that, all of the data labels will be visible on the chart.
➤ Select the labels of the X-axis and then Right-click here.
➤ Click on the Format Data Labels option.
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.
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.
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.
➤ Select the Below option.
In this way, we will be able to add our desired X-axis labels.
➤ Now, select the Y-axis labels and then Right-click here.
➤ Click on the Format Data Labels option.
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.
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.
Then, you will be taken to the Format Data Labels part again.
➤ Click on the Left option under the Label Position.
Finally, we will have the name of the products on the Y-axis labels.
Step-07: Adding Labels for Bubbles
➤ Select the bubbles with the number 5 and then Right-click on it.
➤ Choose the Format Data Labels option.
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.
After that, the labels of the bubbles will be converted into the values of the Selling Prices, Cost Prices, and Profits.
➤ You can remove the chart title by clicking on the Chart Elements symbol and then unchecking the Chart Title option.
The final outlook of the chart will be like the following figure.
Similar Readings
- How to Calculate Covariance Matrix in Excel (with Easy Steps)
- Multiply 3 Matrices in Excel (2 Easy Methods)
- How to Create Traceability Matrix in Excel
- Create a Risk Matrix in Excel (With Easy Steps)
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.
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.
After that, the following graph will appear.
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.
➤ Choose the Format Axis option.
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.
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.
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.
➤ 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)
➤ 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)
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.
Afterward, the Select Data Source wizard will open up.
➤ Click on Add.
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.
Then the new series Series2 will be added and to insert a new series for the vertical line click on Add again.
➤ 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.
In this way, we have added the final series Series3 also, and then press OK.
Finally, we will have 2 Orange points indicating the horizontal part and 2 Ash points indicating the vertical part.
Step-04: Inserting Quadrant Lines to Create a Matrix Chart in Excel
➤ Select the 2 Orange points and then Right-click here.
➤ Then choose the Format Data Series option.
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.
➤ To hide the points, go to the Fill & Line Tab >> expand the Marker Options Option >> click on the None option.
In this way, the horizontal line will appear in the chart.
Similarly, create the vertical separator line also by using the 2 ash points.
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.
➤ Check the Data Labels option.
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.
➤ Click on the Format Data Labels option.
After that, you will have the Format Data Labels pane on the right side.
➤ Check the Value From Cells option from the Label Options.
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.
➤ Then uncheck the Y Value option and check the Left option as the Label Position.
Finally, the outlook of the Quadrant Matrix Chart will be like the following.
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.
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.
