Display hidden worksheet data and empty cells in a chart

By default, data that is hidden in rows and columns in the worksheet is not displayed in a chart, and empty cells are displayed as gaps. For most chart types, you can display the hidden data in a chart.

For line, scatter, and radar chart types, you can also change the way that the empty cells are displayed in the chart. Instead of displaying empty cells as gaps, you can display empty cells as zero values, or you can span the gaps with a line.

Line charts showing different ways to plot empty cells

What do you want to do?

Display hidden data in a chart

Change the way that empty cells are displayed in a chart

Display hidden data in a chart

  1. Click the chart in which you want to display hidden data.

    Tip: This displays the Chart Tools, adding the Design, Layout, and Format tabs.

  2. On the Design tab, in the Data group, click Select Data.

    Excel Ribbon Image

  3. Click Hidden and Empty Cells.

  4. In the Hidden and Empty Cell Settings dialog box, select the Show data in hidden rows and columns check box.

Top of Page

Change the way that empty cells are displayed in a chart

  1. Click the line, scatter, or radar chart for which you want to change the way that empty cells are displayed.

    Tip: This displays the Chart Tools, adding the Design, Layout, and Format tabs.

  2. On the Design tab, in the Data group, click Select Data.

    Excel Ribbon Image

  3. Click Hidden and Empty Cells.

  4. In the Hidden and Empty Cell Settings dialog box, click Gaps, Zero, or Connect data points with line.

    Note: On a scatter chart that displays only markers (without connecting lines), you can display empty cells as gaps or zero only — you cannot connect the data points with a line.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×