REMOVE ZERO VALUE LABEL FROM AXIS

[Satish Bhardwaj]

Hi,

I am making a dashboard on month basis when I select the one month excel chart shows the horizontal labels with zero value .

I don’t want to show label with zero value. Because brand names are more than 150 nos and when we see it on month basis only 10 or 15 has value rest are zero.

So how we can remove the zero value axis.

Please help.
I am using office 2013.

 

[Hui]

Select the Axis
Right Click on it and select Format Axis
Select Number
Select Custom
Enter a Custom Number format of 0.00;-0.00;;

Adjust the decimals to suit in the above by adding or removing the trailing zeroes

 

[Satish Bhardwaj]

thanks,

but with this 0.00;-0.00;; the zero value show blank
but i want remove label name which have zero value.

 

[Hui]

Do the same for the Labels

 

[Satish Bhardwaj]

But sir how i remove below country name which have 0 value when we change the month.

 

[Hui]

Can you post the file so we can give you a targeted response
But simply you either:

1. Filter the data so that there is only 5 entries in the chart range or
2. Put an If() in the country names field so that it is "" when the value is 0 but the full 20 countries are still charted

 

[Satish Bhardwaj]

Can you post the file so we can give you a targeted response
But simply you either:

1. Filter the data so thzero
here is only 5 entries in the chart range or
2. Put an If() in the country names field so that it is "" when the value is 0 but the full 20 countries are still charted

Yes Sir, shortly I will share the file,
I have used if(), "" formula with this cell value show nothing with this chart label show nothing instead of zero
I also tried offset(reference, 1counta($b:$b)-1,1)
But my problem not solved.

Here logic is that cell has zero value or "" formula, and I don't want to show country name which have no value.
It is possible that next month may be 10 countries have data rest are zero,
Plz give advice
Thanks

 

[vletm]

@Satish Bhardwaj
Check this sample how to do it ...

 

[Hui]

Hows this?

 

[Satish Bhardwaj]

thanks we are near to out solution but not clear solution.

sir in attached file i used your example.
i don't want to show blank country name label if it is more than 50 or 60 than chart length will be high.

i want to show only those level which have value more than zero.

plz

 

[vletm]

@Satish Bhardwaj
Again ...
Check this sample how to do it ...

 

[Satish Bhardwaj]

 

[Satish Bhardwaj]

A lot of thanks sir but i want to do the same without VBA
my complete dashboard is without VBA

please advice without coding

in dashboard user will select the month not click the All/filter button

 

[vletm]

@Satish Bhardwaj
I would like to get many thing too ... 'coding' is my way.

 

[Satish Bhardwaj]

Ok sir Thanks a lot !!!!

 

[Satish Bhardwaj]

Could you please put this code in my file as dashboard attached above.
Please

 

[Satish Bhardwaj]

I have solved it without coding.
happy