Microsoft Office Tutorials and References
In Depth Information
Handling Missing Data
Another way to handle data in different worksheets is to create a summary range in a
single worksheet. This summary range consists of simple formulas that refer to the data
in other sheets. Then you can create a chart from the summary range.
Handling Missing Data
Sometimes, data that you use in a chart may lack one or more data points. Excel offers the following
ways to handle the missing data:
➤ Ignore the missing data. Plotted data series will have a gap.
➤ Treat the missing data as zero values.
➤ Interpolate the missing data (for line and scatter charts only).
For some reason, Excel makes these options rather difficult to locate. The Ribbon doesn’t contain
these options, and you don’t specify these options in the Format Data Series dialog box. Rather, you
must follow these steps:
1. Select your chart.
2. Choose Chart Tools➜Design➜Data➜Select Data to display the Select Data Source
dialog box.
3. In the Select Data Source dialog box, click the Hidden and Empty Cells button. Excel displays
the dialog box shown in Figure 6-14.
4. Choose the appropriate option and click OK.
Figure 6-14: Use the Hidden and Empty Cell Settings dialog box to specify how to handle missing data.
The setting that you choose applies only to the active chart and applies to all series in the chart. In
other words, you can’t specify a different missing data option for different series in the same chart. In
addition, not all chart types support all missing data options.
Figure 6-15 shows three charts that depict the three missing data options. The chart shows
temperature readings at one-hour intervals, and four data points are missing. The “correct” missing data
option depends on the message that you want to convey. In the top chart, the missing data is
obvious because of the gaps in the line. In the middle chart, the missing data is shown as zero — which is
Search JabSto ::

Custom Search