Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. XL2000: Area Charts Ignore "Empty Cells Plotted As" Setting

XL2000: Area Charts Ignore "Empty Cells Plotted As" Setting

View products that this article applies to.

This article was previously published under Q214293

↑ Back to the top

Symptoms

In Microsoft Excel, an area series in a chart ignores the Plot empty cells as setting on the Chart tab of the Options dialog box.

↑ Back to the top

Cause

The options under the Plot empty cells as setting are designed to be used with line charts, x-y (scatter) charts, 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.

↑ Back to the top

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:
  1. Type the following data in a new worksheet:
    A1: 1
    A2: 2
    A3:
    A4:
    A5: 5
  2. 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.
  3. 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).
  4. In cell A3, type the following formula:
    =A2+$B$1
  5. 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.

↑ Back to the top

More information

The Plot empty cells as setting on the Chart tab in the Options dialog box allows you to determine how Excel handles empty cells that are contained within a range of data. For example, if you create a line chart using the following data
A1: 1
A2: 2
A3: <empty>
A4: <empty>
A5: 5
the empty cells are plotted according to the option that you specify.

The following table explains how Excel plots the example range A1:A5 for each option that you can select. 
   This setting   Has this result
   --------------------------------------------------------------------
   Not plotted    The line exists from 1 to 2, and a single point exists
                  at 5. There is a gap in the line where the values
                  from A3 and A4 would normally appear.

   Zero           The line proceeds from 1 to 2, and then to zero (A3 and
                  A4 are interpreted as zero). The line then proceeds
                  to 5.

   Interpolated   The line proceeds from 1 to 2, "bridges" the gap at 3
                  and 4, and then proceeds to 5.

↑ Back to the top

Keywords: KB214293, kbprb, kbnofix

↑ Back to the top

Article Info
Article ID : 214293
Revision : 4
Created on : 9/27/2003
Published on : 9/27/2003
Exists online : False
Views : 290