The Four Quadrant – Matrix Model is a valuable decision making tool. This article will show you how to create a Four Quadrant – Matrix Model in an Excel chart, add multi-line data labels to each data point, add a graphic background, and more.
Another article shows you how to create a dynamic Four Quadrant – Matrix Model in Excel that has dynamic quadrants that change. Dynamic matrix charts are great for example, if you need quadrants that demarcate above and below averages.
The Four Quadrant – Matrix Model should be in the top drawer of every toolkit carried by consultants, executive and strategy teams, project managers, and product management teams. Four Quadrant – Matrix Models are used to solve problems such as,
The Four Quadrant – Matrix Model won’t make a decision for you or your team, but it will help you understand the relationships between alternatives – it makes decisions easier.
The discussion and decisions that evolve from Four Quadrant – Matrix Models are valuable. These models ensure that topics Critical to Success are discussed. For those of us who have very visual memories it enables us to see and remember the relative positions and weights of different alternatives.
For all that power you’d think Excel would have an FQMM built in, but it doesn’t. With a few tips and tricks however, we can turn a bubble chart or scatter chart into a pretty good FQMM.
Four Quadrant – Matrix Models need more than just a bubble chart or scatter chart. They also need,
Having done this a few times I can tell you that my first approach was to use a scatter chart and include some fancy-pants formulas that would calculate the average and then add additional scatter chart data points to draw the horizontal and vertical mid-point lines. It took a lot of work to do it with a scatter chart and it wasn’t as flexible and didn’t look as good as the technique I’ll show you that uses a bubble chart.
If you use a bubble chart and the tips that follow you will get,
Start with a simple bubble chart. The figure below shows a simple Excel bubble chart. To create one like this, just enter the data as shown, select a cell within the data, then on the Home tab, in the Charts group, and select a bubble chart. It’s just a basic bubble chart.
You can also create a FQMM with dynamic quadrants that change size relative to some value such as the average. To learn how to create dynamic quadrants, read next week’s article, How to Create a Dynamic Four Quadrant – Matrix Model with an Excel Chart.
You will need to modify your bubble chart to turn it into a Four Quadrant – Matrix Model. It needs a static graphics background that shows the four quadrants (in different colors if you want). You will need to scale the data so the maximum is 100% for all data sets. In every FQMM I’ve used the most important concern was relative position and size of the data points so scaling all the data to a 100 point scale will keep relative positions of bubbles consistent between different charts as well as relative to the static four quadrant background. Since there are always questions about the original underlying data you can show the original data in a table or use a trick I’ll show you later to show the data as a multi-line label next to each bubble.
To scale the data let’s copy the data and headings down to C14:E21. You can leave the original data where it is so the bubble chart remains displayed. We’ll replace the chart data in C5:E12 with formulas that scale the data.
To create a scaling formula use a MAX function to find the maximum values of X and of Y data and then scale the data that is charted. The MAX formulas are,
B15 Max X =MAX($C$15:$C$21)
B16 Max Y =MAX($D$15:$D$21)
To create scaling formulas for the data range referenced by the chart area enter these formula,
Then copy them down through row 12.
The next step is to improve the appearance of the bubble chart. Remove the background grid lines by clicking on them and pressing delete. Set the scale min and max for the X and Y axis to 0 and 120 by right-clicking on the axis numbers, choose Format Axis, and set the Minimum to 0 and Maximum to 100.
One last step is to change the title at the top of the chart into a custom title based on a cell content in the worksheet. To do that,
1. Click the title in the chart to select it.
2. Type = in the formula bar and click the cell containing text you want as a title. In this case, I clicked $I$2.
The chart title will display the cell contents. This is a nice trick when you want to make the text in chart elements or drawing objects show a cell contents. You can make dynamic titles this way to reference changing contents, dates, etc.,
Your bubble chart should now look like this. But, it needs a few more modifications to insert the four quadrant background, scale the bubbles, and add multi-line labels.
Use the following steps to turn your bubble chart into a Four Quadrant-Matrix Model.
1. Insert a four color four-quadrant background.
Download a worksheet to help you create colored four quadrants backgrounds.
Your chart will look like this with the four quadrant picture,
2. Scale the size of the bubbles.
You chart will look like this with scaled bubbles,
3. Add labels to the bubbles.
4. Change the labels to multiple lines next to the bubbles. This takes a trick.