Lines plotted on graph where there is 0

[Dibbley247]

I'm in the process of making multiple graphs, and a couple of them are for the 52 weeks of the year, so, at the moment my graphs have that ugly line along the bottom.

I know that this can be removed by creating a formula to display NA so the line does not appear, But the source data is put on automatically with a cell offset macros which is done at the end of the week amongst other things, so If NA was displayed, then the cell offset would not paste into the relevant cells as they are not vacant.

So is there any type of formatting on the charts I can do to not get this line!

Thanks

[Steve=True]

Hi Dibley,

Can you make a chart data range in another area that your chart is created from vs where the data is populated?

Steve=True

[Andy Pope]

If, as you say, the cells are truely empty then the line displaying zeros is either caused by.

Using a stacked line chart
or setting the option for empty cells to be plotted as zero. This setting can be found on the Select Data > Hidden and Empty cells dialog.

A named range would allow dynamic chart data source

[Dibbley247]

Very sorry, i've not been on for some time.
Yes the cells are truly empty, and figures are filled in with a macro celloffset.

Ive gone onto the graph, Select data, hidden and empty cells dialog, But the only option I have is ZERO, the other 2 options are greyed out. And i'm guessing I need the "Leave Gaps" check box. How do I get this option un greyed?

Thanks

[Dibbley247]

Ah, I am currently using a line graph, but if I change this line to a line graph with the dots, Then I get the option to leave blank, but I don't like having these dots!

Any way round this?

[Andy Pope]

Given the options where greyed out I think you where using the Stacked line. You can simply use the clustered line.
Or if you do use Line with markers then format the series to have No Marker.

[Dibbley247]

Ok I'll have a look when I'm on the PC next.
There is another graph I have where the column looks empty but there's a formula, so it's not truly empty, is there a workaround on this also?
Thanks