## Graph Showing Percentage And Actual Values |

## 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

Many thanks

Nilam

## Free Excel Help Forum

**- Ask any question about Excel and have it answered in no time.**

## Similar Excel Tutorials

Calculate Percent Change in Excel

How to calculate the percentage amount that a value has changed in Excel. This includes quickly calculating this v ...

How to calculate the percentage amount that a value has changed in Excel. This includes quickly calculating this v ...

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

How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ...

Change Minutes from a Decimal to a Percentage of an Hour

Change minutes stored as a decimal into a fraction of an hour. For instance, if time is stored as 8.15, 8 hours and ...

Change minutes stored as a decimal into a fraction of an hour. For instance, if time is stored as 8.15, 8 hours and ...

Display all Formulas at Once in Excel

How to view all of the formulas at once in Excel so that you can troubleshoot the spreadsheet quickly and easily. S ...

How to view all of the formulas at once in Excel so that you can troubleshoot the spreadsheet quickly and easily. S ...

## 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

- 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

- 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

- 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

- 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

- 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

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

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?

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.

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

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

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

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

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 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

I want the chart to display the actual numbers, and not a percentage, how can I do this?

Thanks

Hello all.

This is also posted he http://www.excelforum.com/excel-misc...ata-label.html

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

This is also posted he http://www.excelforum.com/excel-misc...ata-label.html

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 have a column on a spreadsheet where the cells are formatted to display percentages - related to floor space used for different purposes in a building.

This allows the user to enter a value, eg. 25%, 10% etc. However, there are instances where the value entered is less than 1%, which is where my problem lies.

Because I need to be able to total the percentages I need to prevent the user from entering things like <1%. I have achieved this using the Data Validation Custom Formula, =ISNUMBER(Cell Ref) and coupled this with a warning, "Please enter an actual percentage".

But how can I allow the user to enter values such as 0.5%, 0.1% etc. If the user enters these values, the cell displays 50% and 10% respectively, whereas I need the cell to actually display 0.5% or 0.1%.

Many thanks

This allows the user to enter a value, eg. 25%, 10% etc. However, there are instances where the value entered is less than 1%, which is where my problem lies.

Because I need to be able to total the percentages I need to prevent the user from entering things like <1%. I have achieved this using the Data Validation Custom Formula, =ISNUMBER(Cell Ref) and coupled this with a warning, "Please enter an actual percentage".

But how can I allow the user to enter values such as 0.5%, 0.1% etc. If the user enters these values, the cell displays 50% and 10% respectively, whereas I need the cell to actually display 0.5% or 0.1%.

Many thanks

I am wanting to calculate forecast accuracy compared to sales where I have one column with actual sales figures and two other columns with forecasts. What I need to do is display the accuracy of each forecast against actual sales in terms of a percentage.

Simply showing the percentage difference is not good enough (can be anywhere from -200% to +200% as our sales guys are rubbish at forecasting), I need to show the accuracy as a figure from 0% to 100%.

I've been mucking around with this for over three hours and not getting anywhere.

Can anyone provide some assistance?

Simply showing the percentage difference is not good enough (can be anywhere from -200% to +200% as our sales guys are rubbish at forecasting), I need to show the accuracy as a figure from 0% to 100%.

I've been mucking around with this for over three hours and not getting anywhere.

Can anyone provide some assistance?

I have a simple bar chart showing budgeted spend vs. actual spend by month within a year. So, two bars per month. I would like to show actual spend as a percentage of budgeted spend for each actual bar. How can I do that?

i have seen variations of this question on but i am curious about my particular graph.

i have a bar graph which graphs percentage values. Some are positive and some are negative. Because some of the data is negative, the data labels are "encroaching" on to the graph (even at maximum offset, i.e. 1000).

I need them not to.

I know you can adjust the "Category X Crosses Axis at" value to move the data labels off the chart graphic, but this changes the view of the negative percentages. Previously, the negative values "grew" downward from 0%, where as adjusting the axis makes them grow upward from say -.15%.

I need the negatives to grow downward AND have the data labels off the chart.

Any thoughts???

Thanks for looking,

Greg

i have a bar graph which graphs percentage values. Some are positive and some are negative. Because some of the data is negative, the data labels are "encroaching" on to the graph (even at maximum offset, i.e. 1000).

I need them not to.

I know you can adjust the "Category X Crosses Axis at" value to move the data labels off the chart graphic, but this changes the view of the negative percentages. Previously, the negative values "grew" downward from 0%, where as adjusting the axis makes them grow upward from say -.15%.

I need the negatives to grow downward AND have the data labels off the chart.

Any thoughts???

Thanks for looking,

Greg

This may be less of an Excel question than a general data display question, but I'll try here.

I have 3 variables that I'm charting. I have one on the primary axis and it's values are in the billions. I have one on the secondary axis and it's a percentage. I need to display a 3rd who's value is in the millions. If I put it on the primary axis, you cannot see modest changes.

Is there a tricky way to get all three on one graph? I know I could split on two graphs, so that's my backup plan.

Thanks,

Dave

I have 3 variables that I'm charting. I have one on the primary axis and it's values are in the billions. I have one on the secondary axis and it's a percentage. I need to display a 3rd who's value is in the millions. If I put it on the primary axis, you cannot see modest changes.

Is there a tricky way to get all three on one graph? I know I could split on two graphs, so that's my backup plan.

Thanks,

Dave

I have a chart that has over 80 data series. The user can toggle which are displayed via checkboxes (unchecked hides the column of selected data.) The data comes in two varieties, standard decimal or percentage. I have a secondary axis for the percentage data which functions as expect. The problem arises when the user selects only percentage data series to display. The X axis and primary Y axis disappears and the plot area turns white. Is there a way to force the primary Y axis and X axis to display even if there isn’t a series plotted against it?

Hi, I'm wondering if this is even possible, I tried searching the forum to see if anyone asked about this already.

I'm trying to create a chart in Excel that basically has a y-axis that is a reflection around the x-axis, with the y-axis representing percentages. On the positive y-axis, I wish to have a column, cone or box that basically shows a percentage value (e.g.60%) and along the negative y-axis I wish to show the complimentary percentage value (i.e., in this case, 40%). The x-axis should show four groups of three columns/cones/boxes each.

Does this sound feasible, what other information should I provide? I'm still have problems with providing excel data on the forum, but I can send my excel file to anyone willing to help, it's pretty small.

Thanks so much.

Joe.

I'm trying to create a chart in Excel that basically has a y-axis that is a reflection around the x-axis, with the y-axis representing percentages. On the positive y-axis, I wish to have a column, cone or box that basically shows a percentage value (e.g.60%) and along the negative y-axis I wish to show the complimentary percentage value (i.e., in this case, 40%). The x-axis should show four groups of three columns/cones/boxes each.

Does this sound feasible, what other information should I provide? I'm still have problems with providing excel data on the forum, but I can send my excel file to anyone willing to help, it's pretty small.

Thanks so much.

Joe.

I am preparing a line graph showing 2007 versus 2006. In my worksheet I have also calcuated the percentage difference. I'd like to show the percentage difference, but not graph it. Is there a way to show this percentage in the chart?

Thanks.

Data

2007 2006 %

JAN 1,189 1,193 0%

FEB 1,249 954 31%

MAR 1,135 1,278 -11%

APR 1,046 1,257 -17%

MAY 748 1,188 -37%

JUN 1,058 1,343 -21%

JUL 849 1,170 -27%

AUG 1,126

SEP 1,346

OCT 1,309

NOV 1,377

DEC 1,432

Thanks.

Data

2007 2006 %

JAN 1,189 1,193 0%

FEB 1,249 954 31%

MAR 1,135 1,278 -11%

APR 1,046 1,257 -17%

MAY 748 1,188 -37%

JUN 1,058 1,343 -21%

JUL 849 1,170 -27%

AUG 1,126

SEP 1,346

OCT 1,309

NOV 1,377

DEC 1,432

I have data from a questionnaire that incorporated a Likert scale which I would like to chart but am having difficulties incorporating everything I want in one chart, i.e. i would like the number of responses indicated along with these response figures as a percentage that ive already calculated using formula in excel under the likert scale of strongly disagree, disagree, undecided, agree and strongly agree which would all be indicated in different coloured bars. How can i get all the three pieces of data i have on my spreadsheet into a graph. I can either get the responses and likert scale as the legend or the percentages corresponding to the likert scale as the legend.

Also what graph would be best to display my data.

Furthermore i would it be possible to separate this data once all displayed in a graph according to the respondents age to the number of responses for each scale along with the percentage.

I would appreciate any help.

Gareth

I have attached my raw data so i hope someone can help me with a solution.

Also what graph would be best to display my data.

Furthermore i would it be possible to separate this data once all displayed in a graph according to the respondents age to the number of responses for each scale along with the percentage.

I would appreciate any help.

Gareth

I have attached my raw data so i hope someone can help me with a solution.

I'm trying to make my spreadsheet display over 100% while calculating a long column of entries. Each entry is showing percentage cost per hour of a benchmark of $65.00 per hour. When an entry for example is $51.10 the percentage displays 0.79%, but if the entry is for example $73.89 then the displayed value is 1.14%.

How do I make this display the percentage over the benchmark of 65 as 110, 115 or whatever it calculates out?

Thanks

How do I make this display the percentage over the benchmark of 65 as 110, 115 or whatever it calculates out?

Thanks

Hello people:

Not sure why but Excel 2010 is displaying a column with percentage values with decimal values on a Pivot Chart. Example It shows 0.7823232 instead of 78% as shown on main Excel Column.

Already tried formatting Pivot Table's Row labels to percentages and refreshed data but no success!

Would appreciate any help!

Thanks!

Hi There -- looking to create a column chart that would display both the absolute value and the percentage value in a chart. I'm not having any luck -- any suggestions?

Other$ Other % AAM$ AAM%

9/1/2008 3,914,723 46% 4,597,953 54%

9/1/2007 2,184,835 36% 3,966,829 64%

9/1/2006 2,029,604 37% 3,476,841 63%

9/1/2005 2,210,974 38% 3,581,566 62%

9/1/2004 770,035 18% 3,595,985 82%

Other$ Other % AAM$ AAM%

9/1/2008 3,914,723 46% 4,597,953 54%

9/1/2007 2,184,835 36% 3,966,829 64%

9/1/2006 2,029,604 37% 3,476,841 63%

9/1/2005 2,210,974 38% 3,581,566 62%

9/1/2004 770,035 18% 3,595,985 82%

This one landed in my inbox this morning... I've been asked to see if I can fix a spreadsheet graph. The spreadsheet contains about 20 columns of data. Each column represents a series in an embedded line graph. The values in each series are either units or percentages. Unit series are plotted on the primary y-axis, while percentages are plotted on the secondary. The category labels (x-axis) correspond to 336 30-minute intervals which cover one week.

Each of the data series can be turned on and off with a checkbox. The problem that we are having is that when only series plotted on the secondary y-axis are enabled, the x-axis labels default to integer values, instead of the linked date/time values. If at least one data series on the primary y-axis is enabled, then the chart displays as expected.

My suspicion is that Excel 07 links the x-axis labels through the primary y-axis data series, so if no primary y-axis data series is available to the chart, the x-axis defaults to integer values.

The purpose of this document is to provide a weekly performance analysis report to a variety of individuals within our company, so certain aspects of the audience may get hung up on what could be perceived as a "broken" report.

I'm trying to determine if there is any way possible to force excel to use the linked category labels in the x-axis, regardless of which y-axis is enabled/disabled. One thought was to plot a series of zero values on the primary axis and format the series so it is invisible on the chart, but my boss is asking for a more elegant solution. Any thoughts?

Each of the data series can be turned on and off with a checkbox. The problem that we are having is that when only series plotted on the secondary y-axis are enabled, the x-axis labels default to integer values, instead of the linked date/time values. If at least one data series on the primary y-axis is enabled, then the chart displays as expected.

My suspicion is that Excel 07 links the x-axis labels through the primary y-axis data series, so if no primary y-axis data series is available to the chart, the x-axis defaults to integer values.

The purpose of this document is to provide a weekly performance analysis report to a variety of individuals within our company, so certain aspects of the audience may get hung up on what could be perceived as a "broken" report.

I'm trying to determine if there is any way possible to force excel to use the linked category labels in the x-axis, regardless of which y-axis is enabled/disabled. One thought was to plot a series of zero values on the primary axis and format the series so it is invisible on the chart, but my boss is asking for a more elegant solution. Any thoughts?

Hello,

I have a pivot table which I have created a calculated field called WMAPE which stands for Weighted Mean Absolute Precent Error. The formula looks like this.

=IF(AND(SUM(Actual)=0,SUM(Forecast)=0),0,IF( SUM(Actual)=0,100,'Abs Error'/Actual))

The first IF is checking to see if a forecast and actual existed, if both values = 0, then WMAPE = 0 since there is no error.

The second if is going to check to make sure if Actual equal 0 than WMAPE is 100 because the user forecasted a value, but nothing shipped.

Else, if neither condition exist, WMAPE is Abs Error / Actual.

It turns out that some of my raw data for Actual contains negative numbers which is causing my WMAPE to return a negative number which is not correct.

I would like to edit the calculated firled to add another IF statement to fix my negative problem. However, I cannot find out how to do that?

Any suggestions?

I have a pivot table which I have created a calculated field called WMAPE which stands for Weighted Mean Absolute Precent Error. The formula looks like this.

=IF(AND(SUM(Actual)=0,SUM(Forecast)=0),0,IF( SUM(Actual)=0,100,'Abs Error'/Actual))

The first IF is checking to see if a forecast and actual existed, if both values = 0, then WMAPE = 0 since there is no error.

The second if is going to check to make sure if Actual equal 0 than WMAPE is 100 because the user forecasted a value, but nothing shipped.

Else, if neither condition exist, WMAPE is Abs Error / Actual.

It turns out that some of my raw data for Actual contains negative numbers which is causing my WMAPE to return a negative number which is not correct.

I would like to edit the calculated firled to add another IF statement to fix my negative problem. However, I cannot find out how to do that?

Any suggestions?

Im looking to create a line graph to show three sets of data each on a seperate line.

The first two data types are numbers and the third is a percentage.

How can I get the data to show correctly on one chart? Im not interested in the values of each point im only interested on how the line changes.

Heres what I currently have but the percentage line sits at the bottom. Is my only option to have a seperate chart for the percentage?

The first two data types are numbers and the third is a percentage.

How can I get the data to show correctly on one chart? Im not interested in the values of each point im only interested on how the line changes.

Heres what I currently have but the percentage line sits at the bottom. Is my only option to have a seperate chart for the percentage?

Good morning all,

I've seen this problem posted before on MrExcel.com, but none of the threads have a working answer yet.

The problem is that the values in my graph lose their decimal precision when the spreadsheet holding the source values in not actively open.

I have two spreadsheets ("A" and "B") and spreadsheet A has a chart whose source data in found in spreadsheet B. (The graph is just a line graph, with the actual plotted values displayed at the bottom of the graph in a chart). The source values in spreadsheet B look as follows:

-6% -8% -1% 6% 18% 18% 23% 18%

(point being, no decimal precision displayed. The true values do have decimal precision).

When both spreadsheets are open, the chart in spreadsheet A displays the values exactly as they appear in spreadsheet B. The problem is that if I simply close spreadsheet B and then view the values in the chart, they have all reverted to showing the full decimal precision that is stored in the cell from spreadsheet B.

Does anyone know how to fix this? I am using MS Excel 2007.

Thanks so much!!!

Mark

I've seen this problem posted before on MrExcel.com, but none of the threads have a working answer yet.

The problem is that the values in my graph lose their decimal precision when the spreadsheet holding the source values in not actively open.

I have two spreadsheets ("A" and "B") and spreadsheet A has a chart whose source data in found in spreadsheet B. (The graph is just a line graph, with the actual plotted values displayed at the bottom of the graph in a chart). The source values in spreadsheet B look as follows:

-6% -8% -1% 6% 18% 18% 23% 18%

(point being, no decimal precision displayed. The true values do have decimal precision).

When both spreadsheets are open, the chart in spreadsheet A displays the values exactly as they appear in spreadsheet B. The problem is that if I simply close spreadsheet B and then view the values in the chart, they have all reverted to showing the full decimal precision that is stored in the cell from spreadsheet B.

Does anyone know how to fix this? I am using MS Excel 2007.

Thanks so much!!!

Mark

Hi,

I created a Vertical bar chart with percentage on the right or as my secondary axis. I have to datasets referencing the secondary axis.

For whatever reasons, I cannot fine one of these datasets, which is suppose to be a straight line going across the chart & representing the goal - in this case 90% to reach.

How to find & unhide this dataset?

Thanks

I created a Vertical bar chart with percentage on the right or as my secondary axis. I have to datasets referencing the secondary axis.

For whatever reasons, I cannot fine one of these datasets, which is suppose to be a straight line going across the chart & representing the goal - in this case 90% to reach.

How to find & unhide this dataset?

Thanks