How to Create BCG Matrix in Excel - Practical Guide

Learn to create a BCG Matrix in Excel to analyze your product portfolio based on market growth and relative market share. This strategic tool helps businesses identify which products are stars, cash cows, question marks, or dogs, enabling data-driven decisions on resource allocation and product strategy.

Why This Matters

Strategic portfolio analysis is essential for prioritizing investments and maximizing profitability. The BCG Matrix provides a visual framework that executives and teams use to justify product decisions.

Prerequisites

•Basic Excel knowledge (data entry, formulas)
•Understanding of market share and growth rate concepts
•Product or business unit data (revenue, market size, growth rates)

Step-by-Step Instructions

Prepare your data

Create a table with columns: Product Name, Market Share (%), Market Growth Rate (%), and Revenue. Enter your product data in rows, calculating relative market share as (your market share / competitor's market share) × 100.

Create a scatter chart

Select your data (Product Name, Market Share %, Market Growth Rate %). Go to Insert > Charts > Scatter > Scatter Only. This creates the foundation for your BCG matrix.

Add quadrant dividing lines

Right-click the chart and select Edit Data. Add two reference lines: one at X=1.0 (market share axis) and one at Y=0% (growth rate axis). These divide the matrix into four quadrants. Use Insert > Shapes > Lines to draw these manually if needed.

Format and label quadrants

Add text boxes in each quadrant (Insert > Text Box): top-right = 'Stars', top-left = 'Question Marks', bottom-right = 'Cash Cows', bottom-left = 'Dogs'. Color-code each quadrant (Insert > Shapes > Rectangle) with transparency for clarity.

Finalize and interpret

Label axes as 'Relative Market Share' (X-axis) and 'Market Growth Rate' (Y-axis). Review positions: invest in Stars and selective Question Marks, milk Cash Cows, and divest or reposition Dogs. Add a legend showing product names and sizes.

Alternative Methods

Use Bubble Chart for revenue sizing

Replace Scatter chart with Bubble Chart (Insert > Charts > Bubble) and use Revenue as the bubble size to visualize financial impact alongside market positioning.

Use third-party templates

Download pre-built BCG Matrix templates from Microsoft Office or business sites; simply input your data and the matrix auto-generates with formulas.

Manual shape-based approach

Create quadrants using shapes (Insert > Shapes > Rectangle), then manually position product labels as circles—useful for small portfolios without complex calculations.

Tips & Tricks

✓Use consistent data units (percentages for growth and market share) to ensure accurate chart positioning.
✓Add bubble sizes proportional to revenue to give stakeholders immediate financial context on product importance.
✓Update your BCG Matrix annually or quarterly to track portfolio shifts and strategic progress.
✓Color-code products by category (e.g., region or division) for multi-dimensional insights.

Pro Tips

★Set the X-axis midpoint at 1.0 (not 0) to properly represent relative market share where 1.0 = equal to largest competitor.
★Use conditional formatting with data bars to highlight high-growth products in your source table, syncing visual emphasis across sheets.
★Create a summary dashboard linking chart selections to profit/loss statements to justify strategic recommendations to executives.

Troubleshooting

Products not positioning correctly on the chart

Verify your relative market share calculation (your % ÷ competitor %). Check axis scaling: X-axis should range 0–2.0 and Y-axis –10% to +20%.

Quadrant lines misaligned with data distribution

Use the actual median of your market growth rate (not 0%) and median relative market share (not 1.0) as dividers for balanced quadrants.

Labels overlapping on crowded charts

Reduce font size, use leader lines (Insert > Shapes > Connectors), or create a separate legend table linked to product codes instead of on-chart labels.

Related Excel Formulas

INDEX/MATCH IFERROR AVERAGE (for median alternatives)

Frequently Asked Questions

What does each quadrant represent?

Stars (high growth, high share): invest heavily. Cash Cows (low growth, high share): milk profits. Question Marks (high growth, low share): selective investment. Dogs (low growth, low share): divest or reposition.

How do I calculate relative market share?

Divide your product's market share by your largest competitor's market share. If you have 20% and the leader has 15%, your relative share = 20÷15 = 1.33.

Should I use actual or forecast growth rates?

Use a blend: historical growth (2–3 years average) for stability and next-year forecast for forward strategy. This balances trend reliability with future positioning.

Can I include more than 4 products?

Yes, the BCG Matrix scales to any number of products using bubble or scatter charts. Bubble size can represent revenue or unit sales for additional dimensionality.

How often should I update the matrix?

Update quarterly for fast-moving markets or annually for stable industries. Link to your actual revenue and market share dashboards for real-time accuracy.