Excel tip: Working with missing data for a line chart

When you create a polyline chart in Excel, if some cell data in the data region is missing, that is, the cell is blank, a notch appears in the line chart, which results in a line chart that is not attractive, or it is less intuitive to reflect the changing trend of the data.

As Figure 1 shows a line chart with missing data, you can see the gap on the polyline.

Figure 1

Excel provides two ways to work with missing data in a line chart:

is represented by a value of 0: The blank cell is treated as 0, and the row falls to 0 for 0-valued data points.

Interpolation replacement: Use the interpolation number of positions instead of blank cells, fill the space to connect the row.

Let's take a look at the effects of the two methods. As shown in Figure 2, the line chart is represented by a value of 0. As shown in Figure 3, the line chart is replaced with an interpolation value.

Figure 2

Figure 3

How did it happen? Follow these steps.

1. In Excel, select the polyline chart to process the missing data.

2. Select menu command Tools > Options to open the Options dialog box.

3. Click the Charts tab.

4. Select "Represent in 0" or "Replace with interpolation" in "empty cells are plotted as", as shown in Figure 4.

Figure 4

5. Click on the OK button to see the chart change. The above selection will affect all data series for the selected chart.

Another option is to enter the formula =na () in a blank cell. The chart then uses interpolation for the cell that contains the formula, regardless of what is selected in the Options dialog box