Hide data labels with zero values WITHOUT changing number format

[raheem]

Hi,
How to hide data labels with zero value? I did a bit search but all solutions suggested are by changing number format. As I already have a defined number format in the chart, changing format is not an option. Also, VBA is not an option.

 

[raheem]

Ok maybe I didn’t mention properly. Here is the case. All figures are in millions with scale. Means, 1,234,567 is shown as 1.23 in the chart. Likewise, -457,310 is shown as -0.46. This must remain same. But there should be no data label if value is zero.

 

[RoryA]

So apply a custom number format to the data labels of:

0.00,,;-0.00,,;;

 

[Ormie]

Right click data labels and choose format.

Under number select custom category and then under format code type:

0;-0;

 

[Jon Peltier]

Hi,
How to hide data labels with zero value? I did a bit search but all solutions suggested are by changing number format. As I already have a defined number format in the chart, changing format is not an option. Also, VBA is not an option.

So what's the number format?

A number format is like this:
{format if positive};{format if negative};{format if zero};{format if text}

So just delete whatever is in the {format if zero} position, or if there is no {format if zero}, append a semicolon.

 

[raheem]

The requirement of report is that number format should be #'##0,
This I cannot change. Was just wondering if I can do something with chart itself not to show zero value labels. But from all answers, it looks like that this is not possible without changing the number format.

 

[RoryA]

I don't think you're getting what we are saying. If you change the number format of the labels to use:

#'##0,;-#'##0, ;;

then they will display positive (and negative) numbers in the format you want but they won't show 0 values at all.

 

[raheem]

I don't think you're getting what we are saying. If you change the number format of the labels to use:

#'##0,;-#'##0, ;;

then they will display positive (and negative) numbers in the format you want but they won't show 0 values at all.

O yes... Got it.... . Although if the format is applied on data table, the negative numbers get a slightly increased indent in the cell as compare to positive, but that's fine. Thanks for help.

 

[RoryA]

You should be able to adjust the formatting to line everything up as you need.

 

[Jon Peltier]

Remove the space after the second comma in Rory's number format.

 

[raheem]

Remove the space after the second comma in Rory's number format.

Didn't notice. Looks like I am too naive . Thanks.