Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Plot Bar Graph with Error Bars in Excel (with Easy Steps)

Microsoft Excel can help us analyze our data more effectively by keeping track of data through Excel charts. The error bars can help readers comprehend charts better, draw attention to these issues, and reduce data ambiguity. Creating a bar graph with error bars in Excel files just involves a few simple steps and a working grasp of Excel. With this in mind, we learn this step-by-step procedure to plot bar graph with error bars in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.


Why Do We Plot Bar Graphs with Error Bars in Excel?

We show a chart’s margin of error for particular measurements graphically by error bars. Data accuracy in both negative and non-negative ranges is indicated by error margins. Statisticians use one of three error bar settings in Excel:

  • Standard Error
  • Percentage
  • Standard Deviation

Each graph you create can have one or more error bar types. They all use the same fundamental insertion method, which reduces the chance of confusion.


Step-by-Step Procedures to Plot Bar Graph with Error Bars in Excel

To create Excel bar graphs along with error bars, we use the Excel Charts group and basic Excel formulas. We will calculate with the AVERAGE and STDEV.S functions. To illustrate this work, take the dataset listed below that represents a record book of a car company.

excel bar graph with error bars


Step 1: Populate Dataset with Necessary Components

This step aims to fill up the spreadsheet cells with the graphing data. This process speeds up by labeling the rows and columns and thus providing you with pertinent data for graphical analysis. Meanwhile, follow the below procedure.

  • Add new rows at the bottom and label them as Average and STRD Deviation.

Populate Dataset with Necessary Components

  • This is because while generating error bars, these labels accelerate data entry.

Read More: How to Add Vertical Error Bars in Excel (With Easy Steps)


Step 2: Use AVERAGE Function to Identify Column Average

The objective of this step is to apply the AVERAGE function to get the averages of columns C, D, E, and F. This function is under the Statistical functions category which returns the averages of a given range. Let’s see how the function works.

  • First, type the below formula in C9,

=AVERAGE(C5:C7)

  • Later, press the Enter key to get the output.

Use AVERAGE Function to Identify Column Average

  • Further, use the AutoFill tool or drag the formula cell to the right.

  • Thus, we obtain the averages of the columns.

Read More: How to Add Horizontal Error Bars in Excel (With Easy Steps)


Step 3: Apply STDEV.S Function to Calculate Standard Deviation

In this step, we compute the standard deviation for each column by adding the STDEV.S function beneath our Average row with the name “Standard Deviation”. The graph has individual error bars since the other columns’ standard deviations need to be filled up. The STDEV.S function is also under the Statistical function category that estimates and returns the standard deviations of an array. Let’s apply the function with this procedure below.

  • Firstly, in C10, type the formula:

=STDEV.S(C5:C7)

  • Afterward, tap the Enter key.

Apply STDEV.S Function to Calculate Standard Deviation

  • Next, use the Autofill tool.

  • Hence, we get the required standard deviations of the columns.

Read More: How to Add Individual Error Bars in Excel (With Easy Steps)


Step 4: Insert Bar Graph in Excel

Meanwhile, we generate a bar chart based on the dataset to compare the values and visualize them in our display. To do so, follow the procedure.

  • To begin with, select cell B4.
  • Further, go to the Insert tab and click on Recommended Charts in the Charts group.

Insert Bar Graph in Excel

  • Subsequently, the Insert Chart dialog box pops up.
  • There, tap the Cluster Column chart option.
  • See the picture below to understand better.

  • As a result, the bar chart appears on the screen.

Insert Bar Graph in Excel


Step 5: Plot Error Bars to Bar Chart in Excel

Furthermore, we will add Error Bars to our bar graph in this step. We will use the Chart Elements tool to do so. Let’s see the following procedure carefully.

  • First, click on the bar graph.
  • After that, tap the green Charts Elements option → Error Bars → Standard Deviation option.

Plot Error Bars to Bar Chart in Excel

  • Consequently, the Error Bars appear on the graph.


Step 6: Modify Error Bars

After adding the Error bars, we customize the graph to increase the specificity of our data. Fortunately, Excel charts provide useful customization tools to modify the charts. Let’s explore the modification options to do so.

  • Firstly, tap on the graph again.
  • Then, click Charts Elements → Error Bars → More Options like the below picture.

Modify Error Bars to plot Excel bar graph with error bars

  • Eventually, the Format Error Bars dialog box slides on the display.
  • Here, check the Custom checkbox and tap Specify Value box.

Modify Error Bars to plot Excel bar graph with error bars

  • Subsequently, the Custom Error Bars dialog box appears.
  • Now, input 5 in both the Positive Error Value and Negative Error Value fields after removing the numbers from each box.
  • To close this menu, click OK.

Modify Error Bars

  • Therefore, we display the bar graph with modified error bars in our dataset.

Modify Error Bars to plot Excel bar graph with error bars

Read More: How to Add Custom Error Bars in Excel (2 Examples)


Conclusion

We hope the above-mentioned step-by-step procedures to plot bar graphs with error bars will now provoke you with productivity to apply them in your spreadsheets. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please leave any further queries or recommendations in the comment box below.


Related Article

Yousuf Khan

Yousuf Khan

Hello! This is MD Yousuf Khan. I am a graduate & post-graduate in Information Technology from Jahangirnagar University, Bangladesh. Currently, I am writing articles for ExcelDemy. I am an independent, self-motivated person with enthusiasm to learn new things, and always try to do my best in any work assigned to me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo