Hiding Worksheet Data Used in Excel Charts and Dashboards

Excel

Recently an associate called me with an Excel 2010 problem. She was asked to create an elegant worksheet that pulled data in from an external database, did calculations, and then charted the results as well as creating a columnar report. Sounds pretty easy so far.

The problem she ran into was that when she hid data in the Excel workbook her Excel chart content disappeared. How could she show or hide data and still have the chart show the results? When the data appeared on the sheet, the chart data displayed as in this figure.

Excel 2007, 2010 showing a chart with data hidden.

Excel 2007, 2010 showing a chart with data hidden.

When she hid the data by hiding columns K and L, then the chart data disappeared as in this figure.

Excel 2007, 2010 hiding data causes the chart content to disappear.

Excel 2007, 2010 hiding data causes the chart content to disappear.

It turns out it’s a pretty straightforward problem to fix, if you know where the right dialog box is. To fix this, select the chart. A group of Chart Tools menus will display on the ribbon.

  1. On the Design tab, in the Data group, click Select Data.
  2. The Select Data Source dialog displays.
  3. Click the Hidden and Empty Cells button to display the Hidden and Empty Cell Settings.
Excel 2007, 2010 Select Data Dialog

Excel 2007, 2010 Select Data Dialog

  1. In the Hidden and Empty Cell Settings dialog box, click the Show Data in Hidden Rows and Columns box, then click Ok.
Use the hidden cells settings to show hidden data.

Use the hidden cells settings to show hidden data.

Now you can hide or display data in Excel and still the chart.

 

Share the power...