Area Chart - Hidden and Empty Cells Settings are not maintained upon re-opening

[asilvious]

I have an area chart that I need to show empty cells as gaps. The chart shows $ amts on the y-axis and months on the x-axis. I have my formula constructed so if the cell value = 0 then the value returned is NA(). Then I have 'Show empty cells as Gaps' radio button selected under the 'Hidden and Empty Cells' settings menu. This works perfectly and I get the desired results with the area charted simply stopping at the appropriate month as opposed to trailing off to zero on the x-axis. My problem is when I save the file and re-open the setting is changed back to 'Show empty cells as Zero' so the area charts trails off to zero.

Is there a way to preserve the Hidden and Empty cell settings upon saving and reopening the file?

I am using Excel 2007 SP3.

 

[Steve=True]

Hi asilvious,

Welcome to the forums.

I think this is a bug that cannot be fixed. Thought i was able to create an Area Chart ending as a Cliff, but i had the same problem.

Every time you save and reopen, it resets.

Sorry.

Good Luck. Steve=True

 

[asilvious]

Thanks Steve=True,

I was afraid of that. As a work around I created an Autpen macro to change the setting to show empty cells as gaps and saved the file as a macro-enabled workbook.

 

[Steve=True]

That is a great solution.