Microsoft Office Tutorials and References
In Depth Information
Tip 89: Handling Missing Data in a Chart
Handling Missing Data in a Chart
Sometimes, data that you’re charting may be missing one or more data points. As shown in Figure 89-1,
Excel offers three ways to handle the missing data:
➤ Gaps: Missing data is simply ignored, and the data series will have a gap. This is the default
way missing data is handled in a chart.
➤ Zero: Missing data is treated as zero.
➤ Connect Data Points with Line: Missing data is interpolated, calculated by using data on
either side of the missing point(s). This option is available for line charts, area charts, and XY
Figure 89-1: Three options for dealing with missing data.
To specify how to deal with missing data for a chart, choose Chart Tools➜Design➜Data➜Select
Data. In the Select Data Source dialog box, click the Hidden and Empty Cells button. The Hidden and
Empty Cell Settings dialog box appears, as shown in Figure 89-2. Make your choice in the dialog box.