How to Combine Bubble and Column Excel Charts – Creating a Dynamic Four Quadrant – Matrix Model

A static Four Quadrant – Matrix Model is fine if you’re working with something like the Steven Covey – Eisenhower time-management matrix or a SWOT (strengths, weaknesses, opportunities, and threats) matrix that just contain lists and there is no difference between item weightings. But, what if you are a consultant working with a four quadrant – matrix model where the quadrants change size?

Dynamic Four Quadrant Matrices use quadrants that change size to reflect a changing mid-point such as an average of the data points. They are valuable in seeing relationships.

If you want to combine a bubble or scatter chart with a dynamic Four Quadrant – Matrix you are out of luck in Excel, unless you know a few tricks. Excel documentation says that bubble and scatter charts cannot be combined with other charts. But there are ways to work around it.

By combining these two charts you can solve problems such as,

• Deciding which projects take priority by charting projects by cost and time and seeing which are in quadrants above or below the line.
• Deciding between which actions to take on weighted levels of Strength, Weakness, Opportunities, and Threats in SWOT charts.
• Deciding which marketing campaigns are in the Hero quadrant, which are in an improvable quadrant, and which should be killed.
• Deciding between which products should have more development funds and which should be used as cash cows.

These are important decisions where relative weightings and position make a difference. You can make those decisions easier by showing your data in an Excel bubble chart with a dynamic Four Quadrant – Matrix background.

These charts require a Four Quadrant – Matrix Excel chart that has a bubble or scatter chart on top and a dynamic (changing with data) four quadrant background. The size and relationships of the four quadrants change to reflect the average or median value of the data. For example, you want to compare all projects by their Return on Investment versus time to completion. Which are above or below average?

How to Trick Excel into Creating Combo Charts with Bubble Charts and any Second Chart

One of Excel’s most powerful charting features is creating combo charts that combine two different types of charts. For example, combining column and line charts.

The problem is that bubble and scatter charts cannot be combined with other charts. But, we can use a trick to make it look like the two are combined.

What you will learn in the following steps will allow you to combine any two charts together. In fact, you can even combine three charts together.

Here’s the big picture of how to do this,

1. Create a stacked column chart of four rows of data so the chart looks like the chart below. Use 1s as data to show a column and 0s to hide a column.

2. Change the gap between the columns to 0 so all the columns together look like four different colored columns.

3. Remove the axis legends, color the quadrants, and expand the Plot Area to fill the chart background.

4. Replace the static 1s and 0s with formulas that display 1s for above average and 0s for below average.

5. Link the calculated average from the bubble chart data to the average used to show or hide columns.

6. On the bubble chart change the Plot Area and Chart Area to no background so they are transparent.

7. Drag the bubble chart over the dynamic Four Quadrant Chart and align the axis.

8. Select both charts and group them.

Now they will update, move, and size like they are a single chart. Because the calculated average that controls the four quadrants comes from the bubble chart you can change, expand, or contract bubble chart data and the four quadrant chart moves stays aligned with the bubble chart.

ALERT! In testing this with Excel 2010 and Excel 2013 I’ve learned that the multiple line labels used in the Static and Dynamic Four Quadrant – Matrix Model sometimes disappear when moving worksheets between Excel versions. (This seems to be a bug between the two Excels.) If you find that happens you can recreate the links to the multiple line labels or create two different sets of charts, one for Excel 2007/2010 and one for Excel 2013.

Creating a Simplified Dynamic Four Quadrant – Matrix Chart Using an Excel Column Chart

The idea behind showing and hiding columns is a little mind boggling so let’s begin with a simple example that uses static data and is only 20 columns wide. Twenty columns could cause up to a 5% difference between the actual data in the bubble chart and the Four Quadrant Matrix. The actual model later in this article is 100 columns wide so there is a maximum of 1% difference.

This simple example with twenty columns looks like this before shrinking the gap between columns to zero.

To create this sample,

1. Enter 1s and 0s as shown in rows 18 through 21. Row 17 is the horizontal axis 1 through 20. These are static numbers, not formulas.

2. Select a cell inside the data, then on the Insert tab, from the Chart group, select Stacked Column. The chart should appear like the figure.

3. Right-click a column to select the data series and choose Format Data Series, then set the Gap Width to No Gap.

Your stacked column chart should now look like this,

Closing the gap between columns makes columns appear to be four quadrants.

Notice that the data switches between 1s and 0s at 7 on the horizontal axis. The stacked column switches at the same location. Change the 1s and 0s in the worksheet to see how the matrix changes.

At this point you can see how all we need are more columns to give us finer detail and a formula that makes the 1s and 0s switch at the Average or Median of our bubble chart data.

Creating a Full Scale Dynamic Four Quadrant – Matrix Model using an Excel Column Chart

The full scale dynamic Four Quadrant – Matrix chart uses the same layout as the simple example you just built. However, the full scale version uses 100 data points and uses a formula to decide when to switch between 1s and 0s. Using four series of one hundred data points and a formula to switch between 1s and 0s turns this stacked column chart into a dynamic Four Quadrant – Matrix.

What you are about to create is a Four Quadrant – Matrix that that will align with and can be used behind any chart. When the Average of data used by the bubble chart changes then the Four-Quadrant – Matrix created in this worksheet also changes making the two charts move as one.

The tab containing this matrix chart can be hidden. Then the matrix can be copied and moved behind any chart in your workbook.

The actual matrix uses four rows of data that are 100 cells wide.

To create this matrix chart, which you will use to combine with any other Excel chart, begin by entering manual data and creating a static four quadrant chart,

1. In cells C17:CX17 enter 1 to 100 as the horizontal axis.

2. Enter labels in B18:B21. These will later be removed so they are just for your reference during creation.

3. Enter 1s across rows 18 and 20 up to column BA, 50 on the horizontal axis. Fill the rest of rows 18 and 20 with 0s.

4. Enter 0s across rows 19 and 21 up to column BB, 51 on the horizontal axis. Fill the rest of rows 19 and 21 with 1s.

5. Create a stacked column chart by selecting a cell in the data, on the Insert tab, in the Charts group, select a Stacked Column chart.

You should now have a larger version of the static Four Quadrant Matrix created earlier.

Format your chart to make the columns move together and look like a Four Quadrant – Matrix,

1. Right-click a column to select the data series, chose Format Data Series, and change the Gap Width to 0.

2. Right-click a column in each quadrant, choose Format Data Series, and select Fill to color the quadrant with the color you want.

To make the columns change dynamically,

1. Enter the following to manually define where the quadrants will change. Later the fixed numbers in D6 and H6 will be linked to the calculated averages of the bubble chart data,

C4           Vertical Scale

C5           Max – Avg

D5           =100-D6

C6           Avg

D6           65

G4          Horizontal Scale

G5          Max – Avg

H5           =100-H6

G6          Avg

H6           70

2. Shade cells D6 and H6 green so it’s easy to see the cells that control quadrant size.

3. Enter the following formulas to create 1s and 0s that switch at the average point along each row of data,

C18         =IF(C17<=\$H\$6,\$D\$6,0)

C19         =IF(C17<=\$H\$6,0,\$D\$6)

C20         =IF(C17<=\$H\$6,\$D\$5,0)

C21         =IF(C17<=\$H\$6,0,\$D\$5)

4. Copy the range C18:C21 across the rows to the right to fill all cells under the horizontal axis.

Test your dynamic Four Quadrant – Matrix by changing the values in D6 and H6. The quadrants should resize at the average point on the horizontal and vertical axis.

Format your Four Quadrant – Matrix by adding border lines, deleting the legend and title, and scaling the axis to Max Y = 100 and Max X = 100. Delete the axis labels.

This dynamic Four Quadrant – Matrix chart will go behind the bubble chart. To make it easier to align it with the bubble chart axis you should expand the Plot Area as large as possible. To do that,

1. Click in the chart.

2. In Chart Tools, on the Layout tab, look to the far left to see the element selector, a drop-down menu that enables you to select hard to click parts of a chart. Select Plot Area.

3. Drag the corners of the Plot Area out to fill as much of the chart area as possible.

The matrix is now ready to use as a dynamic background behind any bubble chart or scatter chart. All you need to do is link the average calculations between the charts and move the Four Quadrant – Matrix chart behind the bubble chart.

Combining the Dynamic Four Quadrant – Matrix Chart and Excel Bubble Chart

In the following figure the matrix chart has been copied from its original worksheet onto the bubble chart worksheet. The bubble chart can then be slid over the dynamic Four Quadrant – Matrix chart so they appear as a single chart. The data for the two charts are linked together by linking the Avg X and Avg Y for the bubble data back to the matrix cells D6 and H6.

In the picture below the charts have not yet been aligned over each other. You can see how the bubble chart has a no fill background for the Plot Area and Chart Area.

Slide the bubble chart with no fill over the top of the matrix chart.

Align and group the two charts so they act as a single chart,

1. Fix the horizontal and vertical axis of the bubble chart to a maximum of 100. This keeps the axis aligned and proportional between the two charts.

2. Link the averages from the bubble chart data in cells H9 and H10 on the bubble chart sheet back to the averages on the matrix sheet in cells D6 and H6. This way when the bubble chart data changes, the matrix changes.

3. Position the matrix chart by dragging and sizing it while holding the Alt key. Holding down the Alt key while moving charts or graphic objects makes their corners align with cell corners.

4. Right-click the bubble chart and format the Chart Area and Plot Area to No Fill. Use the chart element selector to the far left on the Chart Tools, Layout tab to select each area, then right-click and set the Fill option to No Fill. This enables the matrix to show through behind the bubble chart.

5. Drag and size the bubble chart so it aligns with the Four Quadrant – Matrix chart.

6. Select both charts and group them so they stay together.

Tips on Selecting, Sizing, and Grouping Multiple Charts

Here are tips that will help you select, size, and group the two charts,

• If it’s hard to select a chart element, click in the chart, then in the Chart Tools, on the Layout tab, look to the far left. The chart element drop-down list enables you to select chart elements.
• If you need to select a chart, but the one on top is in the way, right-click the top chart and select Send to Back. Use Send to Back or Bring to Front to move between layers.
• If it is hard to select both charts because one covers the other, select them both by pressing F5, the Goto key, click Special, select Objects and click Ok. All objects on the worksheet are selected.

This takes a lot of work so you may want to download the finished Excel model.