Excel: Data Represented in Chart "Broken" If Cells Left Blank

 Last reviewed: April 27, 1995
Article ID: Q43927
The information in this article applies to:
  • Microsoft Excel for Windows, versions 3.0, 4.0, 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0, 5.0a

SUMMARY

In Microsoft Excel, if the reference for a point on a chart is blank (the cell does not contain any data), that point may not be plotted on the chart. If the series formula for the data line contains information for the points prior to and following a given point, the line will be drawn up to the last point before the point that has no value and will resume at the first point after it that does refer to a cell with data. The result will be a broken representation of the data.

MORE INFORMATION

To control how Microsoft Excel represents the missing data (empty cells), follow the appropriate procedure below.

Microsoft Excel 5.0

  1. Activate the chart.

  2. From the Tools menu, choose Options, and select the Chart tab.

  3. Under Active chart, in the Empty Cells Plotted As box, select the desired option.

Note that if you are using an area chart, these options may be ignored. For additional information, please see the following article(s) in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q111957
   TITLE     : XL5: Area Charts Ignore "Empty Cells Plotted As" Setting

Microsoft Excel versions 4.0 and earlier

To create a line that continues through a point that has no information, assign the point the value #N/A. Microsoft Excel will plot a straight line between the point before and the point after, but will not include a data point for the missing value.

Example:

The following table on the left will plot a broken line chart (shown on the right): 

                                 +           *
         Table 1                 |          /
       ___________               |         /
       | X  |  Y |               +        *
       |____|____|               |
       | 0  |  0 |               |
       | 1  |  1 |               +
       | 2  |    |               |
       | 3  |  3 |               |
       | 4  |  4 |               +  *
       |____|____|               | /
                                 |/
                                 *__+__+__+__+__
                                     Graph 1
The following table will plot the continuous line chart shown on the right: 

                                 +           *
         Table 2                 |          /
       ___________               |         /
       | X  |  Y |               +        *
       |____|____|               |       /
       | 0  |  0 |               |      /
       | 1  |  1 |               +     /
       | 2  |#N/A|               |    /
       | 3  |  3 |               |   /
       | 4  |  4 |               +  *
       |____|____|               | /
                                 |/
                                 *__+__+__+__+__
                                     Graph 2

KBCategory: kbother
KBSubcategory:

Additional reference words: 2.20 3.00 3.00 4.00 5.00 5.00c drop zero plot
lines descend line

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 27, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.