3. Showing Market Share
Using the revenue figures from the previous lesson, I construct a chart that tracks market share over time. You might need basic knowledge of Excel for this lesson.
How to show market share
- Market share can be shown effectively with a pie-chart or 100% stacked column.
- If you are showing market share over time, 100% stacked column is a better option
- It's always best to convert absolute numbers into % terms for the audience
Switching from absolute numbers to percentages
1 Right-click on the chart and select 'Edit data'
2 Create a sum formula to find the total market size for each year
3 Divide each company's revenue by the total annual revenue
4 Copy the new numbers and paste special the values into the top lefthand corner
5 Convert numbers to percentages by pressing the % button in the ribbon
Note: Market share does not provide information on the overall market size. Always be aware of what information your chart is leaving out.
In charting competitors, we used the clustered column chart to compare the revenues of three companies.
But what if I wanted to show market share rather than the absolute revenue numbers? If we go to Insert, Chart, we can find two clear options for displaying market share.
The first is the 100% Stacked Column Chart and the next is the Pie Chart.
Pie charts won’t work well for this particular task because I will have to build four of them, one for each year, and it may be difficult for the audience to track market share progression across four separate charts.
For this reason, I’m going to go with the 100% Stacked Column Chart.
Instead of creating a new chart, let’s start by duplicating the old slide by right clicking and Duplicate.
Next, we’ll select the chart and if we right click, we have the option to Change Chart Type.
So let’s click on that and let’s select the 100% Stacked Column Chart.
This gives us exactly the chart we want, but unfortunately with the wrong data labels.
Ideally, we would like percentages.
We’d also like to change the legend so that it doesn’t overlap with the columns.
Let’s quickly fix the legend.
I’ll select it, I’ll extend it and then I’ll adjust the height.
To convert absolute numbers to percentages, will require some Excel calculations which we can perform in our data sheet.
Let’s open this by right clicking on the chart and selecting Edit Data.
To calculate market share percentage, we need to divide each company’s revenue by the total market revenue.
So let’s quickly calculate the total market revenue for each year, and we’ll do that by creating a SUM formula and we’ll select these three cells for 2008.
Pressing Return completes that formula for each of the years.
We don’t want the total included in our chart, so let’s move the blue line outside the total.
Let’s remove this text and perform our calculations underneath our existing numbers.
So the first thing we’ll need to do for Mason 2008 is to divide revenue by the total, and I’ll need to repeat that for each year and all three companies.
So let’s hold the Shift button to select these cells and then when we’re ready Ctrl + V to paste the formula.
And it looks like we have a problem for both Goodspeed and Hummel, where we have a Divide by Zero error.
If we look at the formula for this cell, we can see that the total revenue corresponds to the cell F2 and not E2.
To fix this, let’s go back to our original cell and let’s put a dollar sign in front of the E.
And then we can copy with Ctrl + C and paste again.
Next, we need to move our new data into where the old data lies so that it appears on our chart.
Let’s first copy with Ctrl + C, then click the cell in the top left hand corner where you wish to paste, click on the drop down, select Paste Special and let’s select Values, because we don’t want to paste the formulas, just the values. Click OK and now we have the right set of data in the right position to show up on our chart.
Let’s adjust the format slightly by creating a percentage clicking on this for percent style, and now we can return to our chart.
Our chart is almost complete.
Let’s quickly change the color of the bottom label so that it’s easy to read.
I’ll change it to white.
We’ll need to change the action title and the title of the chart.
And I’m also going to change the unit to percent.
I’m also going to make sure that the legend is roughly in line with the title of the chart.
Let’s zoom in and let’s adjust the position of the legend.
That’s much better. Let’s go back to Fit to Window.
And now our chart is pretty much complete.
If I put it in slideshow view, you can see that it clearly articulates the fall in market share for Mason and the corresponding growth in market share for Goodspeed.
Although it’s great for showing changes in market share, the 100% stacked column chart does have some obvious drawbacks.
It doesn’t show you a whether market, as a whole, is growing or shrinking.
For example, if a market crash happened in 2009 affecting all companies, there would be no way of telling from this particular chart.
When you do select a chart to convey a message in a PowerPoint slide, you should always be aware of the chart’s limitations as well as its strengths.
In PowerPoint, selecting the right chart is often more of a challenge than actually creating it.
This example also highlights the fact that creating charts in PowerPoint does require some basic Excel skills, such as creating formulas and using Paste Special.
If you struggle with the Excel part of today's lesson I'd encourage you to check out my course called "Beginning Excel" to help you get the most out of your PowerPoint charts.