How do I find hidden data in Excel?

Question

on September 17, 2009, 5:00 PM PDT

Table of Contents Show

Display 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
How do I show hidden data?
How do I show hidden data in Excel chart?
How do I inspect hidden properties in Excel?

Display hidden data in an Excel chart

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:

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:

Select the chart.
From the Tools menu, choose Options.
Click the Chart tab.
In the Active Chart section, clear the Plot Visible Cells Only Option.
Click OK.

In Excel 2007, do the following:

Select the chart.
Click the Design tab.
Click Select Data in the Data group.
Click the Hidden And Empty Cells button (at the bottom).
Select the Show Data In Hidden Rows And Columns option.
Click OK twice.

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.

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 Sub

Toggle 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 Sub

Apply 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 Sub

Office 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.

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(!chtArr[i].getPlotVisibleOnly())
  } catch (err) {

  }
}

}

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:

How do I show hidden data?

Select the Start button, then select Control Panel > Appearance and Personalization. Select Folder Options, then select the View tab. Under Advanced settings, select Show hidden files, folders, and drives, and then select OK.

How do I show hidden data in Excel chart?

Click the chart you want to change. Go to Chart Tools on the Ribbon, then on the Design tab, in the Data group, click Select Data. Click Hidden and Empty Cells. In the Show empty cells as: options box, click Gaps, Zero, or Connect data points with line.

How do I inspect hidden properties in Excel?

In the copy of your original workbook, click File > Info. Click Check for Issues, and then click Inspect Document. In the Document Inspector box, check the boxes of the types of hidden content you want to be inspected.

Cara Belajar How