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

• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SYMPTOMS

In Microsoft Excel, an area series in a chart will ignore the "Empty Cells Plotted As" (or "Plot empty cells as" in Microsoft Excel 97) setting in the Chart tab of the Options dialog box.

CAUSE

The options under the "Empty Cells Plotted As" (or "Plot empty cells as" in Microsoft Excel 97) setting 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. And, 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 problem, you can interpolate missing data points and format the area chart (the result will be similar to the way the Interpolated option works with other charts).

To see an example that demonstrates the workaround, follow these steps:

1. Enter 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 will drop off to the x-axis between the third and fourth data points.

3. In cell B1, enter 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, enter the following formula:

         =A2+$B$1
   

5. Use the fill handle to fill the formula down to cell A4.

   A1: 1
   A2: 2
   A3: 3
   A4: 4
   A5: 5

STATUS

This behavior is by design of Microsoft Excel.

MORE INFORMATION

The "Empty Cells Plotted As" ("Plot empty cells as" in Microsoft Excel 97) setting on the Chart tab in the Options dialog box allows you to determine how Microsoft Excel will handle 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 following table explains how Microsoft Excel would plot the range A1:A5 above if you selected the various options under "Empty Cells Plotted As" ("Plot empty cells as" in Microsoft Excel 97).

   This setting   Would have 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, 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.