Error Bars in Excel Charts.

Error bars are a useful and flexible tool that help add detail to an Excel chart. A typical use for error bars is to show variability in the measures which are plotted in the chart. There are other ways to use error bars to embellish Excel charts, as listed at the end of this article.

Adding Error Bars

Error bars are easy to add to a chart series. Double click on the series, or select the series and press CTRL+1, to open the Format Series dialog. Most chart types allow Y error bars, and XY Scatter types allow X error bars as well. The Error Bar dialogs, shown below, are not at all complicated.

These dialogs allow you to display no error bars, positive error bars, negative error bars, and error bars in both directions. There are several ways to enter values: fixed values, a percentage of the point's value, a number of standard deviations, the standard error of the plotted points, and custom values. As a means of explaining these options, each will be shown using the following simple data set, which results in a basic series of points. You should make up your own data set to practice making error bars.

This is a simple XY Scatter chart of the sample data set, without error bars.

This shows our sample chart with positive and negative X and Y error bars, with a fixed value of 0.75.

This shows our chart with positive and negative X and Y error bars, using a percentage of 12%. As the data values increase from the bottom left to the top right, the length of the error bars, 12% of the value, also increases.

Here is our chart with error bars of one standard deviation. This chart differs from the rest, because the error bars do not start at the data points and move away from them. For each data point, the X error bars are centered on the average of the X values, and they extend left and right by one standard deviation of X (5.13 ± 2.43, or 2.70 to 7.56); likewise the Y error bars are centered on the average of the Y values (5.01), and extend up and down by one standard deviation of Y (5.01 ± 2.31, or 2.71 to 7.32). For clarity, the chart is redrawn with only the Y error bars.

This chart shows the standard error of the plotted points as the error bar value for each point. Excel doesn't document how this is calculated, but you can consult a statistics text.

If you have a more complicated set of values for your error bars, you enter them using the Custom (+) and Custom (-) boxes. You can click in the boxes to select a range of values, as shown, for the case in which each point has a unique value. You can define a dynamic range that contains error bar values, akin to defining dynamic ranges for the X and Y values of a chart series in a dynamic chart. You can select a single cell, to use a calculated value for all error bars. You can type different fixed values for positive and negative error bars in these boxes. You can even enter an array of values, separated by commas.

This chart shows custom error bars applied by selecting a range in the dialog above. The error bars are tighter in the middle of this chart, and expand as distance from the center increases, especially toward the upper right of the chart.

Formatting Error Bars

Through the Format Error Bars dialog, you can adjust the color, line style and weight, and end cap appearance of your error bars. This dialog also provides access to the pages which enable you to define the values for the error bars. If you click once to select the error bars before opening this dialog, both X and Y error bars will receive the same format. If you click a second time on just the X or Y bars, just that set will be selected and formatted.

The error bars in this chart have been turned blue, and the style is line only, without the crosses at the ends.

This chart shows how the X and Y error bars can be formatted independently, with different line colors, line styles, and error bar end styles.

Error Bar Tricks

Drop Lines

You can make drop lines from each point to the axes using negative error bars with a percentage value of 100%. If the axes do not cross at zero, you can use custom ranges with formulas that subtract the axis position from the point values.

Horizontal or Vertical Lines

Error bars provide one mechanism to add Horizontal or Vertical Lines to a column or line chart.

Gridlines

Gridlines are simply a set of vertical or horizontal lines. A series of error bars can be used as above to generate a custom set of Gridlines in a chart.

Box and Whisker Charts

Error bars comprise the whiskers in Box and Whisker Charts, statistical charts used to illustrate and compare the spread in sets of data.

Step Charts

You can use error bars to construct a step chart, using subtraction formulas to compute the error bar lengths. The point markers have been retained here to illustrate the construction of the step chart, but they would be removed in the final chart. This technique is documented in more detail by Tushar Mehta and is one of several methods suggested by Andy Pope.