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. You can, however, display the hidden data and change the way that the empty cells are displayed. Rather then displaying gaps, you can display empty cells as zero values, or you can span the gaps with a line.

  1. Click a chart in which you want to display hidden data and empty cells.

    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, and then do one of the following:
    • To define how empty cells are displayed in the chart, click Gaps, Zero, or Connect data points with line.
    • To display hidden cells in the chart, select the Show data in hidden rows and columns check box.