Excel Chart Show Hidden Data? How to Control It

Yes. In Excel for Microsoft 365 on Windows, select the chart, choose Chart Design > Select Data > Hidden and Empty Cells, and select Show data in hidden rows and columns. Clear that checkbox to keep hidden rows and columns out of the chart. The choice affects what the chart displays; it does not unhide worksheet data.

How Excel charts handle hidden data

By default, a standard Excel chart does not plot values in worksheet rows or columns that are hidden. This can be useful when you hide detail that is not relevant to a presentation, but it can also make a chart look incomplete if you hid data only to tidy the worksheet. The chart setting lets you decide whether hidden source data should still be included.

For example, suppose a line chart uses monthly sales in A1:B13. If you hide the row for March, the chart normally omits that month. Turning on Show data in hidden rows and columns allows the chart to plot March even though its worksheet row remains hidden. The chart may therefore contain information that is not visible in the current sheet view.

This setting is separate from hiding a chart series, filtering chart categories, or hiding the chart itself. It controls whether the chart reads values from hidden source rows and columns. It does not change the source values, formulas, or worksheet visibility.

Show data in hidden rows and columns

To include hidden worksheet data in a chart on Excel for Microsoft 365 for Windows:

Select the chart so the chart tabs appear on the ribbon.
Choose Chart Design > Select Data.
In the Select Data Source dialog, choose Hidden and Empty Cells.
Select Show data in hidden rows and columns, then choose OK and close the dialog.

The chart should now include values from hidden rows or columns within its source range. To reverse the behavior, follow the same path and clear the checkbox. If the setting is unavailable or the chart behaves differently, check whether you are working with a PivotChart or a chart in Excel for the web; those cases can have different controls.

On Excel for Mac, select the chart and look for the corresponding options under the Chart Design tab and Select Data dialog. Ribbon placement and dialog appearance can vary slightly by release. Excel for the web may offer fewer chart-editing controls than the desktop app, so open the workbook in Excel for Windows or Mac if you cannot find the hidden-data option.

Keep hidden data out of a chart

If a chart should reflect only what readers can currently see on the worksheet, make sure Show data in hidden rows and columns is cleared. This is the usual default for standard charts. Hiding a row can then temporarily remove its plotted values without deleting or editing the source data.

That behavior can be helpful for preparing a focused view. For instance, a worksheet might contain actual results and several forecast scenarios. You could hide the rows for scenarios that are not being discussed, and the chart will omit them when the option is off. Remember that hiding rows is not a security measure: the data remains in the workbook and may be revealed by someone with access to it.

Hidden rows, hidden columns, and filtered data

Hidden rows and columns are not the same thing as rows excluded by a filter. The chart option is specifically named for hidden rows and columns. A worksheet filter changes which records are displayed, and charts often respond to the filtered view by excluding filtered-out records. Do not assume that the hidden-data checkbox will override every filter or chart-specific filter.

For example, if a table lists sales by region and a filter displays only the West region, a chart based on that table may show only the displayed records. If you manually hide a worksheet row instead, the chart’s hidden-row setting determines whether its value is plotted. When chart results matter, test the specific filter and chart together rather than treating all invisible records as equivalent.

Also distinguish hiding a worksheet column from removing a field from the chart’s source range. A hidden column can still be included when the option is enabled, but a column outside the selected source range cannot contribute data. Use Chart Design > Select Data to inspect the chart’s source range and series if expected values are missing.

Blank cells and formulas are a separate issue

The Hidden and Empty Cells dialog also contains choices for how a chart handles empty cells. Those choices are different from the checkbox for hidden rows and columns. For many chart types, Excel can display empty cells as gaps, treat them as zero, or connect data points across the empty cells. The available behavior depends on the chart type.

A genuinely blank cell is not necessarily treated like a cell containing a formula that returns an empty text string. If a line chart has unexpected breaks or points, inspect the source cells and the empty-cell setting. A formula result that looks blank can still be interpreted differently from a cell that has no value at all, so test a small example if the distinction matters to the presentation.

For a line chart of daily readings, choosing gaps can make missing measurements visible rather than implying a continuous trend. Connecting points may be reasonable when the goal is to show the overall pattern, but it can suggest continuity where no measurement exists. Choose the treatment that accurately represents the data, not merely the one that makes the chart look smooth.

What changes when you hide source data?

Hiding a row or column changes the worksheet view, not the underlying values. With the chart option off, a standard chart generally stops plotting those hidden source values; with it on, it can continue to plot them. In either case, formulas elsewhere in the workbook still refer to the source cells unless those formulas themselves depend on visibility.

This matters when charts are copied into reports or viewed by other people. A chart that includes hidden records may seem to disagree with the visible table. Conversely, a chart that omits hidden records may appear to have gaps or fewer categories than expected. Add a note or make the chart’s scope clear when the distinction could affect interpretation.

Use hidden rows for temporary worksheet organization, not to remove data from a workbook.
Check the chart setting before sharing a workbook if hidden records could change the message.
Use filters or a clearly defined chart source range when you need a repeatable selection of records.

Common problems and how to troubleshoot them

If a chart still does not show a value after you enable the setting, check the source range first. Select the chart and open Chart Design > Select Data. Confirm that the missing cell belongs to a plotted series or category and that the range has not stopped above or beside it. A chart cannot display data that is outside its source range.

Then check whether the cell is truly hidden, filtered out, blank, or part of a PivotTable. These situations can look similar on screen but are controlled differently. For a PivotChart, adjust the associated PivotTable fields, filters, or slicers rather than assuming the standard chart setting will control every displayed item.

Other useful checks include:

Confirm that the relevant series is selected and has not been removed in Select Data.
Check whether a worksheet filter or chart filter is excluding the category.
Inspect formulas for errors or empty-string results that may affect plotting.
Verify the chart type and the empty-cell treatment if the issue is a gap in a line.
Reopen the Hidden and Empty Cells dialog to confirm the checkbox state for that chart.

If the chart is linked to an Excel table, adding or filtering table rows can affect its plotted categories in ways that differ from manually hiding ordinary worksheet ranges. When a workbook is used as a recurring report, document whether the chart should represent all records or only visible records, then test a filter change before relying on the result.

Standard charts and PivotCharts

The hidden-row and hidden-column option is intended for regular charts whose data comes from worksheet ranges. PivotCharts are tied to PivotTable data and have their own field buttons, filters, and refresh behavior. A PivotTable can summarize or exclude records based on its fields, so hiding a worksheet row is not a reliable substitute for changing the PivotTable’s selection.

For an ordinary chart, inspect Select Data and Hidden and Empty Cells. For a PivotChart, inspect the associated PivotTable, its filters, and any slicers. If you need a chart that responds predictably to a particular set of visible records, use a source range or table designed for that purpose and verify the result after changing the selection.

Frequently Asked Questions

Why does my Excel chart not show hidden rows?

Standard charts normally omit data in hidden rows and columns unless Show data in hidden rows and columns is selected. Open Chart Design > Select Data > Hidden and Empty Cells and turn on the checkbox. Also confirm that the missing cells are within the chart’s source range.

Can I show hidden data in a chart without unhiding the rows?

Yes. Enable Show data in hidden rows and columns in the chart’s Hidden and Empty Cells dialog. The worksheet rows remain hidden while the chart can plot their values.

Does filtering a table work the same as hiding rows?

No. A worksheet filter and manually hidden rows are different ways of changing what is visible, and a chart may respond differently to each. Test the chart with the filter you plan to use; the hidden-data checkbox should not be treated as a universal control for filtered records.

Does the setting apply to every chart in the workbook?

Do not assume it does. Check the setting for the chart in question. If a workbook contains several charts, inspect each one whose treatment of hidden data matters.

Will hiding rows protect the data from other people?

No. Hidden data remains in the worksheet and can be revealed. Hiding is a display choice, not a confidentiality or access-control method.

A practical way to choose

Decide what the chart is meant to communicate. If it should summarize the full dataset even while parts of the worksheet are hidden, turn on the option and label the chart so viewers understand its scope. If the chart should match only the visible worksheet view, leave the option off and check how filters behave. If the chart is a PivotChart, manage the underlying PivotTable selection instead.

Before sharing, compare the plotted categories with the intended source data and test one hidden row or column. That quick check helps catch the most common surprise: a chart quietly including information that a reader cannot see in the worksheet, or leaving out values that the chart creator expected it to show.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.