How to Show Hidden Data in Excel Chart

When you hide rows or columns in Excel, your chart data often vanishes along with them. This default behavior can be frustrating, especially when you need to show the big picture in a graph while organizing the underlying data. This guide will walk you through the simple fix to keep your charts complete, regardless of what rows or columns are hidden.

Why Does Excel Hide Chart Data by Default?

First, it helps to understand the logic behind Excel's behavior. The program generally operates on a "what you see is what you get" principle. When you hide a row or column, you're telling Excel that you don’t want to see that data at the moment. Excel extends this logic to charts, assuming that if the source data is hidden, it shouldn't be plotted either.

This is useful for creating dynamic reports where a chart should only reflect visible, filtered data. But it's a major headache when you're simply trying to tidy up your spreadsheet for presentation and find your charts are suddenly missing crucial information. Luckily, you can easily override this setting.

The Easiest Fix: Showing Hidden Data Through Chart Settings

The most direct way to solve this problem is through a hidden setting in the chart options. It only takes a few clicks to change how your chart interacts with hidden cells.

Step-by-Step Instructions

Select Your Chart: Click on the chart you want to modify. When you do, two new contextual tabs will appear in the Ribbon: Chart Design and Format.
Open the Select Data Window: Navigate to the Chart Design tab. In the “Data” group, click on the Select Data button. This will open the Select Data Source dialog box, which gives you control over what information is being plotted.
Find the Hidden Cells Setting: In the bottom-left corner of the Select Data Source window, you'll see a button labeled Hidden and Empty Cells. Click on it.
Check the Box: A new, smaller dialog box will pop up. At the bottom, you'll see the option that's causing all the trouble: Show data in hidden rows and columns. Simply check this box.
Confirm Your Changes: Click OK to close the Hidden and Empty Cell Settings box, and then click OK again to close the Select Data Source box.

That's it! Now, go back to your spreadsheet and hide a row or column that contains data from your chart. You’ll see that the chart remains complete, displaying the data from the hidden cells exactly as you wanted.

What About Filtered Data?

It's important to distinguish between hiding data and filtering data. You might notice that even after changing the "Hidden and Empty Cells" setting, your chart still displays "gaps" if you apply a filter to your data source.

Here’s the distinction:

Hiding rows/columns is a manual formatting choice. The setting we just changed tells Excel to ignore this manual choice when plotting data.
Filtering rows is a data analysis action. Excel treats this differently and assumes your goal is to analyze a specific subset of data. Therefore, the chart data will always reflect the filtered results, ignoring the "show hidden data" setting.

So, what can you do if you want a chart to always show the total dataset, even when the source data is filtered? The best solution involves a slightly more advanced technique.

A More Robust Solution: The Helper Sheet Method

If you build dashboards or reports where you regularly filter or hide data, relying on the chart setting alone can be messy. For a truly robust chart that never changes, regardless of what you do to the original data sheet, the "helper sheet" method is your best bet.

The concept is simple: you create a separate, static copy of your data on a new worksheet and build your chart from that copy. Since you'll never hide or filter a row or column on this helper sheet, the chart linked to it becomes completely independent of any formatting changes on your main sheet.

How to Set Up a Helper Sheet

Create a New Worksheet: Add a new sheet to your workbook and name it something intuitive, like ChartData or DashboardHelper. You might even want to hide this sheet later on to keep your workbook clean.
Link to Your Original Data: In cell A1 of your new helper sheet, type = and then navigate to your original data sheet and click on the first cell of your chart's data range (e.g., the top-left cell). The formula will look something like this:

Drag the Formula: Press Enter. Then, click on cell A1 in your helper sheet and drag the fill handle (the small square in the bottom-right corner of the cell) across and down to cover a range that is the same size as your original data source. This action quickly creates direct links to all the corresponding cells.
Build the Chart from the Helper Sheet: Now, create your chart as you normally would, but select the linked data ranges on your new helper sheet as the source.

With this setup, you can go back to your original data sheet and hide, filter, or sort to your heart's content. The chart, because it's tied to the untouched helper sheet data, will remain perfectly static and display the full dataset at all times. It's a bit more work upfront but provides a stable solution for interactive dashboards.

Useful Functions Related to Visible Data

While the goal of this article is to show hidden data, it’s worth knowing about functions designed to do the opposite - calculate things based on visible data only. These won't directly help you keep hidden data in a chart, but they're part of the same toolkit for creating dynamic reports.

The SUBTOTAL Function

One of the most powerful functions in this category is SUBTOTAL. It can perform various calculations (like SUM, AVERAGE, COUNT, etc.) but with a twist: you can tell it to include or ignore values in hidden rows.

The syntax is SUBTOTAL(function_num, ref1, [ref2], ...) where function_num is a number that specifies which calculation to use.

If you use a function_num from 1-11 (e.g., 9 for SUM), it calculates based on filtered data but includes manually hidden rows.
If you use a function_num from 101-111 (e.g., 109 for SUM), it calculates based on filtered data and also ignores manually hidden rows.

For example, to sum only the visible numbers in a filtered list in range A2:A100:

When you build a summary table that powers a chart, using SUBTOTAL can ensure your totals and your visual charts match perfectly when filters are applied.

Final Thoughts

Controlling how Excel charts handle hidden data is a simple fix that solves a common frustration. By adjusting the "Show data in hidden rows and columns" setting in the Select Data dialog, you gain direct control over your visuals. For more complex, interactive reports, using a linked helper sheet provides a rock-solid method to keep your charts stable, no matter how a user filters or organizes the source data.

While Excel offers powerful flexibility, preparing data, building charts, and managing settings across different sources for weekly or monthly reports takes a lot of time and manual effort. We created Graphed to automate that entire process. Instead of downloading CSVs and wrestling with chart settings, we enable you to connect data from platforms like Google Analytics, Shopify, and Salesforce in just a few clicks. From there, you just describe the dashboard you need in plain English, and it builds interactive, real-time reports for you in seconds, already wired to handle all your data correctly without any extra steps.