how to add individual error bars in excel

Error Bar in Excel

Excel Error Bar (Table of Contents)

Error Bars in Excel
How to Add Error Bars in Excel?

Error Bars in Excel

Error bars is one of the graphical representation of data which is used to denote the errors. Also, error bars can have plus, minus or both types of direction with Cap and No Cap style of bars. To insert error bars, first, create a chart in Excel using any Bars or Columns charts, mainly from the Insert menu tab. Then click on the Plug button located at the right top corner of the chart and select Error Bars from there. To further customize the error bars choose More Options from the same menu list.

Error Bar 1

In Error Bar, we have three options which are listed as follows:

Errors Bars with Standard Error.
Error Bars with Percentage.
Error Bars with Standard Deviation.

Error Bar 2

How to Add Error Bars in Excel?

To add error bars in excel is very simple and easy. Let's understand how to add error bars in Excel with a few different examples.

You can download this Error Bar Excel Template here – Error Bar Excel Template

Example #1

In Microsoft excel standard error bar is the exact standard deviation. The standard error is a statistical term that measures the accuracy of the specific data. In statistics, a sample means deviates from the actual mean of the population, and therefore this deviation is called the standard error.

In this example, we will learn how to add error bars to the chart. Let's consider the below example where it shows the sales actual figure and forecasting figure.

Error Bar Example 1-1

Now we will apply excel error bars by following the below steps as follows.

First, select the Actual and Forecast figure along with the month to get the graph as shown below.

Error Bar Example 1-2

Go to the Insert menu and choose the Line chart.

Error Bar Example 1-3

We will get the below line chart with an actual and forecasting figure as follows.

Error Bar Example 1-4

Now click on the forecast bar so that the actual line will get selected with a dotted line, as shown below.

Error Bar Example 1-5

Once we can click on the forecast, we can see the layout menu. Click on the Layout menu, where we can see the Error bar option.

Error Bar Example 1-6

Click on the Error Bars option so that we will get the below option as shown below.
In the error bar, click on the second option, "Error Bar with Standard Error".

Error Bar Example 1-7

Once we click on the standard error bar, the graph will get changed, as shown below. In the below screenshot, we can see the standard error bar, which shows fluctuation statistic measurement of Actual and forecast report.

Error Bar Example 1-8

Example #2

This example will learn how to add an Excel Error bar with a percentage in the graphical chart.

Let's consider the below example, which shows a chemistry lab trial test shown as follows.

Error Bar Example 2-1

Now calculate the Average and standard deviation to apply for the error bar by following the below steps.

Create two new columns as Average and Standard Deviation
Insert Average formula =AVERAGE(B2:F2) by selecting B2: F2.

Example 2-2

We will get the average output as follows.

Example 2-3

Now calculate the Standard Deviation by using the formula =STDEVA(B2:F2)

Example 2-4

We will get the standard deviation as follows.

Example 2-5

Now select the Sucrose Concentration Column and AVG column by holding the CTRL-key.

Example 2-6

Go to the Insert menu. Select the Scatter Chart that needs to be displayed. Click on the scatter chart.

Example 2-7

We will get the result as follows.

Example 2-8

We can notice that dotted lines show the average trails figure.
Now click on the blue dots so that we will get the layout menu.
Click on the Layout Menu, and we can find the error bar option as displayed in the below screenshot.

Example 2-9

Click on the Error Bar, and we will get error bar options. Click on the third option called "Error Bar with Percentage".

Example 2-10

Once we click on the Error Bar with the percentage, the above graph will get changed, as shown below.

Example 2-11

In the above screenshot, we can see that Error Bar with 5 Percentage measurement.
By default, Error Bar with Percentage takes as 5 Percentage. We can change the Error Bar Percentage by applying custom values in excel, as shown below.
Click on the Error Bar where we can see More Error Bar Option as shown below.
Click on the "More Error Bar option".

Example 2-12

So that we will get the below dialogue box, we can see in the percentage column by default, excel will take as 5 Percent.

Example 2-13

Here In this example, we are going to increase the Error Bar Percentage by 15 %, as shown below.

Example 2-14

Close the dialogue box so that the above Error bar with percentage will be get increased by 15 percentage.
In the below screenshot, Error Bar with Percentage shows 15 percentage Error Bar measurement of 1.0, 1.0, 0.0, -1.3 and at last with value -1.4 in blue color dots as shown in the below screenshot.

Example 2-15

Example #3

In this example, we are going to see how to add an Error Bar with Standard Deviation in Excel.

Consider the same example where we have already calculated Standard deviation as shown in the below screenshot where it shows chemistry labs test trails with average and standard deviation.

Example 3-1

We can apply an Error Bar with a Standard Deviation by following the below steps.

First, select the Sucrose Concentration column and Standard deviation Column.
Go to the Insert menu to choose the chart type and select the Column chart as shown below.

Example 3-2

We will get the below chart as follows.

Error Bar Example 3-3

Now click on the Blue Colour bar so that we will get the dotted lines as shown below.

Error Bar Example 3-4

Once we click on the selected dots, we will get the chart layout with the Error Bar option. Choose the fourth option, "Error Bars with Standard Deviation".

Error Bar Example 3-5

So that we will get the below standard deviation measurement chart as shown in the below screenshot.

Error Bar Example 3-6

To show the Error bar in Red Bar, follow the same process as illustrated above.

Error Bar Example 3-7

As we can notice that error bars showing a standard deviation of 0.5, 0.1, 0.2, 0.4 and 1.0

Things to Remember

Error Bars in Excel can be applied only for chart formats.
In Microsoft excel, error bars are mostly used in chemistry labs and biology, which are used to describe the data set.
While using Error bars in excel, make sure that you are using the full axis, i.e. numerical values must start at zero.