How to Add Error Bars in Excel Using Standard Deviation

Error bars in Excel show how much the data behind each bar varies. The most useful kind uses each group's own standard deviation, so a team with steady scores gets short bars and an erratic team gets long ones. You calculate the means and standard deviations first, chart the means, then point the error bars at your standard deviation cells. This guide is for students, researchers and analysts who need a chart that shows spread, not just averages.

What error bars show#

An error bar is a thin line drawn above and below a data point. Its length shows variability or uncertainty around that value. On a chart of averages, there are two common choices:

Use SD bars to show how varied the data is. Use SE bars, or confidence intervals, when you're comparing averages. Whichever you pick, label it: a reader can't tell SD bars from SE bars by looking.

Step 1: Calculate the mean and standard deviation for each group#

Put the raw data in columns, one group per column. Here are six test scores for three teams:

Below the data, add a summary. In B9, type =AVERAGE(B2:B7). In B10, type =STDEV.S(B2:B7). Fill both across to column D:

STDEV.S is the sample standard deviation, the right choice when your data is a sample of a bigger group. The guide to STDEV.S vs STDEV.P explains when to use each, and the standard deviation guide shows how it's calculated. The AVERAGE function guide covers the mean.

Before you chart, check the median too. Beta's median is 25.5, close to its mean of 25. When the mean and median are far apart, the data is skewed, and symmetric SD bars can mislead.

Step 2: Chart the averages#

Error bars go on a chart of the summary values, not the raw data:

1. Select the team names and the means: B1:D1, then hold Ctrl (Mac: ⌘) and select B9:D9.
2. Go to Insert > Insert Column or Bar Chart > Clustered Column.

You now have three columns at 20, 25 and 16. The guides to chart basics and bar charts cover formatting the chart itself.

Step 3: Add custom error bars from your standard deviation cells#

Now link the error bars to row 10, so each column gets its own length.

Windows:

1. Click the chart, then the Chart Elements button (the + next to the chart).
2. Point to Error Bars, click the arrow and choose More Options.
3. In the Format Error Bars pane, set Direction to Both and End Style to Cap.
4. Under Error Amount, choose Custom and click Specify Value.
5. In Positive Error Value, delete what's there and select B10:D10. Do the same for Negative Error Value. Click OK.

Mac: Click the chart, go to Chart Design > Add Chart Element > Error Bars > More Error Bars Options, then follow steps 3 to 5.

Alpha's bar now runs from 18 to 22, Beta's from about 23.2 to 26.8, and Gamma's from about 13.6 to 18.4. If you change a score, the standard deviation and the error bar update by themselves.

Why not use the built-in Standard Deviation option?#

The Error Bars menu has a ready-made Standard Deviation option, but it doesn't use your raw data. It calculates the standard deviation of the values plotted on the chart, the three means, and applies that one amount. That says how different the teams' averages are from each other, not how varied each team is.

The same goes for Standard Error, Percentage and Fixed Value: each applies one rule to every bar. For error bars based on each group's own data, always choose Custom.

Standard error and confidence interval error bars#

To show how precise each average is, put standard errors in row 11 and use those cells instead:

=B10/SQRT(COUNT(B2:B7))

Alpha's standard error is 2 ÷ √6 ≈ 0.82, Beta's ≈ 0.73 and Gamma's ≈ 0.97. With more data, the standard error shrinks, because the average is better known. For a 95% confidence interval, multiply by the right critical value, or use =CONFIDENCE.T(0.05,B10,COUNT(B2:B7)). The guide to confidence intervals in Excel explains this step.

Common error bar mistakes#

• All bars the same length. You used Fixed, Percentage or the built-in Standard Deviation. Switch to Custom with one value per bar.
• Only one cell selected. If Specify Value points at a single cell, every bar uses that value. Select the whole row of standard deviations.
• Ranges in a different order. The custom range must follow the same order as the columns in the chart.
• Unlabelled bars. Add a note such as "Error bars show ±1 standard deviation".
• Too few values. With two or three values per group, the standard deviation is very rough. Say how many values each bar is based on.

Practise it#

Error bars depend on getting the summary numbers right: averages, spread and the middle value. Practise AVERAGE, MIN and MAX in a live spreadsheet, then practise MEDIAN and MODE to check whether your data is skewed. Each lesson checks your formula instantly. The AVERAGE, MIN and MAX guide is a quick refresher. Then try the exercise on this page to work out a standard deviation step by step.