Show chart data for empty cells

Excel 2016
If there are some gaps in source data for the chart, by default, Excel does not display them on the chart, and this looks odd. Excel offers a useful feature to display interpolated values instead of gaps in the chart. In addition, Excel allows you to process data with #N/A errors as empty cells.

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

Chart with empty cells in Excel for Microsoft 365

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 Chart Design tab, in the Data group, choose Select Data:
    Select Data in Excel 365
  • Right-click on the chart plot area and choose Select Data... in the popup menu:
    Select Data in popup menu Excel 365

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

Select Data Source in Excel 365

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

Hidden and Empty Cell Settings in Excel 365

Note: This option is only available if there is some empty data or data with #N/A errors in the source data. To display data with #N/A errors in the same way as the empty cells, select the Show #N/A as an empty cell checkbox.

Excel will calculate and display the interpolated data instead of the empty cells on your chart:

Show empty data in the chart in Excel 365

See also this tip in French: Afficher les données du graphique pour les cellules vides.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.