Graph Showing Percentage And Actual Values


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Graph Showing Percentage And Actual Values  Excel

View Answers


I have created a clustered bar chart with percentages on the Y axis. I would like to display the actual values (that this percentage was calculated from) next to each individual bar, how to do this?
Many thanks
Nilam
Similar Excel Video Tutorials
Decrease Values By 35%
 See Mr Excel and excelisfun demonstrate two methods to subtract 35% from a column of values:
Mr Excel: Create formula for remaining percentage (1 ...
Helpful Excel Macros
Get Values from a Chart
 This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Similar Topics
Hi,
I am attempting to display a chart as percentages, and include a data table on the bottom of the chart, however i want to show the data table as the actual values that the percentages represent, rather than the percentages which come through by default.
Is there a way to do this? I have attached a worksheet (2007) as an example.
Thanks
Hey everyone,
I'm really hoping someone can help me with this...
I need to plot percentages over time in a line graph in excel. I don't want to have to do a percentage equation in the spreadsheet, I just want excel to take two sets of values and display the percentage in the chart. For example, I need B1 as a percentage of B2 for week 1, C1 as a percentage of C2 for week 2, etc....
Can someone please offer a suggestion for how to do this? I would really appreciate it.
Also, would it be possible to link data from other sheets in the workbook into one single chart?
Hi,
I have succesfully built a spreadsheet that changes ranges and data required in a graph. I am stuck at two points.
Firstly, one of the selection options to appear in the graph, is for percentage or actual values. Is there a way that the chart axis can automatically detect this and switch between percentage values and "normal values"?
Secondly, though not associated directly, I would like a pivot table filter to automatically change depending on the value entered into a different cell. I guess this would need VBA, any help please!
Thanks
I want to do a simple chart  the Y axis will be percent (from 0 to 100) and the X axis will be year (02, 03, 04). I want to use a line chart with markers displayed at each data value  the data values will be 34 (or 25%) for 02, 131 (or 96%) for 03, and 137 (or 100%) for 04. I can get it to plot the percentages nicely, but I can't figure out how to get it to put the actual quantities as data labels. Any help you could provide would be appreciated.
hello there,
I am doing bar graphs like the one I am attaching
the graph axis needs to be % and show the % sign, this is achieved
however the values for each bar, while being % values, should not display the % sign  I asked that in another thread, but help will be welcome as well.
then the second and more tricky question
I have do display the actual values (not the percentage ones) somehow in the graph for each of the vars
anybody can help we with that?
I've heard there is some trick with an invsible secondary axis  can somebody point out an straight forward process? And I need to do like a tone of times, hopefully is not too troublesome
thanks a lot in advance!
I'd like to have the xaxis values as displayed on the chart be different than the actual values plotted on the graph. Specifically, my actual xaxis values are date/time values so they're huge mother labels like "October 12, 2009 13:52:27". I absolutely don't want these as my xaxis increment values. Instead I'd like to have the xaxis increment values enumerated as 1, 2, 3... values, while actually plotting the true xvalues on the chart itself. Is there a way to do this in Excel 2007. Thanks  AJ.
There is probably a really easy solution to this, but I just can't figure it out!!!
I performed an investigation, which was to return a certain percentage of data (e.g. 1%, 2%, 5%, 10%, 25%, 50%, 75% or 100%) and time how long it took for the data to be displayed to the screen. I performed the investigation on each percentage twice, so I now have two sets of results for each percentage.
I would like to display these results in a line chart, but Excel won't let me arrange the line chart how I wish. I would like the time taken to be in the X axis and the percentage to be in the Y axis.
No matter how many different ways I try, I cannot get this to happen. I have tried drawing my table of results with the percentages along the top and then down the side, I've tried adding in extra series and then taking them out after the graph has been drawn, but nothing works.
If anyone could advise, I would be so grateful.
Many thanks.
I have a straightforward line chart with values from say 80 to 101 on the y axis.
However I want the real 90.5 axis value to display as 100, and then BOTH the real 89 & 92 values (i.e. 90.5 plus and minus 1.5) to display as 90.
Similarly I want both the 86 & 95 axis values (i.e. 90.5 plus and minus 4.5) to display as 75.
...etc
In summary although the actual series line plots are plotted normally, I want the y axis to display something different. i.e. reading upwards the axis display will be 60, 75, 90, 100, 90, 75, 60
Has anyone got any ideas?
Usual TIA
Hi folks.
I'm currently working on a spreadsheet to give a target update for various partners I work with.
I need to work out a percentage value for a quater, which is dependant on the input of a percentage of retail target figure. So for instance I have:
Jan  125% of retail target
Feb  60%
March  100%
I need to show a quater value, which I have come to the conclusion is:
(1(((1B8)+(1B9)+(1B10))/3)
Which just so happens to be the average value
Which works out as 95%
But if these percentage figures are derived from a actual/target like:
Jan  4 target, 5 actual
Feb  5 target, 3 actual
March  6 target, 6 actual
Then my "true" percentage actual works out at 15 target, 14 actual which is 93.34%
Is there a better way to work out the quartile percentage?
I currently have a set of data the is shown in a column chart. At the moment, the 'y' axis has the scale set to the values and each bar is shown as the value itself.
Is there anyway of showing the figure on the bar as a percentage of the total for that bit, whilst keeing the 'y' axis as the values?
Hope this makes sense??
Please note this is for a column chart, not bar chart
I have created a simple bar chart (with percentage as it yaxis) but I have a one value that has a much larger percentage than the other (xaxis) values. As all other values are shown to the same scale, it is making all slight differences between the other values look completely insignificant. Is there a way we could alter the scale on the yaxis, so that it goes 0% to 30% with a gap to 200% to 250%?
What I have: I inherited a chart and table that I am required to use. The chart is supposed to display table values and percentage. As the table and chart is now, values have to be manually entered into the table then the chart with each new use. Table data isn't linked to the chart. I tried to figure out how the percentages are calculated without success.
What I need: I would like to enter Occupied hours in the table and display those hours and the unoccupied hours in the chart with the percentage each value is of 168 hours.
I have created a dynamic chart with the option of viewign the data as percentage data or the actual numerical (non percentage) data.
The slect button I created selects the rows to chart either percenatges of actual values as a line chart.
The problem is I have 4 charts in a quadrant style and the maximum in the y scale varies for each as it is automatic.
I want all charts to show max of 100% in the scale to make them relavive when viewing but if I set the max to 1.0 then when I hit the button and the chart changes to regular values eg 01500 then a max of 1.0 shows no data obviously
any thoughts ?
I am doing absolutely masses of simple sums in Excel (X for Mac) to then produce charts for a report  but I am having to illustrate the actual
figures as whole percentages and that is getting me hot under the collar  as my little lists rarely add up to an exact 100%. Now, I can
understand where the discrepancy is creeping in with the rounding and all  but is there a sneaky way to get Excel to make the adjustment
for me so that the percentages always total 100%?
Here is an example from my sheet, where the actual figures are added to equal 200, and the cell showing the percentage figure has a simple
formula at present of for example "=(B71/B76)" where B71 is the actual figure (in this case, 12) and B76 is the sum of all the actual figures
(200 below) and the cell is then set to be a percentage with zero decimal places.
12
6%
105
53%
44
22%
31
16%
8
4%
200
So you can see that my percentages here total 101%!
I have spent hours trying to research a sneaky way and am starting to think I'm asking for the impossible  and will just have to 'adjust' the
percentages manually to make my 90 charts look accurate!
Many thanks as always, here's hoping.
Hi,
I'm trying to create a pie chart showing a percentage breakdown based upon a set of values that includes repeating values.
For example, for the list:
1
1
1
1
2
2
3
4
I'd like a pie chart that shows the pentage breakdown of the four unique values, not represent each line as it's own slice.
I'm not sure how to do this in Excel without manually calculating the percentages based. Any suggestions woul dbe much appreciated.
Hi,
I'm creating a chart that shows a distribution of values, comparing them month by month (see attached spreadsheet). In addition to showing the actual values on the chart, I'd like to show the percentage for that month. For example, in my spreadsheet, in month '1', there are 11 values. 4 of them are labeled as '14.3'. I'd like to show the 14.3 column as being 4 high, but also, I'd like to show that it's 36% (4 divided by 11) of the total numbers gathered for that month. Is there a way to do this?
Thanks,
Mike
I am trying to find a formula to create my chart column, I have managed to see the total column and the actual column, but the percentage column remains at `0`, I am not sure if I have the formual correct.
1, 2, 3, 4
Total 275 1050 1140 310
Actual 120 1000 1050 250
% 44% 95% 92% 81%
The percentage is divided from actual into total which gives 44% (rounded up)
The actual and total coulumn appear correct, but the % column is showing `o`
Can you advise thank you
Blackwell
Hi again everyone,
I have a graph that has 5 variables in a stacked column. X axis is year, Y axis is total number of people. On the same graph, I'd like to label each individual series with its % of the total for that year. I've done this before by making individual text boxes and manually entering in the percentages, but I feel like there must be a better way to do this. I tried a linecolumn graph and plotted the percentages on a second Y axis (percentage) but the data points don't line up with their corresponding bar chunk. I hope I'm making sense, I've b een staring at this for quite awhile. Thanks!!
Hi! I have a graph which displays the movement of a parameter in percentages.
On the yaxis are the percentages displayed, e.g. 5,0%
I want to show these numbers on this axis without the '%'sign, so e.g. 5,0
Which custom format should/can I use? I'm using Excel 2007.
I want to create a chart with 2 values
Eg below:
name marks percentage
Total no. 76 100%
Pass 54 71%
Fail 22 29%
No result 41 54%
I need to create a chart where I want to show both the marks and percentage on Clustered Column 3D chart.
When I try to create the chart I get only the marks bars and the percentage bars are just flat shown on the chart. Pls helt
Thanks for your answer
I am trying to create a chart that has the primary axis as a stacked chart that shows 2 figures (numbers of sales & number of referrals). I am then adding a line that shows percentage across the chart in a secondary axis. The issue is that I need this line to show the percentage of sales that were referrals. I have the chart created, however due to the referrals changing each month the line is not accurately showing the percentage. The percentage number is correct; it is the spot where the line intersects that is not calculating properly on the stacked bars. Any thoughts?
I have a pie chart and I dont want to display percentages, but the whole number value from the source. When I format the data label I get a fraction instead of the whole number.
I want the chart to display the actual numbers, and not a percentage, how can I do this?
Thanks
Hello all.
I have a pie chart that simply displays total number of items and number of remaining items. I have the percentages manually calculated but wanted the pie chart to also display the percentages. The problem is that when I select Format Data Series, Data Labels, Percentage...the percent displayed on the chart is incorrect. For example, I set up a test chart with 100 total items with 50 items remaining. The calculated percentage is 50% but the pie chart is displaying 33.33% which is obviously incorrect. Anyone know how to correct the pie chart percentages?
Thanks,
Andrew
i'm trying to create a simple line graph for percentages. Every five years from 1960 to 2000 i have a percentage( ranging from 5 to 30%). I want one axis to be the year and the other to be the percentage.
Next i'll need to show the same graph with a shockingly steep increase, probally by starting the percentages at zero.
I am able to get the line graph with the information but not with the corresponding years.
thanks for any help
So I can't seem a way to do this, but I've heard before that you can link data labels to other data than the Chart is showing. For instance, I am comparing actual data to forecasted data. I want the data label to show the variance between the two in percentage form.
Ex.
Forecast = 200
Actual = 210
Variance = 10% = (210  200) / 200
I have a 3D Clustered Column in which it shows the Actual and Forecast. The only remaining part is the Variance that I want above those two columns.
Does anybody know how to do this?