How to add custom error bars in Excel

The standard error bars provided by Excel work fine in most situations. But if you wish to display your own error bars, you can easily do that too.

To make custom error bars in Excel, carry out these steps:

Click the Chart Elements button.
Click the arrow next to Error Bars and then click More Options…
On the Format Error Bars pane, switch to the Error Bars Options tab (the last one). Under Error Amount, select Custom and click the Specify Value button.
Making custom error bars in Excel
A small Custom Error Bars dialog box appears with two fields, each containing one array element like ={1}. You can now enter your own values in the boxes (without equality sign or curly braces; Excel will add them automatically) and click OK.
Specify your own values for positive and negative error bars.

If you do not want to display positive or negative error bars, enter zero (0) in the corresponding box, but don’t completely clear the box. If you do that, Excel will think you simply forgot to input a number and it will retain the previous values in both boxes.

This method adds the same constant error values (positive and/or negative) to all data points in a series. But in many cases, you will want to put an individual error bar to each data point, and the following example shows how to do this.

How to make individual error bars in Excel (of different lengths)
When using any of the inbuild error bars options (standard error, percentage or standard deviation), Excel applies one value to all data points. But in some situations, you may want to have your own calculated error values on individual points. In other words, you wish to plot error bars of different lengths to reflect different errors for each data point on the graph.

In this example, I’ll show you how to make individual standard deviation error bars.

To begin with, enter all the error bar values (or formulas) into separate cells, usually in the same columns or rows as the original values. And then, tell Excel to graph error bars based on those values.

Tip. Optionally, you can fill two separate rows/columns with your error values – one for positive and the other for negative.
Supposing, you have 3 columns with sales numbers. You have calculated an average (B6:D6) for each column and plotted those averages in a chart. Additionally, you found the standard deviation for each column (B7:D7) by using the STDEV.P function. And now you wish to display those numbers in your graph as standard deviation error bars. Here’s how:

Click the Chart Elements button> > Error Bars > More Options….
On the Format Error Bars pane, select Custom and click the Specify Value button.
In the Custom Error Bars dialog box, delete the contents of the Positive Error Value box, put the mouse pointer in the box (or click the Collapse Dialog icon next to it), and select a range in your worksheet (B7:D7 in our case).
Do the same for Negative Error Value box. If you do not want to display negative error bars, type 0.
Click OK.
Adding custom standard deviation error bars

Important note! Be sure to delete the entire contents of the entry boxes before selecting a range. Otherwise, the range will be added to the existing array like shown below, and you will end up with an error message:
={1}+Sheet1!$B$7:$D$7

It is quite difficult to spot this error because the boxes are narrow, and you cannot see all the contents.

admin January 3, 2022

0 236 5 minutes read

A WordPress Commenter
Hi, this is a comment. To get started with moderating, editi...
Georgia Waltrip
Hi, this is a comment. To get started with moderating, editi...
Donald Allbright
Hi, this is a comment. To get started with moderating, editi...
Ivy Torres
Hi, this is a comment. To get started with moderating, editi...
Donald Allbright
Hi, this is a comment. To get started with moderating, editi...

How to add custom error bars in Excel

admin

Leave a Reply Cancel reply

A Refined Seattle Restaurant, Hold the Table Linens

Bosch looking to smart devices to get ahead in the cloud

Nintendo Details Next Miitomo Update

admin

Subscribe to our mailing list to get the new updates!

How to add error bars in Excel: standard and custom

How to add horizontal error bars in Excel

Related Articles

Controlling PWM fans with the Raspberry Pi CM4 IO Board’s EMC2301

Mini-ITX Seaberry adds 11 PCIe slots to a Raspberry Pi

Turing Pi 2: 4 Raspberry Pi nodes on a mini ITX board

Install Python 3.9 on Raspberry Pi OS or Debian 10 (for Ansible or other uses)

Leave a Reply Cancel reply

A Refined Seattle Restaurant, Hold the Table Linens

Bosch looking to smart devices to get ahead in the cloud

Nintendo Details Next Miitomo Update