How to Make Boston BCG Matrix in Excel

by Best Excel Tutorial Charts

In this tutorial, you will learn how to create a BCG Matrix in Excel. The BCG Matix is a chart that shows the potential of your company’s products. BCG stands for Boston Consulting Group; this company created this chart for the first time.

Preparing Data to BCG Matrix

First, you need some data. You need market growth, market share for all of your products and relative market share.

To get relative market share, just divide the market share of your product by the market share of your largest competitor.

BCG Matrix Data Table

Inserting a BCG Matrix

To insert the BCG Matrix into Excel, select columns A, B and C for all products. Market Growth will be measured using the values of Y axis. Market Share will be the bubble in the chart.

BCG Matrix Data

Go to Ribbon > Insert > Other Charts and click Bubble Chart.

Bubble Chart

This is what the basic bubble chart looks like.

Excel Bubble Chart

For further formatting, change the X-axis values. Right-click X-axis values and choose Select Data.

Bubble Chart Select Data

Click Edit to change the source of data.

Bubble Chart Edit Source Data

You have to change the series X values.

Bubble Chart Edit Series

Select Relative Market Share values. It is column E in this example.

Bubble Chart Relative Market Share

Your BCG Matrix chart is a bit changed. Now, all values are correct.

Bubble Chart Not Ready

Let’s continue the formatting of the X Axis. Right-click the X-axis and go to Format Axis. Click Values in reverse Order and change Axis Value to 1 (it depends on your market!).

Bubble Chart Format Axis

Your bubble chart starts to look similar to the BCG Matrix.

BCG Matrix beta

Click the chart. Go to the Ribbon > Layout tab. Click:

  • Gridlines > Primary Horizontal Gridlines > None
  • Legend > None
  • Add both Axis Titles
See also  Yes No Chart with Non-numeric Data

Right-click the Y-axis and go to Format Axis. Change Axis value to 0,1 (also depends on yor market!).

BCG Matrix Axis Value

The BCG Matrix is almost ready but needs some more formatting.

BCG Matrix almost ready

Do some formatting:

  • Go to Ribbon > Insert > Text Box and insert box in the chart to create some labels
  • Change titles of chart and axes

BCG Matrix is ready.

Example of BCG Matrix in Excel

This is a portfolio of your products:

  • Product3 is your star. You will earn money on this product.
  • Product2 is a cash cow. This is product which is giving you money now.
  • Product4 is a qustion mark. You should invest money in this product.
  • Product1 and Product5 are dogs. Just don’t sell them any more.

I prepared a template of the BCG Matrix. You can download it here for free.

Related posts:

Interactive Charts data tableHow to Create Interactive Charts in Excel horizontal line data set tableHow to Add Multiple Trendlines in Excel Max Chart data tableMin/Average/Max column Chart