Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a graph showing percentages with a Data Table alongside. Excel 2010 rounds the values so that columns can sum to more than 100%, but I want every column in the graph to total exactly 100%.

Furthermore, some values like 0.0001 show 0, and I'd like to display something like 0* in the table so it will be clear that this is a value between 0 and 1.

share|improve this question
    
Have you tried implementing banker's rounding? –  soandos Oct 25 '12 at 3:55
    
See the comment I gave on Carl's answer. –  AYBABTU Oct 25 '12 at 8:01
add comment

2 Answers

You could define a separate column with this formula:

=IF(A1>100%,100%,IF(A1<=0.1%,"0*",A1))

(where A1 is the cell where the actual percent result is posted on your worksheet). Your graph would have to reference this column’s results. This will indicate that anything above 100% will be noted as 100%, and anything above zero, but less than 0.1%, will show as 0*; but if it is really zero, it will show 0. You may have to format the original columns for percent with two decimal places.

share|improve this answer
    
I have no problem with the actual result in the columns. The problem is in the way they are presented in the graph's data table - it is only rounded there. –  AYBABTU Oct 25 '12 at 8:01
add comment

Data tables in charts are rather inflexible, since you have almost no control over formatting, nor over what to include or exclude from the table. What I've always done is make a custom table in the cells adjacent to the chart with whatever values and formats I need.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.