Chapter 19: Learning Advanced Charting
461
Tip
Often, the data labels aren’t positioned properly — for example, a label may be obscured by another data
point. 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. n
As you work with data labels, you discover that the Excel data labels feature leaves a bit to be
desired. For example, it would be nice to be able to specify an arbitrary range of text to be used for
the data labels. This capability would be particularly useful in XY charts in which you want to
identify each data point with a particular text item. Despite what must amount to thousands of
requests, Microsoft still hasn’t added this feature to Excel. You need to add data labels and then
manually edit each label.Handling missing data
Sometimes, data that you’re charting may be missing one or more data points. As shown in Figure
19.23, Excel offers three ways to handle the missing data:l (^) Gaps: Missing data is simply ignored, and the data series will have a gap. This is the
default.
l (^) Zero: Missing data is treated as zero.
l 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 button. 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 different option for different
series in the same chart.
Tip
Normally, a chart doesn’t display data that’s in a hidden row or column. You can use the Hidden and Empty
Cell Settings dialog box to force a chart to use hidden data, though. n
Adding error bars
Some chart types support error bars. Error bars often are used to indicate “plus or minus” informa-
tion that reflects uncertainty in the data. Error bars are appropriate for area, bar, column, line, and
XY charts only.