Microsoft KB Archive/114519

Microsoft Knowledge Base

XL5: Error Bars Do Not Display If Blanks in Data Range

Last reviewed: September 12, 1996
Article ID: Q114519

The information in this article applies to:

• Microsoft Excel for Windows, version 5.0
• Microsoft Excel for the Macintosh, version 5.0

SYMPTOMS

In Microsoft Excel, if you add error bars to a data series on a chart, and you use the Custom option in the Error Bars dialog box, the following behavior occurs:

• If the data on the worksheet that the chart series is created from contains a blank cell or a cell with the value #N/A, the error bar for this data point does not appear, and the error bars for each of the following data points in the series either do not appear on the chart or appear incorrectly.
• If the Custom + or - box contains a worksheet range, and a cell in the range is blank, then the error bar for this data point and the error bars for each of the following data points do not appear on the chart.
• If the Custom + or - box contains a worksheet range, and a cell in the range contains the value #N/A, then the error bar for this data point does not appear on the chart, and the error bars for each of the following data points are incorrect.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

WORKAROUNDS

Workaround 1

NOTE: This behavior depends on the option selected under Empty Cells Plotted As on the Chart tab of the Options dialog box. If the Not Plotted (leave gaps) option is selected, the error bars for each of the data points following the data point created from the blank or #N/A cell value do not appear. If the Zero option is selected, the error bars for each of the data points following this cell data point appear incorrectly.

To avoid this behavior when the worksheet that the chart series is created from contains a blank cell, do either of the following:

• Instead of using a blank cell, enter a 0 in the cell for which you do not want a data point to appear on the chart. -or-
• Use any of the other error amount options in the Error Bars dialog box to display error bars on the chart.

Workaround 2

To prevent an error bar from displaying for a data point on your chart, enter 0 as the error amount value for that data point using either of the following methods:

• In the Custom + or - box in the Error Bars dialog box for the data series, enter the error amount values for the data points as an array, for example {1,2,0,4,2}, using a 0 for the data point for which you don't want to display an error bar. -or-
• In the Custom + or - box in the Error Bars dialog box for the data series, enter the worksheet range as the error amount values, where the cell for the data point for which you don't want to display an error bar contains the value 0.

MORE INFORMATION

If the error value is a fixed value or can be expressed as a percentage, then you can enter the value in the Error Bars dialog box in the Fixed Value box or the Percentage box. When you enter a value in the Fixed Value box or the Percentage box, an error bar of this value displays for all of the data points in the selected series. You can add an error bar to a data series on an area, bar, column, line, or xy (scatter) chart.

REFERENCES

"Microsoft Excel User's Guide," version 5.0, pages 322-323, 354-357

For more information about Adding Error Bars To A Data Series, choose the Search button in Help and type:

    error bars

KBCategory: kbtool KBSubcategory: Additional reference words: 5.00 disappear show 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: September 12, 1996 ©1997 Microsoft Corporation. All rights reserved. Legal Notices.