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

The dataset below contains measurements of the Air Quality Index of Kyiv, the capital of Ukraine.

Air Quality Index Measurement of Kyiv

To create a bar chart, select the AQI column and go to the Insert tab >> Insert Column/ Bar Chart. Select the bar chart that fits your needs.

1-Inserting Bar Charts

A bar chart will appear like this.

After Inserting Bar Chart in Excel


Method 1 – Addition of Standard Error Bars in a Bar Chart

Steps:

  • Click anywhere on the bar chart. A menu will appear on the right side of the graph – as shown below.

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

  • The bar chart will 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

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


Method 2 – Addition of Percentage Error Bars in a Bar Chart

Steps:

  • Click on any point in the chart.
  • From the menu in the right corner, select Chart Element >> Error Bars >> Percentage.

Choosing Percentage Error Bar for Bar Chart in Excel

  • 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


Method 3 – Addition of Standard Deviation Error Bars in a Bar Chart

Steps:

  • Click on any point in the chart.
  • From the menu in the right corner, select Chart Element >> Error Bars >> Standard Deviation.

Choosing Standard Deviation Error Bar for Bar Chart in Excel

  • 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


Method 4 – Addition of Custom Error Bars in a Bar Chart

Steps:

  • Go to  Chart Element >> Error Bars >> More Options.

Choosing Custom Error Bar for Bar Chart in Excel

  • You will see Error Bar Options under the Format Window Bar on the right side.

Format Error Bar Menu

  • 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, select the type and insert the Error Amount.
    • For example, the default value of the Percentage Error Bar is 5⁒. By inserting a value in the percentage, you can modify it to any number you wish.

Changing Error Amount in Percentage Error Bar in Format Error Bar

  • Specify different error values for each data point by selecting Custom and then clicking on Specify Values.

Specifying Custom Value for Error Bars in Excel Bar Chart

  • A dialogue box will appear.
  • Insert error values for each data point (both positive and negative error values). (i.e., in the range of D5:D9.)

Specifying Custom Value for Error Bars in Excel Bar Chart

  • 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

  • Click OK, and the chart will display the Error Bars like this.

Bar Chart with Custom Error Bars in Excel

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


Things to Remember

  • We can change the properties of the Error Bar lines, 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 workbook to practice.


Related Articles


<< Go Back to Excel Bar Chart | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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