Excel 2019 Bible

Chapter 21: Using Advanced Charting Techniques

21

FIGURE 21.23

Data labels linked to text in an arbitrary range

Often, the data labels aren’t positioned properly. For example, a label may be obscured by another data point or
another label. If you select an individual data label, you can drag the label to a better location. To select an individual
data label, click once to select them all and then click the single data label.

Handling missing data

Sometimes, data that you’re charting may be missing one or more data points. As shown in

Figure 21.24, 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.

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 charts only.

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 but-

ton. Excel displays its Hidden and Empty Cell Settings dialog box. Make your choice in the

dialog box. The option that you choose applies to the entire chart, and you can’t set a dif-

ferent option for different series in the same chart.