How do you create a matrix chart in Excel?

Unfortunately, the chart is not supported in Excel, meaning you will have to build it from scratch on your own. Check out the Chart Creator Add-in, a newbie-friendly tool for creating advanced Excel charts in just a few clicks.

In this step-by-step tutorial, you will learn how to plot this highly customizable Excel quadrant chart from the ground up:

How do you create a matrix chart in Excel?

Getting Started

For illustration purposes, let’s assume you have set your mind to track the performance of every marketing channel your high-end brand is using and separate the wheat from the chaff. What should be prioritized, and what should be cast aside?

In this case, the quadrants will split the chart into four areas, effectively grouping together the best- and worst-performing options to help you make well-informed decisions.

Here is a sample table showing the amount of money spent on each marketing channel along with the revenue it generated:

How do you create a matrix chart in Excel?

So, let’s get started.

Step #1: Create an empty XY scatter chart.

Why empty? Because as experience shows, Excel may simply leave out some of the values when you plot an XY scatter chart. Building the chart from scratch ensures that nothing gets lost along the way.

  1. Click on any empty cell.
  2. Switch to the Insert tab.
  3. Click the “Insert Scatter (X, Y) or Bubble Chart.”
  4. Choose “Scatter.”

How do you create a matrix chart in Excel?

Step #2: Add the values to the chart.

Once the empty chart appears, add the values from the table with your actual data.

Right-click on the chart area and choose “Select Data.”

How do you create a matrix chart in Excel?

Another menu will come up. Under Legend Entries (Series), click the “Add” button.

How do you create a matrix chart in Excel?

Next, do the following:

  1. For “Series X values,” highlight all the values in column Expenses (column B).
  2. For “Series Y values,” select all the values in column Revenue (column C).
  3. Select “OK” to set the series.
  4. Select “OK” again to close the dialog box.

How do you create a matrix chart in Excel?

In addition, delete the gridlines by right-clicking on the chart element and choosing “Delete.” And don’t forget to change the chart title.

Step #3: Set the rigid minimum and maximum scale values of the horizontal axis.

You need to set the horizontal axis scale in stone as a means to prevent Excel from rescaling it—and shifting the chart around that way—when you alter your actual data.

To do that, right-click on the horizontal axis (the numbers along the bottom of the chart) and choose “Format Axis.”

How do you create a matrix chart in Excel?

In the task pane that pops up, do the following:

  1. Switch to the Axis Options tab.
  2. Set the Minimum Bounds value to zero.
  3. Change the Maximum Bounds value to the maximum number based on your data (in our case, that’s 30,000).

How do you create a matrix chart in Excel?

Step #4: Set the rigid minimum and maximum scale values of the vertical axis.

Without closing the pane, switch over to the vertical axis and repeat the steps outlined above.

How do you create a matrix chart in Excel?

Step #5: Create a new table for the quadrant lines.

Here comes the hard part. Having laid the groundwork, you now need to place four dots on each side of the chart to draw the accurate quadrant lines based off of the axis numbers.

To make it happen, set up the following table next to your actual data:

How do you create a matrix chart in Excel?

While each element is pretty much self-explanatory, calculating the X and Y values for each category might sound complicated at first, but in reality, you will get through it in less than three minutes.

So how do you figure out your values? Just follow the instructions below:

  1. For Horizontal line (left):
    1. Set the X value to zero.
    2. Set the Y value to half of the vertical axis Maximum Bounds value, which you have previously set (140,000/2=70,000).
  2. For Horizontal line (right):
    1. Set the X value to the horizontal axis Maximum Bounds value (30,000).
    2. Set the Y value to half of the vertical axis Maximum Bounds value (140,000/2=70,000).
  3. For Vertical line (bottom):
    1. Set the X value to half of the horizontal axis Maximum Bounds value (30,000/2=15,000).
    2. Set the Y value to zero.
  4. For Vertical line (top):
    1. Set the X value to half of the horizontal axis Maximum Bounds value (30,000/2=15,000).
    2. Set the Y value to the vertical axis Maximum Bounds value (140,000).

Here is how it looks:

How do you create a matrix chart in Excel?

Step #6: Add the quadrant lines to the chart.

Once you have set up the table, it’s time to move the values to the chart.

Right-click on the chart, choose “Select Data,” and click “Add” in the window that appears.

First, let’s add the horizontal quadrant line.

Click the “Series X values” field and select the first two values from column X Value (F2:F3). Move down to the “Series Y values” field, select the first two values from column Y Value (G2:G3). Under “Series name,” type Horizontal line. When finished, click “OK.”

How do you create a matrix chart in Excel?

Next, tackle the vertical quadrant line. Click the “Add” button again and move the remaining data to the chart.

Click the “Series X values” field, select the remaining two values from column X Value (F4:F5). Move down to the “Series Y values” field and select the remaining two values from column Y Value (G4:G5). Under “Series name,” type Vertical line. When finished, click “OK.”

How do you create a matrix chart in Excel?

Step #7: Change the chart type of the newly-added elements.

The worst is behind us. Now it’s time to connect the dots.

Right-click on any of the four dots and pick “Change Series Chart Type” from the menu.

How do you create a matrix chart in Excel?

In the “Chart Type” dropdown menu next to the two series representing the quadrant data (“Vertical line” and “Horizontal line”), choose “Scatter with Smooth Lines.”

How do you create a matrix chart?

How to build matrix diagrams.
Define your purpose. ... .
Recruit your team. ... .
Identify and collect the data sets. ... .
Select the appropriate matrix type. ... .
Determine how to compare your data. ... .
Document the matrix relationships. ... .
Review and draw conclusions..

Is there a matrix template in Excel?

A decision matrix template is an Excel tool where you list all of your options and criteria which you also give them numerical values and coefficients to be able to calculate the weighted average of each option to determine which one is superior compared to others.

How do I create a 4 quadrant matrix in Excel?

You can also manually convert a scatter plot to a four-quadrant graph.
First, delete the trend line from your scatter diagram..
Second, reformat your X-Axis. ... .
Third, reformat your Y-Axis. ... .
Once you have updated both the X and Y Axis Values, you should have a XY Scatter Diagram with Four Quadrants!.

Is there a matrix function in Excel?

The MMULT function returns the matrix product of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2.