A Line chart may plot gaps in lines when the data range contains blank cells in Excel (884406)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
  • Microsoft Excel 2000

SYMPTOMS

When you create a Line chart in Microsoft Excel, the chart may contain a gap in a line.

CAUSE

This issue may occur when the data range for the Line chart contains a blank cell.

WORKAROUND

To work around this issue, use one of the following methods.

Method 1

Plot empty cells as interpolated. To do this, follow these steps:
  1. Click your Line chart to start it.
  2. On the Tools menu, click Options.
  3. On the Chart tab, click Interpolated, and then click OK.

Method 2

Use the NA function in the blank cell of the chart's data range. To do this, use one of the following methods:
  • In the blank cell, type #N/A.
  • In the formula bar for the blank cell, type =NA().
  • If the cell range for the Line chart uses a formula to obtain values from a different cell range, and if you do not want the Line chart to plot 0 (zero), type the following formula in the formula bar:

    =IF(SUM(range)=0,NA(),SUM(range))

    Note range is the cell range that is outside the cell range for the Line chart.
Modification Type:MajorLast Reviewed:9/6/2006
Keywords:kbchart kbtshoot kbprb KB884406 kbAudEndUser
©2004 Microsoft Corporation. All rights reserved.