If you want to show hidden data on an Excel chart, there is one checkbox you need to know about. By default, an Excel chart only plots the cells you can see. Hide a few rows, collapse a group or apply a filter, and those months quietly disappear from the chart. That is a real problem on dashboards, where people filter and fold the table while the chart should keep telling the full story. In this tutorial you will see the problem with a simple month-wise sales chart, then fix it in four clicks with the Hidden and Empty Cells setting.
⬇️ Download the Practice File
Monthly sales data with two charts (setting on and off) · .xlsx (no macros) · Excel 2010 and later
Watch the Video Tutorial
PK filters, hides and groups the month rows to show how the chart shrinks, then turns on Show data in hidden rows and columns so the chart keeps all twelve months.
What You Will Learn
- Why months vanish from a chart when you filter, hide or collapse rows.
- Where the setting lives: Select Data → Hidden and Empty Cells.
- How to switch it on so filtered, hidden and grouped data stays on the chart.
- When to leave it off, because sometimes a chart that follows the filter is exactly what you want.
- How the empty-cell options (Gaps, Zero, Connect with line) differ from the hidden-data option.
The Example Data
The practice file has one simple table on Sheet1: Month in A1:A13 (January to December) and Sales in B1:B13. The sales run from 556 in February to 778 in January. A clustered column chart with data labels plots the twelve values, and its data range is =Sheet1!$A$1:$B$13.
The file contains two copies of the chart. The top chart already has the hidden-data setting switched on, and the bottom chart uses Excel’s default, so you can test both side by side. Rows 4 to 8 (March to July) are already grouped for you.
The Problem: Hidden Rows Disappear from the Chart
There are three everyday ways to hide rows, and all three change a default chart.
1. Filtering the table
Turn on a filter (Data → Filter) and keep only January, February and March. The chart instantly drops to three columns. Excel treats rows hidden by the filter as hidden rows, and hidden rows are not plotted.
2. Hiding rows
Clear the filter, select rows 4 to 8 (March to July), right-click and choose Hide. The chart now shows seven months and jumps straight from February to August.
3. Collapsing a group
Unhide the rows, select them again and go to Data → Group → Rows. An outline bar with a minus button appears at the left. Click the minus button to collapse the group and the five months vanish from the chart; click the plus button to expand it and they come back.
In short, a default chart only shows what is visible on the sheet. If anyone filters or folds your table, your chart changes with it.
How to Show Hidden Data on an Excel Chart
The fix is a per-chart setting. Do this for each chart that should ignore hidden rows.
Step 1: Open Select Data
Right-click the chart and choose Select Data. You can also select the chart and use Chart Design → Select Data on the ribbon.
Step 2: Click Hidden and Empty Cells
The Select Data Source window shows the chart data range, the series (Sales) and the axis labels. In the bottom-left corner there is a button called Hidden and Empty Cells. Click it.
Step 3: Tick Show data in hidden rows and columns
The Hidden and Empty Cell Settings window opens. Tick Show data in hidden rows and columns, click OK, then click OK again to close Select Data Source.
Step 4: Test it
Collapse the March to July group again. This time the chart keeps all twelve months. Apply the filter for January, February and March: the table shows three rows, but the chart still shows the full year. Hide rows by hand and the result is the same.
Why this works
The chart’s data range never changed; it was always A1:B13. What changed is the rule Excel uses when it reads that range. With the box unticked, Excel skips any row or column that is hidden for any reason (manual hide, filter or collapsed outline). With the box ticked, Excel reads every cell in the range whether you can see it or not.
Gaps, Zero and Connect with Line
This section is an addition and is not shown in the video. The same window has a second setting, Show empty cells as, which deals with blank cells rather than hidden ones:
- Gaps (the default): a blank cell leaves an empty slot in the chart.
- Zero: a blank cell is plotted as 0, which can pull a line down to the axis.
- Connect data points with line: for line and scatter charts, the line jumps over the blank cell. It is greyed out for column charts like the one in this example.
Newer versions of Excel also show a Show #N/A as an empty cell option, which treats #N/A results like blanks. Keep these options in mind when your data has missing months, and use the hidden-data checkbox when the rows exist but are out of sight.
When to Use This Setting
- Dashboards with a working table. Users can filter or collapse the detail table to read it, and the summary chart stays complete.
- Helper rows and columns. Hide the calculation rows that feed a chart so the sheet looks clean, without breaking the chart.
- Grouped reports. Fold quarters or departments in an outline while the chart keeps every period.
Leave the setting off when you want the chart to follow the filter, for example a quick ad-hoc chart where filtering is your way of choosing what to plot.
Common Errors and Fixes
Only one of my charts shows the hidden data
The setting belongs to each chart, not to the sheet. Repeat the steps for every chart, or copy a chart that already has the setting and change its data range.
The chart still changes when I use a slicer
A slicer on an Excel Table filters rows, so the same checkbox applies: open Select Data on that chart and tick Show data in hidden rows and columns. A PivotChart is different: it is built from the PivotTable itself, so slicers and pivot filters change the chart by design.
I cannot find the Hidden and Empty Cells button
The button is only in the Select Data Source window, not on the ribbon. Right-click an empty part of the chart area and choose Select Data, or select the chart and use Chart Design → Select Data. The button sits at the bottom left of that window.
Tips
- Set the option on one chart, then copy that chart (Ctrl+C, Ctrl+V) and point the copy at new data, so the setting comes along.
- Pair the setting with grouped helper columns to keep chart calculations out of sight.
- If a line chart shows ugly gaps, check the empty-cell option before you suspect hidden rows.
- Microsoft’s guide to displaying empty cells, #N/A values and hidden worksheet data in a chart covers every option in the window.
Want It Ready-Made?
If you would rather start from charts that are already built and formatted, the Data Analyst’s Chart and Visualization Pack on NextGenTemplates.com bundles ten premium chart templates, and you can browse more Excel templates there. To learn charts, slicers and complete dashboards step by step, see the course Excel Pivot Tables and Dashboards.
Related Tutorials
- Apply the Same Format to Other Charts in Excel
- Chart Templates in Excel
- 10 Pro Tips for Excel Charts
- Resize and Relocate a Chart Using an Excel Range
- Dynamic Chart with Slicer for Employee Count by Age Group
Frequently Asked Questions
Why does my Excel chart change when I filter the data?
By default a chart plots only visible cells. Rows hidden by a filter, by Hide or by a collapsed group are skipped, so the chart shrinks to the rows you can see.
How do I show hidden data on an Excel chart?
Right-click the chart, choose Select Data, click Hidden and Empty Cells, tick Show data in hidden rows and columns, and click OK twice.
Does the setting work for grouped and collapsed rows?
Yes. Collapsed outline rows are hidden rows, so once the box is ticked the chart keeps plotting them when you collapse the group.
Do I need to change the setting for every chart?
Yes. It is stored with each chart. A copy of a chart that already has the setting keeps it, so copying is a quick way to reuse it.
What is the difference between hidden cells and empty cells in a chart?
Hidden cells contain values but are out of sight. Empty cells have no value at all. The checkbox controls hidden cells, and the Show empty cells as options control blanks.
About the Author
PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, Power Query and Power BI since 2016 on his YouTube channel.
Conclusion
A default chart only plots what is visible, so filters, hidden rows and collapsed groups can quietly change your numbers on screen. To show hidden data on an Excel chart, open Select Data, click Hidden and Empty Cells and tick Show data in hidden rows and columns. Download the practice file, compare the two charts, and switch the setting on for any dashboard chart that must stay complete while people work with the table.
