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

Method 1 – Excel Custom Error Bars with Similar Error Values for All Data Points

To add custom bars with the same variance for all data points, we are going to use the following dataset. The dataset contains a company’s quarterly sales. This includes some employee names and the sales of quarters 1, 2, and 3. We generated the averages (C5:C9), (D5:D9), and (E5:E9) of each quarter using the AVERAGE function for placing those on the bar chart. We also used the STDEV.P function to calculate the standard deviation for each column (C5:C9), (D5:D9), and (E5:E9). We want to show those statistics as standard deviation error bars in the bar chart.

Steps:

Select the averages to plot those averages in the bar chart. We selected cells B11:E11.
Go to the Insert tab from the ribbon.
In the Charts category, click on the Insert Column or Bar Chart drop-down menu and choose the first chart, Clustered Column, from the 2-D Column.

You can see the data charts are inserted for better visualization. The name of the chart is “Average”.

Click on the chart and go to the Chart Elements, which is a plus (+) sign.
To the right of the Error Bars option, pick the black triangle indicator and then choose More Options.

This will open the Format Error Bars dialog on the right side of your spreadsheet.
From the Error Bars Options, click on the chart symbol.
In the Error Amount option, choose Custom and click on the Specify Value beside the Custom option.

This will display the Custom Error Bars dialog.
For Positive Error Value, select the range that contains the standard deviation by clicking on the range selector icon.
For Negative Error Value, select the same range.
Click on the OK button to complete the procedure.

Based on the value of the standard deviation that we have chosen, this will generate custom error bars for each data point.

Read More: How to Add Error Bars in Excel

Method 2 – Create Custom Error Bars If All Data Points Have Dissimilar Variances in Excel

Let’s use the following dataset. The dataset has some quarter sales and total sales in each quarter, as well as the Positive Efficiency Bar (EB) and the Negative Efficiency Bar (EB) to plot the chart and add the custom error bars.

Steps:

Pick the ranges to plot the bar chart. We chose cell B4:C8.
From the ribbon, select the Insert tab.
Under the Charts group, select Clustered Column from the 2-D Column of the Insert Column or Bar Chart drop-down menu.

This will place the data chart named “Total Sales”.

Click on the chart and then go to the Chart Elements.
To the right of the Error Bars option, select the black triangle indication to select More Options.

The Format Error Bars dialog will appear on the right side of your spreadsheet.
Select the chart symbol from the Error Bars Options menu.
Under the Error Amount section, select Custom and click the Specify Value button next to the Custom option.

The Custom Error Bars dialog will appear.
For Positive Error Value, click on the range selector icon to select the range containing the positive EB (D5:D8).
Choose the range that contains the negative EB (E5:E8) by clicking on the range selector icon for Negative Error Value.
Click the OK button to finish the process.