create quadrant chart in Excel

How to create a magic quadrant chart in Excel

If you feel inspired by the Gartner magic quadrant chart or there are other reasons, here is how to create a magic quadrant chart in Excel. Imitation of already existing visualization is a great way to learn new things with a definite goal in mind. You might have your metrics, but the main principle is to show them in a two-dimensional grid that is handy for use in Excel scatter plots. In other words, an Excel quadrant chart is a scatter chart with quadrants separated by the X and Y-axis.

 

Magic quadrant chart in Excel

Prepare your data like coordinates in two dimensions. It should be done in a way that there is a defined minimum and maximum. For example, I have a table that contains employee evaluations by two parameters from 0 to 10.

Excel scatter chart dataset

Here are steps on how to create a quadrant chart in Excel, but you can download the result below.

1. Select columns with X and Y parameters and insert a scatter chart.

Excel scatter chart

2. Select the horizontal axis of the axis and press shortcut Ctrl + 1.

3. Set the minimum, maximum, and position where the vertical axis crosses. Sometimes it is necessary to leave a gap for the situation when values reach maximum or minimum. The crossing should be the middle point of your scale.

Excel scatter chart axis settings

4. Repeat the previous two points for Y-axis. The result will look like this.

Excel scatter plot custom axis crossing

5. Remove unnecessary scatter chart elements like gridlines and title.

6. Change chart dimensions to square. You can do that in the Format tab after selecting a chart.

change Excel chart dimentions

7. If you want to hide axis labels, you can do that by using Excel format code. Select axis labels and press Ctrl + 1. In the Number section, use format code like this.

Excel format code to hide axis labels

At this point, the result looks almost like a magic quadrant chart.

Excel halfmade magic quadrant chart

8. You can increase marker size. Most importantly, add custom data labels. Select data labels and press Ctrl + 1. Add values from cells.

add custom data labels in Excel chart

After applying them, keep only Value From Cells selected. Choose label position above data points if you like.

almost finished Excel magic quadrant chart

9. In conclusion, add necessary graphical elements. Here is an example that will help you understand how to add an object to an Excel chart without further problems with copying or moving.

Here you can download the Excel file result.

Excel magic quadrant chart

3 other visualizations in Excel

However, if you want to create other interesting charts in Excel, I recommend a few other posts from this blog:

  1. Rocket chart.
  2. Pac-Man chart.
  3. Glowing chart.

 

0 comments on “How to create a magic quadrant chart in Excel

Leave a Reply

Your email address will not be published.