Area Charts Ignore "Empty Cells Plotted As" Setting (111957)
The information in this article applies to:
- Microsoft Excel 97 for Windows
This article was previously published under Q111957 SYMPTOMS
In Microsoft Excel, an area series in a chart ignores the "Empty cells
plotted as" (or "Plot empty cells as" in Microsoft Excel 97) setting on
the Chart tab of the Options dialog box.
CAUSE
The options under the "Empty cells plotted as" setting (or "Plot empty
cells as" setting in Microsoft Excel 97) are designed to be used with
line, x-y (scatter), and radar charts. Chart types such as pie, doughnut,
column, and bar do not usually benefit from these settings. Also, even
though area charts are very similar to line charts, changing these settings
has no effect on the appearance of an area series in a chart.
WORKAROUND
To work around this behavior, you can interpolate missing data points
and format the area chart; the result is similar to the way the
Interpolated option works with other charts.
To see an example that demonstrates the workaround, follow these steps:
- Type the following data in a new worksheet:
A1: 1
A2: 2
A3:
A4:
A5: 5
- Create an area chart based on the data in step 1.
Your area chart drops off to the x-axis between the third and
fourth data points.
- In cell B1, type the following formula:
=($A$5-$A$2)/(ROW($A$5)-ROW($A$2))
This formula calculates the "interpolation step value" between the two
known values that lie at the ends of the missing value (2 and 5).
- In cell A3, type the following formula:
- Use the fill handle to fill the formula down to cell A4.
The resulting filled data is:
A1: 1
A2: 2
A3: 3
A4: 4
A5: 5
Your area chart does not drop down to zero for the missing data points.
Modification Type: | Minor | Last Reviewed: | 8/15/2003 |
---|
Keywords: | kbnofix kbprb KB111957 |
---|
|