Show chart data for empty cells

Excel 2016
When you have some gaps in your chart data, Excel doesn't show any data for these gaps and your chart looks odd. Instead you can show interpolated values instead of gaps in the chart.

For example, there are some gaps or empty cells in the chart data table:

Chart with empty cells in Excel 2016

If you want to see average values in the chart, do the following:

   1.   Select the chart data by doing one of the following:

  • On the Design tab, in the Data group, choose Select Data:
    Data in Excel 2016
  • Right-click in the chart area and choose Select Data... in the popup menu:
    popup in Excel 2016

   2.   On the Select Data Source dialog box, click the Hidden and Empty Cells button:

Select Data Source in Excel 2016

   3.   On the Hidden and Empty Cell Settings dialog box, in the Show empty cell as radio button groups, select Connect data points with line:

Hidden and Empty Cell Settings in Excel 2016

Excel will "show" all empty data on your chart:

Show empty data in the chart in Excel 2016

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.