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

Using hidden cells in a chart

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

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.

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.

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


3 thoughts on “Using hidden cells in a chart

  1. Marian says:

    Have you got any idea why is this field grayed out in waterfall charts? What could I use in order to hide chart data in that case?

    • Excel Off The Grid says:

      There are elements of the Waterfall Chart feature which don’t behave the same way as other charts. My guess is that the Hidden and Empty Cells feature has been disabled on purpose, probably because the chart can’t handle it.

      Are you trying to hide all the data? If so, some ideas are:
      – Hold the data on a hidden worksheet
      – Include the data on the far right, then set the ScrollArea property, with the data outside of the ScrollArea

      If you are trying to hide only some rows, the Waterfall Chart won’t ever work, as it does not know which columns to treat as totals.

Leave a Reply

Your email address will not be published. Required fields are marked *