Plot Hidden - default

[shades]

I've found an annoyance with Excel that doesn't seem to offer any alternatives.

Often I make charts that plot data that is in hidden rows/columns. In order to do that, I have to select the chart, go to TOOLS >> OPTIONS >> Chart tab, and uncheck the box "Plot visible cells only."

That's fine for one chart. But 90%+ of my charts involve hidden rows/columns. Yet, there seems to be no way to change the default.

This was true for Excel 97/Windows 95, and as of last week, Excel 2002/Windows 2000.

Any help?

[Damon Ostrander]

Hi shades,

I don't know how to change the Excel default, but here is a macro that you can call from the workbook's Open event that will set all the charts on all sheets (embedded or not) to show hidden values.

Sub SetPlotHidden()
Dim CO As ChartObject
Dim Cht As Chart
Dim Wsh As Worksheet
'Do all embedded charts
For Each Wsh In Worksheets
For Each CO In Wsh.ChartObjects
CO.Chart.PlotVisibleOnly = False
Next CO
Next Wsh
'Now do all chart sheets
For Each Cht In Charts
Cht.PlotVisibleOnly = False
Next Cht
End Sub