How to Add Error Bars to Charts in Excel


Often you may be interested in adding error bars to charts in Excel to capture uncertainty around measurements or calculated values. Fortunately this is easy to do using built-in Excel graphing functions.

This tutorial explains how to add error bars to both bar charts and line charts.

Error Bars in Bar Charts

Suppose we have the following dataset in Excel:

To create a bar chart for this dataset, we can first highlight the data, then click on the Insert tab along the top ribbon. Within the Charts group, click on the first chart in the category titled Insert column or bar chart:

This automatically produces the following bar chart:

To add error bars to each bar, click anywhere on the chart. Then click the Plus (+) sign that appears in the top right corner. Then click Error BarsMore Options.

Error bars on bar chart in Excel

A new window will appear where you can choose to format the error bars with the following options:

Error bar options in Excel

The most common “Error Amount” to use is the standard error, which is calculated as:

Standard error = s / √n

where:

  • s: sample standard deviation
  • n: sample size

In this example, we could calculate the standard error by using the following formula:

Standard error calculation in Excel

The standard error turns out to be 1.78. This is the width of the error bar that extends in both directions from the point estimates on the graph. For example, the value of the first bar in the chart is 4, thus it has an error bar that extends from:

  • Lower end: 4 – 178 = 2.22
  • Upper end: 4 + 1.78 = 5.78

Error bars in bar chart in Excel

Error Bars in Line Charts

Suppose we have the following dataset in Excel:

To create a line chart for this dataset, we can first highlight the data, then click on the Insert tab along the top ribbon. Within the Charts group, click on the first chart in the category titled Line chart.  This will produce the following line chart:

To add error bars to each bar, click anywhere on the chart. Then click the Plus (+) sign that appears in the top right corner. Then click Error BarsMore Options.

Error bars on line chart in Excel

A new window will appear where you can choose to format the error bars with the following options:

Error bar options in Excel

You can choose to calculate the “Error Amount” using a fixed value, a percentage, a number of standard deviations, or the standard error. 

Once you select an error amount, the following error bars will be displayed on the line chart:

Error bars in Excel

You can find more Excel tutorials here.

Leave a Reply

Your email address will not be published.