How to add error bars in Excel: standard and custom

The tutorial shows how to make and use error bars in Excel. You will learn how to quickly insert standard error bars, create your own ones, and even display individual error bars for each data point.

Many of us are uncomfortable with uncertainty because it is often associated with lack of data, ineffective methods or wrong research approach. In truth, uncertainty is not a bad thing. In business, it prepares your company for the future. In medicine, it generates innovations and leads to technological breakthroughs. In science, uncertainty is the beginning of an investigation. And because scientists love quantifying things, they found a way to quantify uncertainty. For this, they calculate confidence intervals, or margins of error, and display them by using what is known as error bars.

Error bars in Excel

Error bars in Excel charts are a useful tool to represent data variability and measurement accuracy. In other words, error bars can show you how far from the reported values the actual values might be.

In Microsoft Excel, error bars can be inserted in 2-D bar, column, line and area graph, XY (scatter) plot, and bubble chart. In scatter plots and bubble charts, both vertical and horizontal error bars can be displayed.

You can put error bars as a standard error, percentage, fixed value, or standard deviation. You can also set your own error amount and even supply an individual value for each error bar.
Error bars in Excel

How to add error bars in Excel

In Excel 2019, Excel 2016, and Excel 2013, inserting error bars is quick and straightforward:

  1. Click anywhere in your graph.
  2. Click the Chart Elements button to the right of the chart.
  3. Click the arrow next to Error Bars and pick the desired option:
    • Standard Error - displays 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, but you can set your own percentage by choosing More Options.
    • Standard Deviation - shows the amount of variability of the data, i.e. 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 where you can:

  • Set your own amounts for fixed value, percentage and standard deviation error bars.
  • Choose the direction (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, let's add 10 % error bars to our chart. For this, select 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 select the Error Bars box without picking any option. The standard error bars will be inserted by default.
  • To customize the existing error bars, double-click them in the chart. This will open the Format Error Bars pane, where and you change error bars type, choose another color and do other customizations.

How to do error bars in Excel 2010 and 2007

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

  1. Click anywhere in 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

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 display your own error bars, you can easily do that too.

To make custom error bars in Excel, carry out these steps:

  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 Value button.
    Making custom error bars in Excel
  4. A small Custom Error Bars dialog box appears with two fields, each containing one array element like ={1}. You can now enter your own values in the boxes (without equality sign or curly braces; Excel will add them automatically) and click OK.

Specify your own values for positive and negative error bars.

If you do not want to display positive or negative error bars, enter zero (0) in the corresponding box, but don't completely clear 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 method adds the same constant error values (positive and/or negative) to all data points in a series. But in many cases, you will want to put an individual error bar to each data point, and the following example shows how to do this.

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, 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 the entire contents of the entry boxes before 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

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 confuse with column charts), horizontal error bars are the default and only available type. The screenshot below 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 for both x values (horizontal) and y values (vertical).

If you'd like to only insert horizontal error bars, simply 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 remove vertical error bars from all data points. You can now open the Format Error Bars pane (for this, 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.

How to make error bars for a specific data series

Sometimes, adding error bars to all data series in a chart could make it look cluttered and messy. For example, in a combo chart, it often makes sense to put error bars to only one series. This can be done with the following steps:

  1. In your chart, select the data series to which you want to add error bars.
  2. Click the Chart Elements button.
  3. Click the arrow next to Error Bars and pick the desired type. Done!

The screenshot below shows how to do errors bars for the data series represented by a line:
Adding error bars for a specific data series

As the result, the standard error bars are inserted only for the Estimated data series that we selected:
Standard error bars are inserted for only one data series.

How to modify error bars in Excel

To change the type or appearance of the existing error bars, perform these steps:

  1. Open the Format Error Bars pane by doing one of the following:
    • Click the Chart Elements button > Error Bars > More Options…
    • Right-click error bars and select Format Error Bars from the context menu.
    • Double-click the error bars in your chart.
  2. To change type, direction and end style of the error bars, switch to the Options tab (the last one).
  3. To change the color, transparency, width, cap, join and arrow type, go to the Fill & Line tab (the first one).

Customizing error bars in Excel

How to delete error bars in Excel

To remove error bars from your graph, click the Chart Elements button and clear the Error Bars check box. The shortest instruction ever :)

That's how you do error bars in Excel. If you want some practice, please feel free to download our sample workbook to Excel Error Bars.

I thank you for reading and hope to see you on our blog next week!

You may also be interested in:

6 Responses to "How to add error bars in Excel: standard and custom"

  1. Jared says:

    On mac, the scatter plot does not have horizontal errors by default. There is no option to add them. I have not seen the issue using a windows version.

  2. Frank Dean says:

    Thank you Svetlana! Very helpful.

  3. ISMAIL MUHAMMAD says:

    please guide me on how to draw clustered bar graph

  4. علی says:

    I have charts in Excel that show me the form, but I make the slightest change to the data or image size I get an error 2 and help when an error is fixed after opening the application.

  5. Sameer Esak says:

    How do i assign error bars of different lengths to different points on a graph? Currently Excel will only allow me to assign error bars all of the same length to the points.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard