Excel. Help With Chart Not Showing Zero Values

[aedctalk]

Hello. I'm making a chart for values of L6:L40 which are all percentages.

The chart is a bar graph..

On C6:C40 i have movie titles. (the amount which vary by week.

ON MY CHART.. i always have at the bottom a bunch of bars that just show 0.00%

How can i get the bar graph to only recognize values either where..

values are greater than zero in the l column rows.. or value is not equal to "" null in the c column?

Is this possible?

 

[Akashwani]

Hi,

In 2007...Click on the label, right click, click Format Data Labels, click Number, click Custom. In the white box put this #;;; click Add, click Close.

This will prevent 0 values from being displayed.

I hope that's what you require.

Ak

 

[Jon von der Heyden]

Charts ignore #N/A values so one way might be to change you zero values to #N/A. If they are formula driven results then you could adapt the formula:

=IF(formula,formula,NA())

 

[aedctalk]

Thank you so much for the reply. That does reslove one thing that bugged me.. seeing the zero's

However my biggest nuisance still remains.

I have a chart that for values has L6:L40

34 spaces is required because somtimes all those are filled with data and sometimes they are not depending upon the week.

However for weeks where i have just 10 rows of data.. i still have 24 ticks below.. that really minimize the size of my bars and are just unessaray to still be shown on my chart.

Can i make it chart "ticks" (and take up chart space) just for whatever number of rows within L6:L40 that actually have values above 0 ?

Thanks so much Really really appreciate any insight

 

[Robert Mika]

Try to use dynamic range.
Here is an example

http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/

 

[rmccafferty]

I posted a question almost identical to yours a few weeks ago. The primary difference was that I was using pie charts.

My issue was the chart labels. A zero pie slice in the chart obvious did not appear since it had "zero size." But the chart still generated labels of 0%.

In my case, I did not want to show any slices with a percentage below 2%. So first I made an almost duplicate of the base data, using it for the source of the chart. And made all values less that 2% = zero (a simple IF statement).

Then, a couple smart people of this site told me to create a custom number format of %;;;

Then, in each chart I would format the data labels, using this custom format and the labels with zero amount just no longer appeared. I am not much on knowledge of custom format for numbers, but the semi-colons separate various aspects of the number.They include what to do for a positive number, a negative number, a zero number and whatever else. If you want that type of detail (I was personally nosy that way, but not enough to remember all the detail) it is easy to then look up on the web.

 

[aedctalk]

thank you everyone for your help!

I was able to resolve.

appreciate it!!

 

[AlisaA]

Charts ignore #N/A values so one way might be to change you zero values to #N/A. If they are formula driven results then you could adapt the formula:

=IF(formula,formula,NA())

I have found that this works well - not plotting the points if the value is #N/A. However, if you add data labels, the #N/As do show up in the chart even though there are no physical data points to go with them. Any ideas how to resolve that?

Thanks for the help!

 

[rmccafferty]

Personally, I got rid of any #NA values. I made my formulas end with zero if they did not fit other criteria. Then the custom formatting of numbers worked. Generally, I have been able to trap for whatever causes #NA results with an (maybe nested) IF statement.

 

[AlisaA]

Thanks for the advice! I'm giving this a shot & what I'm finding is that for column graphs, this is a good option, and for trend graphs, the NA is a good option, since with that I do want the zeros to show up. NA doesn't seem to show up in those labels, so I'm getting the best of both.

I appreciate the help!