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

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

Activate the chart.

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

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:

Q111957 : 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

Additional query words: 2.20 5.00c drop zero plot lines descend line

Keywords :
Version :
Platform :
Issue type :