How to have the X axis label of a graph be hidden if the value is zero without actually hiding it?

[hmmmidk220]

Is this possible?

I have a bar graph with Costs on the x axis and values on the y axis. Cost 2 (the 2nd x axis label) has a zero value while all the other x axis labels have non zero values. I want cost 2 to be automatically hidden when there is a zero value for it.

I know I can manually hide it, but I'm looking for an automatic solution. I've spent hours online looking for a solution, but can't find anything that works. I don't want to use VBA, as I don't want other people that use it to have to worry about running code. I've read that #N/A seems to be ignored by excel but I can't get that to work (the x axis label always stays there)

Is this possible in excel 2010???

Thank you!

 

[myall_blues]

Do you mean you want to exclude the space for the bar, or just take the label off the X axis? if the latter, what about setting up dummy labels that rely on a formula?

For example:
A1:D1 contain actual X values (Cost 1, Cost 2 etc)
A2:D2 contains a formula - for A2 it would be =IF(A3=0,"",A1) - and copied right
A3:D3 contains your data (Y values)

For the bar graph, the data range is A2:D3.

Regards

Murray

 

[hmmmidk220]

Thanks for the response. I mean the actual space for the bar. Your method does well at removing the label, but there is a very obvious space there and that is what I want to remove. Any ideas?

 

[myall_blues]

How are your data arranged? If in columns then the simplest way is to use Autofilter to filter out the zeros.

So for example, A1:B1 contain column headers
A2:A5 contain X values (labels)
B2:B5 contain data values

Data, Filter, and set the filter on Column B to exclude zeroes.

Use the resultant range (which will have rows containing zeroes hidden) as the range for your chart.

Regards

Murray