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.

Claim your free eBook


How to show hidden data in Excel chart

Show hidden data in Excel Chart

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 Icon

Download the file: 0113 Show hidden data in Excel chart.zip

How to show hidden data in an Excel chart

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:

  1. Right-click on the chart. Click Select Data… from the menu.
    Select data from chart right-click menu
  2. In the Select Data Source dialog box, click the Hidden and Empty Cells button.
    Hidden and empty cells button
  3. The Hidden and Empty Cells Settings dialog box opens. Enable Show data in hidden rows and columns, then click OK.
    Hidden and empty cells dialog box
  4. 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:

Chart Data

That source data as a bar chart displays as follows:

Unfiltered chart

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.

Filtering the Table shows only the selected items in the chart

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.

Save


Headshot Round

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:

  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:

Leave a Reply

Your email address will not be published. Required fields are marked *