Table of Contents
Occasionally you may want to add horizontal error bars to a chart in Excel.
This tutorial provides a step-by-step example of how to create the following chart with horizontal error bars in Excel:
Let’s jump in!
Step 1: Enter the Data
First, let’s create the following dataset that shows how various sports analysts ranked a particular basketball player in their first five years of being in the league:
Step 2: Calculate the Average & Standard Deviation
Next, type the following formulas into cells B6 and B7 to calculate the average and standard deviation of values for each year:
- B6: =AVERAGE(B2:B5)
- B7: =STDEV(B2:B5)
We can then click and drag each of these formulas to the right to calculate the average and standard deviation of values for each year:
Step 3: Insert Horizontal Bar Chart
Next, highlight the cell range B6:F6, then click the Insert tab along the top ribbon, then click the icon called 2-D Bar within the Charts group.
The following horizontal bar chart will be created that shows the average player ranking during each of the five years:
Step 4: Add Horizontal Error Bars
Next, click anywhere on the chart and then click the tiny green plus sign in the top right corner of the chart.
Then check the box next to Error Bars to add horizontal error bars to each bar:
Next, click on any of the horizontal error bars to bring up the Format Error Bars panel on the right side of the screen.
Then click Custom, then click the button called Specify Value:
In the new window that appears, type =Sheet1!$B$7:$F$7 for both the Positive Error Value and Negative Error Value box:
Once you click OK, the horizontal error bars will be added to each bar in the chart to show the standard deviation of the ratings for each year:
The wider the error bars, the more variation in ratings for each year.
For example, the standard deviation of ratings in year 1 is 0.9574 and the standard deviation of ratings in year 2 is 0.5.
This explains why the horizontal error bars in year 1 are nearly twice as wide as the error bars in year 2.
Step 5: Customize the Chart (Optional)
Feel free to add a title, customize the colors, remove the gridlines, adjust the width of the error bars, and add axis labels to make the plot easier to read.
The final plot may look like this: