Remove Data Labels on a bar chart if value = 0

**Grimace** · 03-16-2010, 12:08 AM

Good afternoon everyone,

I have a chart that is reporting data that will be filled in gradually over a period of months, so many of the cells are currently = 0. That being the case, the 0 level of the chart appears very messy with labels sitting over other labels

What I would like to do is only display the data label if the value is greater than 0.
Is this possible? I have gone through the chart wizard and all tabs of formatting both the axis as well as the full chart, to no avail.

Would love your assistance ...

Thanks
Grimace

**teylyn** · 03-16-2010, 01:52 AM

Hi Grimace,

format your data labels with custom format

0;;;

this will suppress zero values.

**Grimace** · 03-16-2010, 02:11 AM

Thanks Teylyn ..... Spot on as always

**Grimace** · 03-17-2010, 08:24 PM

Hi Teylyn,

Was just working with another chart and have one further question on this same topic.

I am looking to use the same approach of not having data labels show when = 0, but want to have the label as the Series Name rather than the Value. This seems to then exlude the use of the 0;;; option you mention above (i tried it and it doesnt work).
Is there a way for me to use a custom format to remove the series name on value = 0?

**teylyn** · 03-17-2010, 09:02 PM

You can create another range of cells and fill them with the series label only if the value <> 0

=if(B1<>0,A1,"")

applied to your chart data, where B1 is the data value and A1 is the label.

Then use the XY Chart Labeler to assign the range with this formula as the labels for the chart. Download the Chart Labeler Add-in here http://www.appspro.com/Utilities/ChartLabeler.htm

Don't get thrown by the name, it creates labels for all charts, not just XY.

**Nelix** · 02-04-2011, 09:57 AM

Hi

I know this is an old thread but what is discussed and also resolved is exactly what I am looking for, however with reference to the solution below:

Originally Posted by teylyn

format your data labels with custom format

0;;;

How is this done?

I'm using Excel 2007.

Kind Regards

**Grimace** · 02-06-2011, 05:09 PM

Hi Nelix

Open your data labels formatting box by double clicking on one of the labels.
Under the number format, select custm and type in the 0;;; in the field provided.

Also, just be a little careful, as my understanding (and I am not a moderator) is that you can get a small rap over the knuckles for adding another question onto someone elses thread (I am more than happy to assist you, just letting you know as it happened to me in the past) it is apparently one of the forum rules.

Anyhoo ... good luck.
Grimace