Search

# Analyze Competition with Scatter Plots – A business chart example

Share

Analyzing competition is one of the key aspects of running a business. In this article, learn how to use Excel’s scatter plots to understand competition.

How Mobile Handset Market Changed from 2007 to 2010?

[view large]

Recently, Kaiser at Junk Charts pointed to a very effective business chart that shows the dynamics of competitive land scape with ease.

The chart shown aside originally appeared in Asymco, shows how mobile handset market has changed between 2007 & 2010.

I like this type of chart because it clearly tells the story of what happened in mobile handset market between 2007 and 2010. It shows how then leader, Nokia, kept loosing profit share despite a tiny loss in market share. It shows how new entrants like Apple have eroded the profit share for others. [related: good charts tell stories]

The chart instantly lets me ask important questions like, “So what did Nokia do wrong?”, “How come RIM is also in the same league as Apple (ie both market share and profit share went up) ?” etc. and explore for answers.

And that is what a chart should do. It should present a story and poke our curiosity to ask questions (or address problems).

### How to construct similar chart in Excel?

Here is how you can construct similar business chart in excel.

### Step 1: Get your data

In case of analytical charts like this, getting correct data is all important. For the sake of example, we will use the same variables – Market Share & Profit Share for 4 fictitious products – A,B,C & D. The data is shown below:

### Step 2: Re-arrange the data, so we get first and last values

This is very simple. Use cell references to extract the data for just first and last periods. Now make starting values as zero and calculate ending values. Something like this:

### Step 3: Make a scatter plot

Select the data and make a scatter plot. When you are done, it should look something like this:

### Step 4: Format the chart

Do the following to format the chart:

• Add lines to scatter plot so that starting and end point are connected
• Set arrow symbol as the end-point style for these lines (new feature in Excel 2007 and above)
• Remove grid lines and legend
• Add data labels to either starting or end points alone.
• Add axis labels, position them accordingly
• Make axis and labels subtle.
• Add a descriptive chart title

And you are done. The chart should look something like this:

### Bonus Step: Making a scatter plot of absolute values

As you can see, the above chart only shows changes in market share and profit share of products between Q1-2008 and Q2-2010. But a more descriptive option would be to show absolute position of each product at both times.

Like this:

To make this chart, all the steps are same as above, just change your data to starting and end points, not the calculated ones.

I have prepared a simple excel chart template to help you create similar charts on your own. Click below links to download.

You can see the chart construction steps in the downloaded workbook.

As I mentioned I really liked how this chart lays out the dynamics of market place without complicating or animating anything. I think it is both simple and elegant [related: keep your charts simple]

What do you think? Please share your opinion and ideas thru comments. Also, tell us how you would have plotted same data?

### More Excel Charts for Analysis:

Excel charts are powerful visual tools for analysis and exploration. We have posted several useful chart templates & ideas on chandoo.org. Please visit these pages for more resources on charting & analytics.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### Automatically Format Numbers in Thousands, Millions, Billions in Excel [2 Techniques]

Ever wanted to automatically format values in thousands, millions or billions in Excel? In this article, let me show you two powerful techniques to do just that.

## Related Tips

Charts and Graphs

### Automatically Format Numbers in Thousands, Millions, Billions in Excel [2 Techniques]

Charts and Graphs

### Make an Impressive Interactive Map Chart in Excel

Charts and Graphs

### How to Create a Dynamic Excel Dashboard in Just 5 Steps

Charts and Graphs

### How to create a fully interactive Project Dashboard with Excel – Tutorial

Charts and Graphs

### 9 Box grid for talent mapping – HR for Excel – Template & Explanation

Charts and Graphs

### 9 Responses to “Analyze Competition with Scatter Plots – A business chart example”

1. Gregory says:

I like the first chart because the four quadrants are clearly evident, but for this data set I like your second chart even more because you get to see the starting points.

2. Michael Pierce says:

For me, the second chart is more useful. The first only tells me movement that each product has made, but there is no context for that movement. In the second chart, I get to see the same movement but also the context (i.e. the relative position of each. For example, in the first chart Product D looks to be the absolute worst. In the second chart, however, I can see that Product D is falling, but is still the dominant product.

Sorry for the remedial question, it must be a typical Monday for me and everything is going wrong. Scatter charts are always the worst for me. When I select the data ranges and create the chart, nothing even remotely resembles the unformatted scatter chart you have in Step 3. What am I doing wrong?

3. Chandoo says:

@Gregory & Michael... I agree. Even I like the 2nd chart better.

4. DTH says:

I've downloaded the file and repeat the instruction steps myself. It took me some time to realize that you didn't add trendlines to have the arrows. You wrote "add line" instead.

I had a problem with the direction of the arrows if I add trendlines to the data series, C & D for instance (I did set arrow for end style of trendline).

Could you help to explain why?

5. arun kumar chaurasia says:

dear sir,

first time i show this types of clock in excel . this is fantastic i like it very very nice.

once again very nice.

6. Joe says:

On the relative chart the labels for B & C are reversed. They are correct on the absolute chart. Also they are correct on the preliminary relative charts. Perhaps it's coffee time, but can't seem to fix this in the downloaded file. For example, what I check "Format data labels", not of the boxes are checked. Very strange...

I already have great appreciation for the site, but help with this would also be appreciated!

Joe

7. Joe says:

Figured it out! (In Excel 2010 anyway). When you set the data label on the end point, it defaults to the value. If you right click the data label, none of the check boxes are on (Series Name, X, Y). If you activate Series Name, then it is on both begin and end points....

However if you **double click** the data label, then the "Y" value is activated. Change it to Series Name and all set.

A truly arcane bug in Excel!

8. TS says:

simple and comprehensive

9. Milesh Patel says:

Nice information. Thanks for sharing the information.

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.