Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Graph Showing Percentage And Actual Values

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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


View Answers     

Similar Excel Tutorials

Format Cells as a Percentage in Excel Number Formatting
This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number f ...
Error bars in Charts in Excel
How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ...
Filter Data in Excel to Show Only the Top X Percent of that Data Set - AutoFilter
This Excel macro filters a set of data in Excel and displays only the top X percent of that data. This means the h ...
Filter Data to Show the Bottom X Percent of the Data Set in Excel - AutoFilter
This free Excel macro filters data to display the bottom X percent of the data set in Excel. This is a great macro ...

Helpful Excel Macros

Format Cells as a Percentage in Excel Number Formatting
- This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number format
Filter Data in Excel to Show Only the Top X Percent of that Data Set - AutoFilter
- This Excel macro filters a set of data in Excel and displays only the top X percent of that data. This means the highes
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
Display The Actual Link / Email Address From Links in Excel - UDF
- Display the actual link or email address from links within Excel with this UDF. This user defined function pulls the ac
Filter Data to Show the Bottom X Percent of the Data Set in Excel - AutoFilter
- This free Excel macro filters data to display the bottom X percent of the data set in Excel. This is a great macro to u

Similar Topics


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.


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?

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 x-axis values as displayed on the chart be different than the actual values plotted on the graph. Specifically, my actual x-axis 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 x-axis increment values. Instead I'd like to have the x-axis increment values enumerated as 1, 2, 3... values, while actually plotting the true x-values on the chart itself. Is there a way to do this in Excel 2007. Thanks - AJ.

Hi I have been attempting to create a percentage change chart on a set of data. The data fluctuates up and down in the millions and billions and I am trying to create a graph that will display the percentage changes of the graph. When you start getting in the billions, any millions change becomes very small, and the percentage change chart fails to resemble the actual chart. I have included my best effort, and it is not good enough. I have inserted 2 arrows where the pink chart completely fails to trace the blue chart, it is higher then lower. Here is the link to download it:

Can anyone tell me how to make a more accurate percentage change chart that is an accurate overlay of actual data? TIA

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.

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-(((1-B8)+(1-B9)+(1-B10))/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 y-axis) but I have a one value that has a much larger percentage than the other (x-axis) 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 y-axis, 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 0-1500 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.


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:


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.

I have a Gantt graph I am making for a project. I am happy with it for the most part

It is a stacked graph obviously with a moving dateline and it all works great!

( The moving date line is not accurate as I am using bunk dates for testing, otherwise it DOES move correctly)

On the very top of the graph is a bar for Total project completion. The bar is simply showing a percentage. The percentage is an average of the tasks percentages on the Data tab.

Right not I have that bar as an embedded graph on top of the main graph and it looks good........on screen.

When I print it doesn't show that embedded graph of course.

What I am wondering is how can I get that one bar to have completely different axis and data then the rest of the graph??

I haven't found way to have dual axis on a stacked bar graph.


Hi All,

Quick one....

I have created a pie chart showing top 10 best selling categories. However, on this pie chart, I want %age of overall sales to be shown.

At the moment say I put in the following

Catalogue 1 100
Catalogue 2 98
Catalogue 3 92
Catalogue 4 85
Catalogue 5 84
Catalogue 6 75
Catalogue 7 74
Catalogue 8 66
Catalogue 9 64
Catalogue 10 62

I add Data Labels and select the percentages. This shows at Cat 1 having 12.5% of the sales (100 of 800 sales), Cat 2 having 12.25%, and so on..... However, what you don't see is the following data:

Catalogue 11 60
Catalogue 12 58
Catalogue 13 57
Catalogue 14 57
Catalogue 15 56

So based on 1,088 sales, Cat 1 at 100 is actually only 9.19% of TOTAL sales.

I understand what is happening, the chart is giving the percentage based on the data inputted into the chart.

I did consider adding an 11th category to the chart, which was the total of Catalogues 11-15. Whilst this does give a more accurate percentage figure (as now all sales have been inputted), it makes the chart look ****

Is there a way of either adding the 11th category, and then "hiding" it, so that the chart doesn't display it, but uses it's value to calculate Catalogues 1-10s percentage?


BTW: The reason I ask is that I could be dealing with anywhere from 30 to over 100 catalogues. So it's not ideal showing the others in 1 chart.

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 line-column 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!!

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


Hi! I have a graph which displays the movement of a parameter in percentages.

On the y-axis 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 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?

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?

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?


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.


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?

What I'm trying to find out is what is the percentage the actual value is between the min and max values.

Min Value (V15)
Max Value (W15)
Actual Value (X15)

Example - The minimum spring rate is 149.4 and the maximum spring rate is 1,120.2. I have my spring rate set to 805.0. What is the % within the min and max ranges that the actual value represents and how would you write that formula in excel