How to Add Custom Error Bars in Excel

---

Occasionally you may want to add custom error bars to a chart in Excel.

This tutorial provides a step-by-step example of how to create the following chart with custom 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 Scatter Plot

Next, highlight the cell range B6:F6, then click the Insert tab along the top ribbon, then click the icon called Scatter within the Charts group.

The following scatter plot will be created that shows the average player ranking during each of the five years:

Step 4: Add Custom 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 both vertical and horizontal error bars to each point:

Next, click on any of the horizontal error bars and press Delete to delete each horizontal error bar from each point. 

Next, click on any of the vertical 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 vertical error bars will be added to each bar in the chart to show the standard deviation of the ratings for each year:

The longer 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 vertical error bars in year 1 are nearly twice as long 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: