# Another tough graphing question

#### earlyd

##### Well-known Member
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?

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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

##### Well-known Member
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

##### MrExcel MVP
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.

earlyd said:
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

##### Well-known Member
Thanks!

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,091
Messages
5,857,303
Members
431,870
Latest member
Muratculous

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back