Error bars in Excel charts are a tool to show data variability and measurement accuracy. In other words, they can be useful to show you how far from the reported values the actual values might be. In MS Excel, you can add error bars in line and area graphs, scatter plots and bubble charts, and both vertical and horizontal error bars. Another way is to learn how to add standard deviation bars in excel or standard error. In this article, we have taken examples and guidelines from Ablebits.com

How to add standard deviation bars in excel 

  1. How to add error bars in Excel

In Excel 2019, Excel 2016, and Excel 2013, learning how to add standard deviation bars is quick and straightforward:

  1. Click on anywhere in your graph.
  2. Click on the Chart Elements button to the right of the chart.
  3. Tap on the arrow next to Error Bars and pick the desired option:
    • Standard Error – showcases the standard error of the mean for all values, which shows how far the sample mean is likely to be from the population mean.
    • Percentage – adds error bars with the default 5% value, on the other hand you can set your own percentage by choosing More Options.
    • Standard Deviation – shows the amount of variability of the data, that is how close it is to the average. By default, the bars are graphed with 1 standard deviation for all data points.
    • More Options… – allows specifying your own error bar amounts and creating custom error bars.
    • Adding error bars in Excel

Picking More Options opens the Format Error Bars pane so that you can :

  • Set your own amounts for fixed value, percentage and standard deviation error bars.
  • Select the direction you want (for eg. positive, negative, or both) and end style (cap, no cap).
  • Make custom error bars based on your own values.
  • Change the appearance of error bars.

As an example we’ve taken from Ablebits.com, let’s add 10 % error bars to our chart. For this, click on Percentage and type 10 in the entry box:

A chart with custom percent error bars

Tips:

  • To add standard error bars in Excel, you can simply choose the Error Bars box without picking any option. The standard error bars will be inserted by default.
  • To customize the existing error bars, double-tap them in the chart. This will open the Format Error Barspane, where you change error bars type, choose another color and do other customizations.

2. How to include error bars in Excel 2010 and 2007

In earlier versions of Excel, the path to error bars is different. To include error bars in Excel 2010 and 2007, this is what you need to do:

  1. Click anywhere within the chart to activate Chart Tools on the ribbon.
  2. On the Layout tab, in the Analysis group, click Error Bars and choose one of the following options:
    Adding error bars in Excel 2010

3. How to add custom error bars in Excel

The standard error bars provided by Excel work fine in most situations. But if you wish to show your own error bars, you can effortlessly do that too.

To make custom error bars in Excel, carry out these steps from Ablebits.com:

  1. Click the Chart Elements button.
  2. Click the arrow next to Error Bars and then click More Options…
  3. On the last tab of the Format Error Bars pane, under Error Amount, select Custom and click the Specify Valuebutton.
    Making custom error bars in Excel
  4. A small Custom Error Bars dialog box will pop up with two fields, each consisting of one array element like ={1}. You can now add your own values in the boxes (without equality sign or curly braces; Excel will give addition to them automatically) and select OK.
Specify your own values for positive and negative error bars.

If you do not want to show your positive or negative error bars, type in zero (0) in the corresponding box, but don’t fully clear out the box. If you do that, Excel will think you simply forgot to input a number and it will retain the previous values in both boxes.

This technique adds the same constant error values (positive and/or negative) to all data points in a series. But in many instances, you will want to put an individual error bar to each data point. 

4. How to make individual error bars in Excel

To begin with, enter all the error bar values (or formulas) into separate cells, usually in the same columns as the original values. And then, tell Excel to graph error bars based on that range.

In this example from Ablebits.com, we will make individual standard deviation error bars.

Supposing, you have 3 columns with sales numbers. You have calculated an average (B6:D6) for each column and plotted those averages in a chart. Additionally, you found the standard deviation for each column (B7:D7) by using the STDEV.P function, and now you want to display those numbers in your graph as standard deviation error bars. Here’s how:

  1. Perform steps 1 – 3 described above.
  2. In the Custom Error Bars dialog box, delete the contents of the Positive Error Value box, put the mouse pointer in the box (or click the Collapse Dialog icon next to it), and select a range in your worksheet (B7:D7 in our case).
  3. Do the same for Negative Error Value If you do not want to display negative error bars, type 0.
  4. Click OK.
Adding custom standard deviation error bars

Important note! Be sure to delete and get rid of the entire contents of the entry boxes prior to selecting a range. Otherwise, the range will be added to the existing array like shown below, and you will end up with an error message:

={1}+Sheet1!$B$7:$D$7

It is quite difficult to spot this error because the boxes are narrow, and you cannot see all the contents.

If all done correctly, you will get individual error bars, proportional to the standard deviation values that you’ve calculated:

Individual error bars in Excel chart

5. How to add horizontal error bars in Excel

For most chart types, only vertical error bars are available. Horizontal error bars can be added to bar charts, XY scatter plots, and bubble charts.

For bar charts (please do not mix this up with column charts), horizontal error bars are the default and only available type. The screenshot displayed under shows an example of a bar chart with error bars in Excel:

A bar chart with error bars in Excel

In bubble and scatter graphs, error bars are inserted here for both x values (horizontal) and y values (vertical).

If you’d like to only insert horizontal error bars, all you have to do is remove vertical error bars from your chart. Here’s how:

  1. Add error bars to your chart as usual.
  2. Right-click any vertical error bar and choose Delete from the pop-up menu.
Delete vertical error bars.

This will get rid of vertical error bars from all data points. You can now open the Format Error Bars pane (to do so, double-click on any of the remaining error bars) and customize the horizontal error bars to your liking.

Customize horizontal error bars in Excel chart.