How to show values in data labels of Excel Pareto Chart when chart is based on percentages

dendres

New Member
Joined
Aug 1, 2015
Messages
14
Hello,

I am developing a Pareto Chart for my client in Excel 2010. They currently have a chart that shows the percentage of each category on the primary Y-axis. Then, the secondary Y-axis is used for the cumulative percentage, up to 100%. So far, this is a normal and easy to create Pareto. I should also mention it is based on a Pivot Chart with 3 different Report Filters.

They wish to show data labels above each column to indicate the number of occurrences. So for example, they may have 6 events on the x-axis:


1 - Event A, 50%, 1,000 occurrences
2 - Event B, 30%, 600
3 - Event C, 10%, 200
4 - Event D, 5%, 100
5 - Event E, 3%, 60
6 - Event F, 2%, 40


What I cannot figure out is how to show the data labels so they show the value of each category (e.g. Event B would show 30% on the left axis, have a data label of 600 and the Cumulative total line using the secondary (or right) axis would be at 80% at this point). Please keep in mind that depending on what is selected with the Report Filters on the Pivot Chart, the name/number of categories on the x-axis will change, so I don't think adding a formula into the data label is the answer either.


I've looked everywhere (I think) for an answer, but cannot figure it out. I'd prefer to avoid a VBA solution, but I do know how to write VBA code, so if that's the only way, a nudge in the right direction would be greatly appreciated.


Thank you.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Steve=True

Well-known Member
Joined
May 27, 2011
Messages
993
Hi Dendres,

With your set up like this:
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">%</td><td style=";">Value</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Event A</td><td style="text-align: right;;">50%</td><td style="text-align: right;;">1,000</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Event B</td><td style="text-align: right;;">30%</td><td style="text-align: right;;">600</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Event C</td><td style="text-align: right;;">10%</td><td style="text-align: right;;">200</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Event D</td><td style="text-align: right;;">5%</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Event E</td><td style="text-align: right;;">3%</td><td style="text-align: right;;">60</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Event F</td><td style="text-align: right;;">2%</td><td style="text-align: right;;">40</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

You can do this with the following steps:
1) Create Clustered Column chart on range A1:C7
2) Move Value data series to 2nd Axis
3) Change Value data series Fill from Automatic to No Fill
4) Change 2nd Vertical Axis Labels to None
5) Add Data Labels to Value data series

Hope this helps.

Steve=True
 

dendres

New Member
Joined
Aug 1, 2015
Messages
14
Hi Steve=True,

Thank you for the help. Unfortunately this isn't what I'm looking for. I need to keep my secondary axis visible since this is a Pareto Chart. The secondary axis needs to show the cumulative percentage of the values in column B (per your demonstration). Your example basically also needs to show a Column D of the cumulative percentages. Column D then gets plotted as a line along with the clustered columns to create the Pareto.

Sorry if my question wasn't clear on that.

Thank you,
dendres
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,962
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
If both the bars and the line plot percentages, then you don't need a secondary axis.

I've made the chart using the first worksheet column for category labels, the second for the bars (percentages), and the third for the line cumulative percentages). I added data labels to the bars, using Excel 2013's option to use label text from cells, referencing the text in the fourth worksheet column. If you don't have Excel 2013, you can use Rob Bovey's Chart Labeler, a free addin from Welcome to Application Professionals.

Pareto1.png


Very often, Paretos plot the bars using values on the primary axis, and cumulative percentages on the secondary axis. I think that's a little misleading if the scales are not synchronized, but whatever.

I made this chart using the fourth worksheet column for the bars, on the primary axis, and put the cumulative percentages on the secondary axis. I also synched the axes, using 2000 for the primary axis full scale, which neatly corresponds to 100% full scale on the secondary axis. In real life this doesn't work so nicely very often. The labels are easy here, because they show the default Y values.

Pareto2.png
 

dendres

New Member
Joined
Aug 1, 2015
Messages
14

ADVERTISEMENT

Hi Jon,

Thank you for your reply. Unfortunately I have some pretty strict guidelines that I must follow for my client. I'm doubtful they will allow use of an add-in, but I can follow up with them regarding it. And we must also stick with Excel 2010 for development. I do agree that having percentages on both sides of the chart are a little awkward, as again, those are the guidelines I need to stick to. Perhaps though there is something I can glean from both examples to help out with an alternate suggestion to what they currently are trying to accomplish, assuming Excel just can't do it their original way.

If anyone else does have the full answer to my original question though, I'd be very excited to see it.

Thanks again for your help Jon.

dendres
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,962
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Clients can be a pain.

The add-in is only needed to add the labels, not to view them later, so if you're making the graphics for the client, it might work out.

There are other ways to do the data labels. See my tutorial Apply Custom Data Labels to Charted Points.
 

dendres

New Member
Joined
Aug 1, 2015
Messages
14
Jon,

Thank you for that link. The VBA code you have on that page (Option #2) is helping me come to a solution for this problem. I appreciate you taking the time to help me out!

dendres
 

Watch MrExcel Video

Forum statistics

Threads
1,129,472
Messages
5,636,515
Members
416,920
Latest member
Riskyplan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top