How to show hidden data in an Excel chart or in Excel Sparklines.
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:
The same problem can occur with Excel sparklines -- tiny in-cell charts.
See the sections below, for steps to fix these hidden data issues:
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.
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:
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.
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.
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:
After you change that chart setting, you can hide rows or columns in the worksheet data, and the chart data will remain visible.
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:
The macro code changes the Show Hidden Data setting only, either turning it on, or turning it off.
There are two macros shown below -
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
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
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
For example, in the screen shot below, there is a list of cities, in column B.
Note: You can get this Sparklines workbook in the Download section below.
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
To create a sparkline, follow these steps:
In the Create Sparklines dialog box, follow these steps:
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,
To show the hidden data in your sparklines, follow these steps:
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:
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
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
Last updated: March 23, 2022 9:49 AM