The default setting for charts in Excel is to hide the data on the chart if it is hidden on the sheet. I forgot that recently when I created a few charts using a workings area to hold the chart data. I later hid the workings with column grouping. Oops – when you hide the data in the charts go blank.
To fix the issue takes six clicks for each chart. There were seven charts, so I recorded a macro and did it once and then used the macro on the other charts.
(Check out the comments section below – chart legend Jon Peltier has provided some improved macro code to help out – thanks Jon!)
I had to tweak the recorded code to get it to work on the selected chart.
When you record chart macros they tend to include the chart name. You can delete some lines of code and use ActiveChart and it should work.
I selected the chart and recorded a macro.
I right clicked the chart and chose Select Data.
I clicked the Hidden and Empty cell button
An ticked the Show Data …. check box and clicked OK and then OK again.
That’s it, done.
I stopped the macro recorder.
The recorded macro is shown below.
Sub chtChangeVisible() ' ' chtChangeVisible Macro ' ActiveSheet.ChartObjects("Chart 1").Activate Application.CutCopyMode = False ActiveChart.PlotVisibleOnly = False End Sub
To run this on the selected chart we can remove the first two lines of code.
Sub chtChangeVisible() ' ' chtChangeVisible Macro ' ActiveChart.PlotVisibleOnly = False End Sub
That’s the macro fixed.
You can now select a chart and run this macro to change the data visibility setting.
If you are unsure how to use macros check out this bog post.