How to Create Bar Chart with Error Bars in Excel (4 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this article, I will show you how to create a bar chart with Error Bars in Excel. While representing different scientific measurements and data in charts, it is also essential to indicate their uncertainty or errors in measurement. Excel has a built-in Error Bars feature that we can use to represent the uncertainty of our data. Here, I will illustrate all sorts of Error Bars available in Excel. So let’s get started.


What Is an Error Bar in Charts?

A visual element called an Error Bar is used to display the variability or uncertainty in a set of data. It is typically represented on a graph as a vertical line or horizontal bar to represent the degree of uncertainty or variability associated with the relevant data point or value. The length of the Error Bar, which depicts the range within which the true number is expected to fall, is often based on the standard deviation or standard error of the data. Error Bars can be used to demonstrate the variation among many data points or to represent the degree of confidence that can be attached to a particular measurement.


Bar Chart with Error Bars in Excel: 4 Useful Types

In this section, I will demonstrate 4 types of Error Bars in Excel. Excel offers 3 predefined types of Error Bars. They are-

1. Standard Error

2. Standard Deviation

3. Percentage Error

Excel also offers to create customized Error Bars for every data point. To illustrate the creation of those 4 types of Error Bars, I have taken a dataset containing a number of measurements of the Air Quality Index of the city of Kyiv, the capital of Ukraine.

Air Quality Index Measurement of Kyiv

Now, we have created a bar chart from this data. To do so, we selected the AQI column and then went to the Insert tab >> Insert Column/ Bar Chart. From the bar chart options, I selected one according to my choice.

1-Inserting Bar Charts

As a result, a bar chart will appear like this.

After Inserting Bar Chart in Excel

Now, we will add different kinds of error bars as described above in the bar chart. Let’s add the Error Bars one by one.


1. Addition of Standard Error Bars in Bar Chart

In the first example, we will use the Standard Error Bars in the bar chart shown above. The Standard Error is calculated by dividing the standard deviation of the data by the square root of the sample size. The standard error is often used to calculate the confidence interval, which is a range of values that is likely to include the true value of a population parameter. The formula by which Excel calculates Standard Error is given below.

Standard Error Bar Formula in Excel

Where,

s= series number

i= Point number in series s

m= number of series for point y in the chart

n= number of points in each series

yis= data value of series s and the ith point

ny= total number of data values in all series

To know how to apply the Standard Error Bar, follow the steps below.

Steps:

  • First, click anywhere on the bar chart. As a result, a menu will appear on the right side of the graph like this.

Opening Context Menu of Bar Chart in Excel

  • From the menu, go to Chart Element >> Error Bars >> Standard Errors.

Choosing Standard Error Bar for Bar Chart in Excel

  • As a result, the bar chart will also display Error Bars like this. The standard error of these data should be a constant value, so you will see the Error Bar of the same size on top of each bar in the chart.

Bar Chart with Standard Error Bars in Excel

This is how we can easily add Standard Error Bars in Excel bar charts.

Read More: How to Change Bar Chart Color Based on Category in Excel


2. Addition of Percentage Error Bars in Bar Chart

In this example, I will show how to add percentage Error Bars in bar charts Percentage error bars show the specified percentage variation in each data point. To add percentage error bars, follow the steps below.

Steps:

  • Like the first example, click on any point on the chart. Now from the menu in the right corner, select Chart Element >> Error Bars >> Percentage.

Choosing Percentage Error Bar for Bar Chart in Excel

  • As a result, the Percentage Error Bars will appear on the bar chart (Marked by blue circles). As Excel uses a 5% default value, the Error Bar will be around ±5% of each value. For example, the Error Bar for 224 will be 224±224*5%= 224±11.2= 212.8 to 235.2.

Bar Chart with Percentage Error Bars in Excel

Read More: Excel Add Line to Bar Chart


3. Addition of Standard Deviation Error Bars in Bar Chart

The standard deviation is a statistic that expresses how much variance or dispersion there is in a group of numbers.  While a high standard deviation suggests that the values are dispersed over a wider range, a low standard deviation suggests that the values tend to be close to the mean (also known as the anticipated value) of the collection. Excel uses the formula below to calculate the Standard Deviation.

Equation of Standard Deviation Error Bar

Where,

s= series number

i= Point number in series s

m= number of series for point y in the chart

n= number of points in each series

yis= data value of series s and the ith point

ny= total number of data values in all series

M= Arithmetic Mean

Now, to add the Standard Deviation Error Bar, follow the steps below.

Steps:

  • Like the first example, click on any point on the chart. Now from the menu in the right corner, select Chart Element >> Error Bars >> Standard Deviation.

Choosing Standard Deviation Error Bar for Bar Chart in Excel

  • As a result, the Standard Deviation Error Bars will appear on the bar chart. (Marked by blue circles)

Bar Chart with Standard Deviation Error Bars in Excel

The size of the Error Bars in this method will also be the same for each bar in the chart like in Method 1 where we used the Standard Error Bar. Because the Standard Deviation of these data is also a constant value.

Read More: How to Add Horizontal Line to Bar Chart in Excel


4. Addition of Custom Error Bars in Bar Chart

Excel offers a great deal of flexibility as we can add our customized Error Bars in bar charts in Excel. To learn more, follow the steps below.

Steps:

  • First, go to  Chart Element >> Error Bars >> More Options.

Choosing Custom Error Bar for Bar Chart in Excel

  • As a result, you will see Error Bar Options under the Format Window Bar on the right side.

Format Error Bar Menu

  • As we can see, there are many options available to customize the Error Bars. On the top, we can choose the Direction and End Style of the Vertical Error Bar.
  • If we want to customize the default value of the Error Bars that we have discussed, we can do that by selecting the type and inserting the Error Amount.
  • For example, the default value of the Percentage Error Bar is 5⁒. We can modify to any number as we wish by inserting the value in the Percentage.

Changing Error Amount in Percentage Error Bar in Format Error Bar

  • Moreover, we can specify different values of error for each data point by selecting Custom and then clicking on Specify Values.

Specifying Custom Value for Error Bars in Excel Bar Chart

  • Consequently, a dialogue box will appear. Here, we need to insert error values for each data point. (Both Positive and Negative Error Values). For ease of insertion, I have written the Values in the range of D5:D9.

Specifying Custom Value for Error Bars in Excel Bar Chart

  • Hence, We just need to select the D5:D9 range for both Positive and Negative values while inserting values into the fields..

Specifying Custom Value for Error Bars in Excel Bar Chart

  • Now, If I click OK, the chart will display the Error Bars like this.

Bar Chart with Custom Error Bars in Excel

In this way, we can easily create a bar chart with custom Error Bars in Excel.

Read More: How to Add Vertical Line to Excel Bar Chart


Things to Remember

  • We can change the Error Bar lines’ properties such as line type, color, etc by accessing the properties tab by clicking on any Error Bar.
  • In Horizontal Bar charts, the Error Bar will also be horizontal.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

That is the end of this article regarding how to create a bar chart with Error Bars in Excel. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.


Related Articles


<< Go Back to Create a Bar Chart in Excel | Excel Bar Chart | Excel Charts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aniruddah Alam
Aniruddah Alam

Md. Aniruddah Alam, a graduate of Bangladesh University of Engineering & Technology, Dhaka, holds a BSc.Engg in Naval Architecture & Marine Engineering. Joining Softeko in August 2022 as an Excel and VBA content developer, he has authored 90+ articles, covering fundamental to advanced Excel topics. He actively develops VBA codes for Excel automation and offers solutions in the Exceldemy Forum. Beyond work, he finds leisure in reading books, and taking relaxing walks, showcasing well-rounded blend of professional and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo