How to Add Error Bars in Excel? 7 Best Methods

Note: This guide on how to add error bars in Excel is suitable for Excel 2010, 2013. 2016, 2019, and Office 365 versions. 

Error bars in Excel help visualise the error margins or standard deviation in your data. Add them to your charts to show the error in absolute or percentage terms or standard deviations. 

They can be added to all the data points in a series to display a preset error percentage of the user’s choice. Error bars are very suitable for bar, column, line, scatter, and bubble charts. 

In this guide, I am going to explain how to add error bars in Excel along with their various use cases with the help of illustrations. 

Related:

How to Extract an Excel Substring? – 6 Best Methods

How to Superscript in Excel? (9 Best Methods)

How To Find Duplicates in Excel? (3 Easy Methods)

Watch this video to learn how to add error bars in Excel

Error Bars in Excel – An Overview

Error bars are a very useful tool to represent the errors or the variability in a data set. That is they display how far the real value can deviate from the measured value. It is a very important tool in statistical analysis and scientific experiments. 

In Excel, error bars can be added to suitable charts like bar, column, line, scatter, and bubble graphs. The user has the option to add the following forms of the error bar:

1. As a standard error
2. As a percentage error
3. As a fixed value
4. As a standard deviation 

Users can also set their own predefined error amount and set individual values for each error bar in the series. 

Adding error bars in Excel 2013 and later versions is slightly different from the method applicable to the earlier versions. These are explained in separate sections below. 

Also Read:

How to Use Goal Seek in Excel? (3 Simple Examples)

How to Insert Multiple Rows in Excel? The 4 Best Methods

How to Autofit Excel Cells? 3 Best Methods

How to Add Error Bars in Excel 2013 and Later Versions?

To add error bars in Excel 2013, Excel 2016, Excel 2019, and Microsoft Office 365 versions follow these steps after plotting suitable graphs: 

Step 1: Click anywhere in the graph

Step 2: Click on the Chart Elements option in the top-right corner of the chart. 

Step 3: Click on the Error Bars arrow to expand it and select a suitable option. 

• Standard Error – To display the standard error for all values, that is the how far the sample mean is from the population mean of the data series.
  
• Percentage Error – To add percentage error bars to your chart. The default percentage error is 5%, which can be changed from “More options”.
  
• Standard Deviation – To show the standard deviation of the data point i.e. how far is it from the average.
  
• More Options – To edit error bar values and to create custom error bars. It will open the Format Error Bars pane where you can do the following: 

1. Set custom values for the fixed value, percentage, and standard deviation error bars.
   
2. Choose the direction of the error bars (positive, negative, or both)
   
3. Make custom error bars based on custom values.
   
4. Format the appearance of error bars. 

To insert standard error bars in the chart, you can directly select the Error Bars option. 

To customize the error bars after adding them, double click on any one of them to access the Format Error Bars pane, where you can edit everything about them. 

How to Add Error Bars in Excel 2010 and Earlier Versions?

To add error bars in earlier versions of Excel i.e. versions 2010 and 2007, follow these steps:

Step 1: Click on the chart to access the Chart Tools tab in the Excel ribbon. 

Step 2: Under the Chart Tools tab, locate and click on the Error Bars option. It can be found in 

             the Analysis group in the Layout tab. 

How to Add Custom Value Error Bars in Excel?

If you want to add your own custom value error bars to your charts follow these steps:

Step 1: Click Chart Elements > Error Bars > More Options to access the Format Error Bars 

   pane

Step 2: Click on the Error Bars options tab. 

Step 3: Under Error Amount, click Custom and click on Specify Value

Step 4: In the Custom Error Bars Dialogue box enter the Positive Error Value and Negative 

  Error Value. If you don’t want a positive or negative value you must enter zero. 

That’s it, you have added custom value error bars to your graph. 

How to Add Individual Error Bars in Excel?

Sometimes, you will need to add different individual error bars of varying lengths to each data point in the series. To do this, follow these steps: 

Step 1:  Calculate the Standard deviation of each data point using the STDEV.P function in an 

    adjacent row or column. 

Step 2: Click Chart Elements > Error Bars > More Options to access the Format Error Bars 

   pane

Step 3: Click on the Error Bars options tab. 

Step 4: Under Error Amount, click Custom and Click on Specify Value

Step 5: In the Custom Error Bars Dialogue box for the Positive Error Value and Negative 

  Error Value, enter the cell reference for the Standard deviation values we just 

  calculated. 

Congratulations, you have successfully added individual error bars of varying lengths of standard deviation to each data point in the data series. 

How to Add Horizontal Error Bars In Excel?

Horizontal error bars are by default added to horizontal bar charts, bubble, and scatter graphs. They are added along with the vertical error bars. 

But, if you need only the horizontal error bars, delete the vertical error bars by right-clicking on any one of them choosing the delete button. 

How to Add Error Bars In Excel for Specific Data Series?

In case if you are using a combo chart in your Excel sheet, it is advisable to add error bars only to one particular data series in the chart. This will avoid confusion by reducing the clutter. 

To do this, just click on the specific data series you want to add error bars to, and then click on the Chart Elements button. Now, click the arrow next to Error Bars and pick any desired type. 

Doing this will add error bars only to the data series you selected. 

How to Format Error Bars in Excel? 

To edit the appearance of the error bars in Excel follow these steps: 

Step 1: Double Click on the Error bars in the chart you want to edit. 

Step 2: To change the appearance of the error bar like colour and width, choose the Fill & Line 

tab. There, you will have the option to change everything about the appearance of the 

error bar. 

Step 3: Click on the Error Bars options tab to change the type, direction and style of the error 

bars

Suggested Reads:

How to Group Worksheets in Excel? (In 3 Simple Steps)

How to Shade Every Other Row in Excel? (5 Best Methods)

How to Use the Excel Fill Handle Easily? (Top 3 Uses with Examples)

Let’s Wrap Up

We are at the end of this guide on how to add error bars in Excel. If you have any questions about this or any other Excel feature, let us know in the comments. We are always happy to help. 

If you need more high-quality Excel guides, please check out our free Excel resources centre.

Ready to dive deep into Excel? Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Adam Lacey

Adam Lacey is an Excel enthusiast and online learning expert. He combines these two passions at Simon Sez IT where he wears a number of different hats.When Adam isn't fretting about site traffic or Pivot Tables, you'll find him on the tennis court or in the kitchen cooking up a storm.