How to get Excel chart to ignore zero values, widen columns, and move axis

Hi,

I am using Excel 2010 in 2003 compatibility mode. I am trying to create a graph that shows movement of results up or down from a baseline of 90%. I'm having several problems with it:

1- The columns are all squashed up as though it is leaving space for the other days of the week. I want them spread out across the gap as the data is shown at weekly intervals, not daily.

2 - I want the axis to cross at 90% which is our target, however, this moves the axis category names into the middle of the graph. Can these be moved back down to the bottom of the chart?

3 - I have selected for zero values to be shown as gaps, however, they are still appearing as zero making the chart unreadable.

i have attached a stripped down version of my original sheet. Would appreciate any advice.

Thanks,

Hi

try this:

For some reason excel is reading your empty cells as not blank – if you delete the contents, the zero lines on the graph will disappear.

You will need to set the axis minimum to something more than 0 to reduce the space below the bars.

Unfortunately, Excel only recognises day, month and year as units of time for its time series charts. The easiest way to present the data as you want is to set the X axis type to “text axis” not “date axis”

To display the axis labels at the bottom of the chart, change the axis labels position from “next to axis” to “low”

Thanks for your reply Nicky. That has helped. Do you have any idea of the following?

1 - when I change the axis type to text. I now only get 2 category names, 22/4/2012 and 10/6/2012. the other areas are blank. I have changed them all to format 'text' and even put a ' in front of them to denote them as text but it doesn't seem to see them

2 - I think the reason it didn't see them as blanks is because in the original document, they are referencing cells in another worksheet that are populated with an IF(ISERROR calculation returning "" if there is a DIV/#0 error. This is to make the report more readable for users. Do you know of anyway I can return a blank cell rather than "" in an IF(ISERROR calc?

Thanks for your help

Hi
Try setting the interval between labels and tick marks to 1, not 7
One of the forum gurus may have a better solution, but by changing your if formula to return N/A if there is an error, the chart should work, eg

=IF(ISERROR(A1),NA(),A1)

Thanks Nicky. That did the trick with the axis (we've gone from 2003 to 2010 and I'm really struggling with charting!!). Returning #N/A is not ideal as it is a very 'busy' report and lots of errors in there makes it hard to read. It may be easier for me to just manually delete all "" cells after the weekly data has been updated.

Thank you!

Hi
you could apply conditional formatting e.g. make the cells' font white if their value is n/a, so it's not visible

Of course! I do that in another report so I don't know why I didn't think of it! Thanks again