Handle Missing Data In A Line Chart

Category: Charts & Graphics | [Item URL]

When you create a line chart in Excel, missing data points (blank cells) won't be plotted, and the line will contain gaps. Excel provides two other ways of handling missing data:

• Treat blanks as zeros

• Interpolate the data by connecting the line between the nonmissing data points.

The figure below shows both options. In this example, interpolating the missing data seems the better choice in view of the data's time-based nature.

To set how Excel deals with missing data, select your chart and choose Tools, Options. In the Options dialog box, click the Chart tab and then select the appropriate option. Your choice will apply to all data series in the selected chart.

You can also represent data with the formula =NA() instead of leaving a cell blank. The chart will use interpolation for data cells that contain this formula, regardless of the setting in the Options dialog box.