Handling Missing Data or Charting Hidden Data

Sometimes, data that you're charting may be missing one or more data points. Also, by default, Excel does not include data in hidden rows and columns within the data range you're charting. Excel offers several options for handling the missing data and allows you to chart hidden data in a range. Just follow these steps:

1. Click anywhere on the chart and click the Design tab to display the chart design tools on the Ribbon.
2. Click the Select Data button. Excel displays the Select Data Source dialog box.
3. Click the Hidden and Empty Cells button. Excel displays the Hidden and Empty Cell Settings dialog box.
4. Select the option that corresponds to how you want to handle the missing data:
   
   
   
   
   
   Gaps:
   Excel ignores missing data, and the data series leaves a gap for each missing data point. This is the default setting.
   
   Zero:
   Excel treats missing data as zero.
   
   Connect Data Points with Line:
   Excel calculates missing data by using data on either side of the missing point(s). This option is available only for line charts and some X Y scatter chart subtypes.
   
   
5. If you want to chart hidden data within the chart's source data range, select the Show Data in Hidden Rows and Columns check box.
6. Click OK to exit the Hidden and Empty Cell Settings dialog box, and click OK again to exit the Select Data Source dialog box.

The options that you set apply to the entire active chart; you can't set a different option for different series in the same chart.