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.
Formula Magic with Dynamic Arrays
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.
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.
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.
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.
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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- 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.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: