I have a graph that is pulling data from a PivotTable, so I get a distribution of counts (how many in category A, how many in B, how many in C, etc)
But above each bar I also want to show a percentage of the whole. So for example I might be able to say "This bar for Category A shows we clearly have 2000 of it, but see the number on top of the bar, it's only 15% of the total"
Does this make sense?
This question is related to
excel
charts
excel-formula
Either
Method 2 by step
In Excel for Mac 2016 at least,if you place the labels in any spot on the graph and are looking to move them anywhere else (in this case above the bars), select:
Chart Design->Add Chart Element->Data Labels -> More Data Label Options
then you can grab each individual label and pull it where you would like it.
You can do this with a pivot table and add a line with the pourcentage for each category like brettdj showed in his answer. But if you want to keep your data as it is, there is a solution by using some javascript.
Javascript is a powerful language offering a lot of useful data visualization libraries like plotly.js.
Here is a working code I have written for you:
https://www.funfun.io/1/#/edit/5a58c6368dfd67466879ed27
In this example, I use a Json file to get the data from the embedded spreadsheet, so I can use it in my javascript code and create a bar chart.
I calculate the percentage by adding the values of all the category present in the table and using this formula (you can see it in the script.js file):
Percentage (%) = 100 x partial value / total value
It automatically calculates the total and pourcentage even if you add more categories.
I used plotly.js to create my chart, it has a good documentation and lots of examples for beginners, this code gets all the option you want to use:
var trace1 = {
x: xValue,
y: data,
type: 'bar',
text: yValue,
textposition: 'auto',
hoverinfo: 'none',
marker: {
color: 'yellow',
opacity: 0.6,
line: {
color: 'yellow',
width: 1.5
}
}
};
It is rather self explanatory, the text
is where you put the percentage.
Once you've made your chart you can load it in excel by passing the URL in the Funfun add-in. Here is how it looks like with my example:
I know it is an old post but I hope it helps people with the same problem !
Disclosure : I’m a developer of funfun
Source: Stackoverflow.com