Show Hidden Data in Excel Chart

Why did data disappear from your Excel chart or Excel Sparklines? Sometimes, if worksheet rows are hidden, Excel chart data disappears. See how to unhide a chart in Excel. You can watch my short video, or follow the written steps and screen shots. Get the sample Excel files too!

hidden worksheet data not showing in chart

Author: Debra Dalgleish

Video: Show Hidden Data in Excel Chart

If you create an Excel chart, then hide rows or columns in your worksheet, the hidden data might also disappear from your chart. By default, Excel charts do not display the data in hidden rows and columns.

This video shows how to change an Excel chart's settings, so all the data will appear in the chart, even if some of the data rows or data columns are hidden.

There are written steps below the video.

Show Hidden Data In Chart

If you create an Excel chart, then hide rows or columns in the chart data, the hidden data might also disappear from your chart.

That happens because Excel charts have a default setting -- charts do not display the data in hidden rows and columns.

Fix Hidden Data Setting

If you want an Excel chart to show all the data, even if some of the source data is hidden, follow the steps below, to change the chart setting:

  • First, click on the Excel chart to select it
  • Next, on the Excel Ribbon, under Chart Tools, click the Chart Design tab
  • In the Data group, click the Select Data command
  • When the Select Data Source window opens, click the Hidden and Empty Cells button -- it's at the bottom left corner of the window.
  • Next, in the Hidden and Empty Cell Settings dialog box, add a check mark to the option -- Show data in hidden rows and columns
  • Then, click the OK button, to close the dialog box
  • Finally, click the OK button, to close the Select Data Source window

hidden worksheet data not showing in chart

Chart Shows All Data

After you change that chart setting, you can hide rows or columns in the worksheet data, and the chart data will remain visible.

Examples: Hidden Data - Excel Chart or Sparkline

You can add a chart or sparkline in Excel, based on worksheet data. Later, if you filter the data, and rows are hidden, that data also disappears from the chart or the sparkline.

For example, in the Excel sheet shown below:

  • There are 8 rows of sales data, with a filter applied on the Region column.
  • Instead of showing all the data, the chart only shows data from the visible rows.

hidden worksheet data not showing in chart

The same problem can occur with Excel sparklines -- tiny in-cell charts.

sparklines ignore hidden data

See the sections below, for steps to fix these hidden data issues:

--A) Show Hidden Data in Excel Chart

--B) Show Hidden Data in Sparklines

A) Example: Hidden Data in Excel Chart

In this example, there is a line chart based on worksheet data, from cells A1:C9.

There are 8 records on the worksheet, and 8 points in the line chart

hidden worksheet data not showing in chart

Hide Chart Data Rows

The chart looks fine, but if you filter the data, some of the worksheet rows are hidden, and that data also disappears from the chart.

For example, in the screenshot below, only the East region’s data is visible on the sheet and in the chart. The Region column has been filtered, and only the 4 rows from the East region are visible.

Note: It will also affect the chart if you hide columns where chart data is stored.

  • In the chart, only those 4 sales from the East region are shown.
  • The West region sales, in the hidden rows, are not in the chart

hidden worksheet data not showing in chart

Macros - Change Hidden Data Settings

Some of my workbooks have many charts, so I wrote a few macros to make it easy to edit the settings in all of them.

There are buttons in the Hidden Chart Data sample file, to run those macros, and code for a couple of the macros is listed below. To get the other macro, download the sample workbook, and copy it from there.

There are three groups of macro buttons, to change the following charts:

  • All charts, on all worksheets
  • All charts, on the active sheet only
  • Selected chart only

The macro VBA code changes the Show Hidden Data setting only, either turning it on, or turning it off.

hidden worksheet data not showing in chart

Sample Macros - ALL Charts on ALL Worksheets

There are two macros shown below -

-- VisDataOnlyOnALLSheets

  • Turns ON the PlotVisibleOnly setting
  • Only the visible data on the worksheet is included in the charts

-- VisDataOnlyOffALLSheets

  • Turns OFF the PlotVisibleOnly setting
  • Both hidden and visible data on the worksheet are included in the charts

Copy this macro code, and paste it into a regular code module in your workbook. Later, run this macro to change the settings for all charts, on all worksheets in the active workbook.

Sub VisDataOnlyOnALLSheets()
'only visible data in chart
'all charts - all worksheets
Dim wb As Workbook
Dim ws As Worksheet
Dim ch As ChartObject

Set wb = ActiveWorkbook
On Error Resume Next

For Each ws In wb.Worksheets
  If ws.ChartObjects.Count > 0 Then
    For Each ch In ws.ChartObjects
      ch.Chart.PlotVisibleOnly = True
    Next ch
  Else
    MsgBox "No charts on this sheet"
  End If
Next ws

End Sub
'=================================
Sub VisDataOnlyOffALLSheets()
'include hidden data in chart
'all charts - all worksheets
Dim wb As Workbook
Dim ws As Worksheet
Dim ch As ChartObject

Set wb = ActiveWorkbook
On Error Resume Next

For Each ws In wb.Worksheets
  If ws.ChartObjects.Count > 0 Then
    For Each ch In ws.ChartObjects
      ch.Chart.PlotVisibleOnly = False
    Next ch
  Else
    MsgBox "No charts on this sheet"
  End If
Next ws

End Sub

Try This Interactive Chart

To see the difference this option setting makes, test the feature in this embedded Excel file.

Filter the Region column, and one chart continues to show all the data, but the other chart has hidden data

B) Show Hidden Data in Sparklines

Excel has an in-cell chart feature called sparklines, for Excel 2010 and later. A sparkline is a mini-chart in a worksheet cell, that shows a trend in a series of values.

The following sections show a brief overview of sparklines, and show how to see hidden row and column data in sparklines

--Sparkline Example

--Video: Create Sparklines in Excel

--Sparklines Ignore Hidden Data

--Show Hidden Data in Sparklines

--Macro to Change Sparkline Setting

Sparkline Example

For example, in the screen shot below, there is a list of cities, in column B.

  • Column C - Sparklines show expense amounts over 3-month period
  • Column D - Sparklines show revenue amounts over 3-month period
  • Columns M:R (not shown) have the expense and revenue data for 3 months

Note: You can get this Sparklines workbook in the Download section below.

sparklines show revenue and expense amounts

Video: Create Sparklines in Excel

To see how to insert Sparklines in an Excel worksheet (Excel 2010 and later), watch this one-minute video, created by Microsoft.

There are written steps below the video

How to Create a Sparkline

To create a sparkline, follow these steps:

  • Select a blank cell near the data that you want in the sparkline
  • On the Excel Ribbon, click the Insert Tab
  • Click the Sparkline command, then click one of the Sparkline types: Line, Column or Win/Loss

In the Create Sparklines dialog box, follow these steps:

  • Click in the Data Range box, then select the worksheet cells that contain the data you want in the sparkline
    • Note: The data cells must be in a contiguous range on the worksheet
  • (if necessary, move the dialog box, so you can select the cells)
  • In the Location Range box, the active cell address is automatically entered
  • Click the OK button, to close the dialog box, and to create the sparkline

Create Sparklines dialog box

Sparklines Ignore Hidden Data

After you create Excel sparklines, you might want to hide the sparkline data, to "de-clutter" the worksheet.

Unfortunately, as soon as you hide the columns or rows where the data is entered, the sparkline data might disappear. Just like normal Excel charts, sparklines have a default setting to hide data that is hidden on the worksheet.

In the screen shot below,

  • Column N is hidden, and it has the February expense data
    • In column C, only the January and March numbers are in the Expenses sparklines.
    • February data is not included.
  • None of the Revenue data columns are hidden
    • In column D, all three months are shown in the Revenue sparklines

sparklines ignore hidden data

Show Hidden Data in Sparklines

To show the hidden data in your sparklines, follow these steps:

  • Select the sparkline cell, or one group of sparkline cells
    • You cannot change multiple sparkline groups at the same time
  • On the Excel Ribbon, click the Sparkline tab that appears
  • At the left end of the tab, click the Edit Data command
  • In the drop down menu, click the Hidden & Empty Cells command
  • In the Hidden and Empty Cell Settings dialog box, add a check mark to the Show Data in Hidden Rows and Columns setting
  • Click the OK button, to apply that setting.

hidden data appears in the sparklines

Macro to Change Sparkline Setting

Unfortunately, you cannot edit the settings for more than one sparkline group at a time.

Some of my workbooks had 20 or more sparkline groups, so I wrote a macro to make it easy to edit all of them.

The macro will set each sparkline group on the active sheet to:

  • show empty cells as Gaps (xlNotPlotted)
  • show data in hidden rows and columns

Copy this macro code, and paste it into a regular code module in your workbook. Later, run this macro to change the settings for all sparkline groups on the active sheet.

Sub SparklinesFix()
Dim spk As SparklineGroup
For Each spk In ActiveSheet.Cells.SparklineGroups
    spk.DisplayBlanksAs = xlNotPlotted
    spk.DisplayHidden = True
Next spk
End Sub

Download Sample Files

Hidden Chart Data: Get the Excel workbook with hidden chart data, and change setting to show that data in the chart, manually, or with macros. The zipped workbook is in xlsm format, and contains the macros from this page

Sparklines for Hidden Data: Download the Excel workbook with hidden Sparklines data example. Macro changes all sparklines on active sheet, so they will show data, even if rows and columns are hidden. Excel 2010 or later. The zipped workbook is in xlsm format, and contains the macros from this page

More Chart Tutorials

Cluster Stack Pivot Chart

Charts, Interactive

Show Target Range in Line Chart

Charts, Line-Column 2 Axes

Pie Charts

Panel Chart

Waterfall Chart

Last updated: December 22, 2023 4:29 PM