In this article, I will show you how to create 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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
4 Useful Types of Error Bars in Excel Bar Charts
In this section, I will demonstrate 4 types of Error Bars in Excel. Excel offers 3 predefined types of Error Bars. They are
- Standard Error
- Standard Deviation
- 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.
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.
As a result, a bar chart will appear like this.
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.
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.
- From the menu, go to Chart Element >> Error Bars >> Standard Errors.
- 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.
This is how we can easily add Standard Error Bars in Excel bar charts.
Read More: How to Create a Bar Chart with Standard Deviation 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 on the right corner, select Chart Element >> Error Bars >> Percentage.
- As a result, the Percentage Error Bars will appear on the bar chart (Marked by blue circles). As Excel uses 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 .
Read More: How to Show Percentage in Bar Chart in Excel (3 Handy Methods)
Similar Readings
- How to Make a 100 Percent Stacked Bar Chart in Excel (with Easy Steps)
- Show Number and Percentage in Excel Bar Chart
- How to Make a Percentage Bar Graph in Excel (5 Methods)
- Difference Between Excel Histogram and Bar Graph
- How to Combine Two Bar Graphs in Excel (5 Ways)
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.
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 Standard Deviation Error Bar, follow the steps below.
Steps:
- Like the first example, click on any point on the chart. Now from the appeared menu in the right corner, select Chart Element >> Error Bars >> Standard Deviation.
- As a result, the Standard Deviation Error Bars will appear on the bar chart. (Marked by red circles)
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 Show Variance in Excel Bar Chart (with Easy Steps)
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.
- As a result, you will see Error Bar Options under Format Window Bar on the right side.
- 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.
- Moreover, we can specify different values of error for each data point by selecting Custom and then clicking on Specify Values.
- 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.
- Hence, We just need to select the D5:D9 range for both Positive and Negative values while inserting values into the fields..
- Now, If I click OK, the chart will display the Error Bars like this.
In this way, we can easily create a bar chart with custom Error Bars in Excel.
Read More: How to Create Bar Chart with Multiple Categories in Excel
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.
Conclusion
That is the end of this article regarding how to create 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. Finally, please visit Exceldemy for more exciting articles on Excel.
Related Articles
- How to Sort Bar Chart in Descending Order in Excel (4 Easy Ways)
- Create & Customize Bar of Pie Chart in Excel
- How to Create a 3D Bar Chart in Excel (3 Suitable Examples)
- Make a Simple Bar Graph in Excel (with Easy Steps)
- How to Make a Double Bar Graph in Excel (with Easy Steps)
- Create Clustered Stacked Bar Chart in Excel
- How to Add Horizontal Line to Bar Chart in Excel (3 Easy Ways)