Another tough graphing question

[earlyd]

Is there a way, without a macro, to have data labels on a column chart not appear when the value is zero or null or some other non-numeric value but appear when there is a value?

 

[PaddyD]

if I've understood what you want - one way would be to set uyp a dummy data series with formulas that returned na() for those conditions you wnated to exclude & chart from the dummy series (to take advantage of the fact that excel doesn't plot na()'s) another option would be to fitler the data not to display them - default is to plot only visable cells

 

[earlyd]

Thanks for your response...The N/A was avoiding the year from being displayed but if I have multiple bars (2 data items) for the same year and only one had data, then both labels were displaying and the N/A data label was coming up zero...I may have done something else wrong in generating the N/A though... anyway, wound up writing a worksheet change macro to delete data labels where values were zero or N/A...tomorrow I'll try your suggestion and see if I wasted time with the macro... Thanks again.

 

[tusharm]

Paddy had the right idea but for the wrong element. Create a dummy series that contains the desired labels. So, if the y values are in column B starting with row 2 and you want to show the y-values in the label, in C2 (assuming C is empty), enter the formula =IF(B2=0,"",B2). Copy C2 as far down col. C as required.

Now, use either Rob Bovey's XY Chartlabeler (www.appspro.com) or John Walkenbach's ChartTools (www.j-walk.com) to use this extra column (C in the above example) for the data labels.

No VBA required on your part. The labels will show only for non-zero values and will self-update as needed.

Thanks for your response...The N/A was avoiding the year from being displayed but if I have multiple bars (2 data items) for the same year and only one had data, then both labels were displaying and the N/A data label was coming up zero...I may have done something else wrong in generating the N/A though... anyway, wound up writing a worksheet change macro to delete data labels where values were zero or N/A...tomorrow I'll try your suggestion and see if I wasted time with the macro... Thanks again.

 

[earlyd]

Thanks!