Show Hidden Data in Excel Chart

How to show hidden data in an Excel chart or in Excel Sparklines.

Show Hidden Data in 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 worksheet 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.

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

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) Show Hidden Data in Excel Chart

For example, in the chart below, there are 8 rows of sales data, in cells A2:C9. The Region column has been filtered, and only the 4 rows from the East region are visible.

• 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

Change Chart to Show Hidden Data

If you want to change this chart behaviour, and show all the data, including hidden data, follow the steps below.

There are written steps, and a video, in the sections below:

--Video: Show Hidden Data in Excel Chart

--Hide Chart Data on Worksheet

--Chart Ignores Hidden Data

--Show Hidden Data in Chart

--Macros to Change Chart Settings

--Try This Interactive Chart

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.

Chart Ignores Hidden Data

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

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 worksheet shown below, only the East region’s data is visible on the sheet and in the chart.

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

Show Hidden Data In Chart

In some cases, you might like that hidden data feature, and not mind that the chart changes. It helps you focus on the filtered data in the chart.

If you want your chart to show all the data, even if some of the source data is hidden, you can change one of the chart settings

To change chart setting, follow these steps:

• Click on the chart to select it, and on the Excel Ribbon, under Chart Tools, click the Chart Design tab
• Click the Select Data command
• Click the Hidden and Empty Cells button
• Add a check mark to ‘Show data in hidden rows and columns’
• Click OK, twice, to close the dialog boxes

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.

Macros to Change Chart 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 code changes the Show Hidden Data setting only, either turning it on, or turning it off.

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.

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

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 Revenu sparklines

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.

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