Handle Missing Data In A Line Chart
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.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks