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 Bars > More Options.
A new window will appear where you can choose to format the error bars with the following options:
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:
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 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 Bars > More Options.
A new window will appear where you can choose to format the error bars with the following options:
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:
You can find more Excel tutorials here.