on September 17, 2009, 5:00 PM PDT Excel won't display hidden data in a chart unless you flip the switch. Learn which option controls this behavior. By default, Excel displays only visible data in a chart. Consequently, if you hide worksheet data, Excel won’t display that data in a chart. For instance, it’s obvious from a quick look at the following chart’s legend that Tuesday’s data is missing: In Excel 2007, do the following: Excel displays the hidden data in the chart without unhiding the data in the worksheet. For
example, the modified chart below displays Tuesday’s data even though that data is still hidden in the worksheet.
Display hidden data in an Excel chart
Most of the time, that’s probably what you’ll want. After all, if you went to the trouble to hide the data at the worksheet level, it’s doubtful that you’ll want to expose the data in a chart. However, it’s easy enough to display the hidden data for those times when you do. To display hidden data in a
chart, do the following:
- Software
Imagine you’ve created a beautiful chart for your Excel report. You decide to hide the source data because the report’s users don’t need to see that. Suddenly the information in the chart disappears. So, this raises the question: how to show hidden data in an Excel chart?
We answer this question in this post. But, we go further. We also look at how we can use this technique for more advanced user interactivity.
Watch the video
Watch on YouTube
Download the example file
I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be helpful for future reference.
Download the file: 0113 Show hidden data in Excel chart.zip
- How to show hidden data in an Excel chart
- Use hidden data to create dynamic charts
- VBA code to toggle between showing hidden/visible
data
- Toggle hidden cells for active chart
- Toggle hidden cells for all charts on worksheet
- Apply hidden setting of active chart to charts on same worksheet
- Office Scripts to toggle
between showing hidden/visible data
- Toggle hidden cells for active chart
- Toggle hidden cells for all charts on worksheet
- Apply hidden setting of active chart to charts on same worksheet
- Conclusion
The option to display or hide chart data is set on a chart-by-chart basis. It is one of those settings you have probably seen many times but never noticed it.
To show hidden data in an Excel chart:
- Right-click on the chart. Click Select Data… from the
menu.
- In the
Select Data Source dialog box, click the Hidden and Empty Cells
button.
- The Hidden and Empty Cells Settings dialog box opens. Enable Show data in hidden rows and columns, then click
OK.
- Click OK again to close the data source settings dialog box.
That’s it, that’s all it takes. The chart information is now visible again. Take a mental note of the additional settings in the Hidden and Empty Cells Settings dialog box; you never know when they might be useful.
Use hidden data to create dynamic charts
Initially, the feature to displaying hidden data may seem annoying. However, it actually creates a new level of flexibility for displaying charts. By combining this setting with AutoFilter, or an Excel Table, we can specify which rows to display inside a chart.
Example
The following is the source data for a chart:
That source data as a bar chart displays as follows:
As the graph is connected to the Table, filtering the Table shows only the selected items in the chart.
As an example, I have selected North in the Region column of the Table. Therefore, the chart also updates displays only the North region.
This creates a flexible dashboard-style interactivity by harnessing the power of hidden rows.
VBA code to toggle between showing hidden/visible data
If we have a lot of charts, it can be time-consuming to apply this to each chart individually. The following VBA codes toggle the hidden cells setting in various scenarios.
For other VBA chart examples, check out this post: Ultimate Guide: VBA for Charts & Graphs in Excel (100+ examples)
Toggle hidden cells for active chart
The following code toggles the hidden cells setting on the active chart only.
Sub ToggleChartDisplayHiddenRows() 'Declare and assign variable Dim cht As Chart Set cht = ActiveChart 'Ignore errors if no chart active On Error Resume Next 'Toggle hidden data visibility cht.PlotVisibleOnly = Not cht.PlotVisibleOnly On Error GoTo 0 End SubToggle hidden cells for all charts on worksheet
The following code toggles the hidden cells for each chart.
Sub ToggleChartDisplayHiddenRowsAllOnSheet() 'Declare and assign variable Dim chtObj As ChartObject 'Loop through all charts on the worksheet For Each chtObj In ActiveSheet.ChartObjects 'Toggle hidden data visibility chtObj.Chart.PlotVisibleOnly = Not chtObj.Chart.PlotVisibleOnly Next On Error GoTo 0 End SubApply hidden setting of active chart to charts on same worksheet
The following code changes the setting for every chart on the worksheet to be identical to the active sheet.
Sub ToggleChartDisplayHiddenRowsSameAsActive() 'Declare and assign variable Dim chtObj As ChartObject Dim hiddenRowsSetting As Boolean 'Capture the PlotVisibility of active chart hiddenRowsSetting = ActiveChart.PlotVisibleOnly 'Ignore errors if no chart active On Error Resume Next 'Loop through all charts on the worksheet For Each chtObj In ActiveSheet.ChartObjects 'Toggle hidden data visibility chtObj.Chart.PlotVisibleOnly = hiddenRowsSetting Next On Error GoTo 0 End SubOffice Scripts to toggle between showing hidden/visible data
Below are 3 Office Scripts that perform the same tasks as the VBA codes above.
Toggle hidden cells for active chart
The following script toggles the hidden cells setting on the active chart.
function main(workbook: ExcelScript.Workbook) { //Declare and assign variable let cht = workbook.getActiveChart() //Ignore errors try { //Toggle hidden data visibility cht.setPlotVisibleOnly(!cht.getPlotVisibleOnly()) } catch (err) { } }Toggle hidden cells for all charts on worksheet
The following script toggles the hidden cells for every chart on the active worksheet.
Apply hidden setting of active chart to charts on same worksheet
The following script changes every chart on the worksheet to have the same setting as the active sheet.
function main(workbook: ExcelScript.Workbook) { //Declare and assign variable let ws = workbook.getActiveWorksheet() let chtArr = ws.getCharts(); //Loop through all charts on the worksheet for (let i = 0; i < chtArr.length; i++) { //Ignore errors try { //Toggle hidden data visibility chtArr[i].setPlotVisibleOnly(workbook.getActiveChart().getPlotVisibleOnly()) } catch (err) { } } }Conclusion
In this post, we have seen it is easy to show hidden data in an Excel chart. Then we saw how to harness the power of this feature to create dynamic charts which update based on filter selection. Finally, we looked at VBA and Office Scripts methods to automate changing the Show data in hidden rows and columns setting.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match 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.
What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: