Chart & Visualization Issues

How to Show Chart Data from Hidden Rows and Columns in Excel

Maira Mehtab Oct 21, 2026 869 views

Question details

Configure an Excel chart to maintain and display its source data points even when the source rows or columns are hidden in the worksheet.

How to Show Excel Chart Data from Hidden Rows and Columns

Product: Microsoft Excel
Device & OS: not provided
Scenario: A user needs to hide specific rows or columns in a shared worksheet to streamline the view, but wants the linked chart to continue displaying the hidden data.
Observed behavior: By default, hiding source rows or columns causes Excel to remove that data from the chart. Additionally, the standard 'Hidden and Empty Cells' configuration option is sometimes disabled or greyed out in shared workbooks.

Before you start

Ensure your workbook is not locked or protected in a way that restricts chart formatting, as this is the most common reason configuration options become disabled.

Solution 1Recommended

Enable the Hidden Data Option in Chart Settings

This is the standard and most direct method to force an Excel chart to display data from hidden rows and columns.

Excel charts are configured by default to ignore data in hidden cells. You can change this behavior for individual charts through the Select Data Source dialog.

Access chart data settings

Right-click anywhere on the chart area and select 'Select Data' from the context menu.

Open hidden cell options

In the Select Data Source dialog box, click on the 'Hidden and Empty Cells' button located in the bottom left corner.

Enable hidden data display

Check the box next to 'Show data in hidden rows and columns', then click OK on both dialog boxes to apply the changes.

Enable the Hidden Data Option in Chart Settings

Immediate Updates: Once enabled, you can freely hide or unhide rows and columns without the chart losing any of its data points.

Solution 2

Use a Linked Data Range on a Separate Worksheet

Use this workaround if the 'Hidden and Empty Cells' button is greyed out and you cannot disrupt a shared workbook's workflow.

Manage Charts Seamlessly with WPS Spreadsheet

WPS Spreadsheet provides a highly intuitive interface for data visualization. You can easily modify chart settings to include hidden data points without dealing with legacy workbook sharing conflicts.

1. Open the workbook: Launch WPS Spreadsheet and open your existing spreadsheet file.
2. Select the chart data: Right-click the chart you wish to modify and choose 'Select Data' from the dropdown menu.
3. Access hidden cell settings: In the dialog box, click on the 'Hidden and Empty Cells' button.
4. Show the hidden data: Tick the option for 'Show data in hidden rows and columns' and click 'OK'.

Fully compatible with Microsoft Excel (.xlsx) files and existing chart configurationsIntuitive chart data selection tools for handling hidden and empty cellsFree and lightweight office suite with fast loading timesSeamless collaboration features for shared team workbooks

Frequently Asked Questions

Why is the Hidden and Empty Cells button disabled in my Excel chart?

This button can be greyed out if the workbook is protected, if it relies on legacy shared workbook features in older versions of Excel, or if the chart is a PivotChart, which strictly follows the visible layout of its associated PivotTable.

Will a chart still update if its source data is on a hidden sheet?

Yes. Excel charts can easily pull and plot data from entirely hidden worksheets. Hiding an entire sheet does not trigger the same data exclusion rules as hiding individual rows or columns.

How do I handle empty cells in my Excel chart?

In the same 'Hidden and Empty Cells' menu, you can choose how empty cells are plotted. You can set them to show as gaps, display as zero values, or connect the data points with a line to span the empty space.

Excel & Data Problems