How to Create a Static Four Quadrant – Matrix Model in an Excel Chart

Charts

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.

Static Four Quadrant Matrices keep equal sized quadrants and are used by many models.

Static Four Quadrant Matrices keep equal sized quadrants and are used by many models.

Download the Four Quadrant Matrix Model Dynamic Example

Download the Four Quadrant Matrix Model Static Example

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,

  • What are the organization’s Strengths, Weaknesses, Opportunities, and Threats (SWOT) for this new venture?
  • What are my personal Strengths, Weaknesses, Opportunities, and Threats (SWOT) for moving into this new job?
  • Which project will give us the greatest impact in the least amount of time/resources?
  • How do our product lines compare to the competition?
  • Should we develop this product line or “milk” it for cash?
  • Which marketing campaigns should we repeat and leverage and which should we develop and which should we cancel?
  • What is our team work style?
  • What leadership style should I use?

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.

Scatter Chart or Bubble Chart?

Four Quadrant – Matrix Models need more than just a bubble chart or scatter chart. They also need,

  • A four quadrant background that might include graphics or text
  • Scaling of X and Y values so they match the graphic background
  • Vertical and horizontal mid-point lines
  • Multi-line labels on scatter chart or bubbles to show data such as project names, costs and dates
  • Scaling for the size of bubbles based on a third parameter, such as forecast revenue

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,

  • Smooth mid-point lines that reach to the edges of the plot area
  • Multi-colored quadrants to indicate risk or reward
  • Formulas that are easy to explain to others
  • Bubbles (data points) that can change size to reflect a third parameter

Building a Simple Excel Bubble Chart as a Static Four Quadrant – Matrix Model

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.

Start with a simple bubble chart.

Start with a simple 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.

Scaling Your Data for a Four Quadrant – Matrix Model

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.

Start with a simple bubble chart.

Scale the data so you can compare charts and keep the static quadrants centered.

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,

C6 =100*C15/$B$15

D6 =100*D15/$B$16

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.,

Creating Dynamic Excel Chart Titles that Link to Worksheet Cells

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.

Start with a simple bubble chart.

Scale the data so you can compare charts and keep the static quadrants centered.

Use the following steps to turn your bubble chart into a Four Quadrant-Matrix Model.

1. Insert a four color four-quadrant background.

Learn how to insert a background picture into the chart’s Plot Area.

Download a worksheet to help you create colored four quadrants backgrounds.

Your chart will look like this with the four quadrant picture,

Insert a picture behind the bubble chart to create a static four-quadrant background.

Insert a picture behind the bubble chart to create a static four-quadrant background.

2. Scale the size of the bubbles.

Learn how to scale the size of bubbles in a bubble chart.

You chart will look like this with scaled bubbles,

Scale bubble size to make the chart more readable.

Scale bubble size to make the chart more readable.

3. Add labels to the bubbles.

Learn how to add labels to bubbles in an Excel bubble chart.

4. Change the labels to multiple lines next to the bubbles. This takes a trick.

Learn how to add labels with multiple lines.

Static Four Quadrant Matrices keep equal sized quadrants and are used by many models.

Static Four Quadrant Matrices keep equal sized quadrants and are used by many models.

 

Share the power...