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.
 

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,728
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.



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.

 

dendres

New Member
Joined
Aug 1, 2015
Messages
14
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
 

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
 

Forum statistics

Threads
1,081,862
Messages
5,361,742
Members
400,653
Latest member
ProParadox

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top