Show chart data in hidden cells

Excel 2016
Excel allows you to create charts and diagrams from different data sets, even those that contain hidden or empty cells. However, sometimes you may see unexpected results. For example, if there are hidden cells in the chart data, by default, Excel ignores such data and doesn't display the corresponding chart elements. You can easily force Excel to handle hidden data.

See also how Excel displays the empty data on the chart.

For example, there is some hidden data in the table:

Chart with hidden data in Excel 365

If you want to see hidden data 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, check the Show data in hidden rows and columns checkbox:

Hidden and Empty Cell Settings in Excel 365

Excel will show all hidden data on your chart:

Show hidden data in the chart in Excel 365

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

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.