This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

Using hidden cells in a chart

Use hidden cells in a chart thumb

Use hidden cells in a chart

Imagine, you’ve created a beautiful chart for your Excel report, to make it more beautiful you hide the source data so that it’s not visible to the recipients.  They don’t need to see the data anyway, so hiding it seems the best way to get them to focus on the chart.  But what happens?  As soon as the data is hidden, the information in the chart disappears too.  What do you do?  This post will cover that very question. By looking at (1) how to show hidden cells in a chart and (2) how to use hidden data to create dynamic charts.

 

How to show hidden cells in a chart

Showing hidden data in a chart is a simple fix.

  1. Right-click on the chart.  Click Select Data… from the menu.
    Hidden chart data - Select Data
  2. From the Select Data Source window click Hidden and Empty Cells (it has been there all along, but you’ve never noticed it before).
    Hidden chart data - Hidden and empty cells
  3. The Hidden and Empty Cells Settings window will open.  Enable Show data in hidden rows and columns, click OK, then OK again.
    Hidden chart data - show hidden data enabled

Advertisement:

The chart information is now visible again.  Take a mental note of the additional settings in the Hidden and Empty Cells Settings window, you might need these one day.

 

How use hidden data to create dynamic charts

Initially the feature of not displaying hidden data can seem a bit annoying.  But it actually creates a new level of flexibility for displaying charts.  We can now use AutoFilter, or hide specific rows/columns to just display the information we want in the chart.

As an example, using AutoFilter, it is possible to create a dynamic chart, which only displays the selected fields.


Advertisement:

Here is my source data and chart.

Hidden chart data - source data
Hidden chart data - initial chart

An AutoFilter has been added to the data.  Select the information (Cells A1-C11) and click Data -> Filter. (Alt, A, T if you’re a shortcut hound).

Hidden chart data - Data Filter

The small grey boxes with downward triangles are displayed.  Click on this icon.  The AutoFilter options will appear.


Advertisement:

Hidden chart data - AutoFilter

I selected North and clicked OK.

Notice how both the data table and the chart have been updated to just display the individuals in the North region.

Hidden chart data - filtered data
Hidden chart data - filtered data chart

Save