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.

Formula Magic with Dynamic Arrays

Of all the features available in Excel dynamic arrays provide the most power for the smallest time investment.  Yet most Excel users do not even know what they are.

Have you ever faced these spreadsheet scenarios?

  • How can I use VLOOKUP to return all the matching items, not just the first?
  • How can I sort my information using a formula, so I don’t have to keep clicking the sort button?
  • How can I quickly create unique lists of items to use with my SUMIFS calculation?
  • How can I stop copying down formulas every time my source data changes.
  • How can I build a PivotTable-like report, but using formulas so I don’t have to click refresh ever again.

Well, I’m here to give you some good news. with dynamic arrays, all these can be achieved easily 🙂

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



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Email Address * First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Save

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 *