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.
- Right-click on the chart. Click Select Data… from the menu.
- From the Select Data Source window click Hidden and Empty Cells (it has been there all along, but you’ve never noticed it before).
- The Hidden and Empty Cells Settings window will open. Enable Show data in hidden rows and columns, click OK, then OK again.
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.
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).
The small grey boxes with downward triangles are displayed. Click on this icon. The AutoFilter options will appear.
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.