Yousuf Khan
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.
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.
- 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.
- 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.
- 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.
- 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.
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.
- 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.
- Eventually, the Format Error Bars dialog box slides on the display.
- Here, check the Custom checkbox and tap Specify Value box.
- 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.
- Therefore, we display the bar graph with modified error bars in our dataset.
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.
