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.

2 Examples to Add Custom Error Bars in Excel

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.

2 Examples to Add Custom Error Bars in Excel

  • 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.

2 Examples to Add Custom Error Bars in Excel

  • 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.

2 Examples to Add Custom Error Bars in Excel

  • 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.

2 Examples to Add Custom Error Bars in Excel

  • 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.

2 Examples to Add Custom Error Bars in Excel

  • 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.

  • This creates the custom bars.

Read More: How to Add Individual Error Bars in Excel


Download the Practice Workbook


Related Articles


<< Go Back To Excel Chart Elements | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sabrina Ayon
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo