Having used this type of chart a lot recently, I thought it was time I had a decent version in Excel. In case you are not aware a BCG matrix, also known as a growth-share matrix is a management planning tool. It is used to portray a company’s / SBU’s product portfolio on a quadrant showing relative market share (horizontal axis) and speed of market growth (vertical axis). It basically shows you the potential of your company’s products.

A BCG matrix is basically a bubble chart with a few tweaks. When complete, it should look something like this:

You can download my sample chart here.

Step 1: Set up your data

To be able to plot a BCG matrix successfully, you need the following data: market growth,market share, market share of the largest competitor, and relative market share. You can calculate the last by dividing the market share / market share of the largest competitor for example:

=C2/D2

A sample data table might look like this:

Step 2: Create a bubble chart

Highlight the first three columns of your table, in my case cells A1:C6. Then click on Insert > Other Chart > Bubble Chart.

Your bubble chart will look something like this:

You can then delete the title, legend and horizontal gridlines by clicking on them and pressing the delete key.

Step 3: Convert the chart to a BCG matrix

Right click on the x axis and then choose “Select Data”. In the pop up menu that appears, click on the “Edit” button.

You have to change the series X values. Highlight the Relative Market Share values. In this example we need to change it from column A to column E.

It should say:

='BCG Matrix'!$E$2:$E$6

Your BCG Matrix chart will update. Now, all of the values should be correct.

Right click on the X axis and then choose format axis.

Select “Values in reverse order”. Then change where the “Vertical axis crosses” to have an Axis value of 2. This value will change depend upon your data.

Without closing the formatting pop up menu box, click on the Y axis. You can then adjust the minimum and maximum values to “0” and “4”. The Axis value this time should be 0.2. Again, this value will change dependent on the data added.

You can also change the number format using the Number tab to “Number” with 2 decimal places.

You can then press the close button. Your chart should now look like this:

Step 4: Format the BCG Matrix

So in my example, I created some custom pictures to use as fills for the different bubbles: a star, a cow, a dog and a question mark.

The bubble in the left upper quadrant is the star. Once you’ve created your star picture, click on it and press Ctrl+C. Then, click on the bubble twice and press Ctrl+V. The bubble should then update with the picture. repeat the process for the other bubbles. The left lower quadrant is for the cow picture. The right upper quadrant contains the question mark bubbles, the right lower quadrant contains the dog bubbles.

The final touch is to add data labels. Ensure all of the bubbles are selected (click off the chart and then click once on the series). Then click on Chart Tools > Layout > Data labels > Right. The chart will show the values of the chart. To change the data labels so that they show the product names, click on one of the data labels twice (slowly). Then click inside the function test entry field and type the cell reference that shows the name of the product.

Step 6: Add the coloured background

Click on Insert > Shapes > Rectangle and draw a rectangle over the top of one of the quadrants. Format the rectangle as you wish. Repeat until you have a different colour for each quadrant. Select all four, by clicking on them while the shift key is held down. Then right click and press “Group”. Then with the group still selected click on Drawing Tools > Format > Send Backward > Send to Back.

Your chart should now look like this:

And that’s it! You can download the sample worksheet here.

+Alesandra Blakeston