Custom Error Bars in Excel Charts
by Jon Peltier
Monday, August 30th, 2010
Peltier Technical Services, Inc., Copyright © 2012.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
I’ve written about Excel chart error bars in Error Bars in Excel Charts for Classic Excel and in Error Bars in Excel 2007 Charts for New Excel. Both articles contained instructions for adding custom error bar values for individual points, but judging from the emails I receive, a separate article on custom error bars is needed.
Manually Defining Custom Error Bars
Sample Data and Charts
Suppose we have the following data: X and Y values, plus extra columns with positive and negative error bar values for both X and Y directions.
The chart itself is easy: create an XY chart using the data in columns A and B.
The protocols for adding custom error bars differ between Classic Excel and new Excel. After following the appropriate protocol below, the chart will have custom error bars on each data point, based on the additional columns of data.
Important Note
A single custom error bar value cannot be added to a single data point, and custom error bar values cannot be added to a series of data points one point at a time. If you select a single value for your custom error bars, this single value will be applied to all points in the series.
A whole set of custom error bar values can be added to an entire series in one operation. Put your custom values into a range parallel to your X and Y values as I’ve done with this sample data, then use the manual technique or the utility to add all the values to the chart series in one step.
New Excel (2007 and later)
It is harder to apply error bars in Excel 2007 than in earlier versions. There is no convenient tab on the Format Series dialog. The Error Bar tab(s) as well as the tabs for Data Label, Up/Down Bars, High/Low Lines, and other features have been removed to make them more discoverable, at least that’s what we were told. To discover these features in Excel 2007, select the chart and navigate to the Chart Tools > Layout contextual tab. Click on the Error Bars button, and scratch your head while you try to decipher the options.
Finally, select the More Error Bars Options at the bottom of the list. X (if it’s an XY chart) and Y error bars with initial constant values of 1 are added to the chart series, with the Y error bars selected, and the Format Error Bars dialog is displayed with the Vertical Error Bars tab showing. (If the chart has more than one series, and you had not specifically selected one series, there is an intermediate dialog asking which series to work with.)
This dialog doesn’t look too unfamiliar. But there is no obvious way to switch to the horizontal error bars. We are used to having two tabs, one for the vertical error bars, and also one for the horizontal error bars. Remember that Microsoft made these chart formatting dialogs non-modal, so you can click on objects behind the dialogs. Click on the horizontal error bars to change the dialog.
To assign custom values to the error bars, select the horizontal or vertical error bars, and on the Horizontal or Vertical Error Bars tab of the Format Error Bars dialog. Move the dialog so it does not cover the range containing your custom values, then click on the Custom option button, and click on Specify Value. A small child dialog appears with entry boxes for selection of the custom error bar values. (It was easier in 2003, where data entry took place directly on the main dialog, but we’re not talking about productivity today.)
By default, each field contains a one element array with the element value equal to one. You can enter another constant value, and you don’t need to type the equals sign or curly brackets; Excel will insert them. More likely you want to select a range. Make sure you delete the entire contents of the entry box before selecting a range, or at least select it all, or Excel will think you meant to enter something like the following, which will lead to an error message. The edit box is so narrow, you cannot see the entire expression at once, and it will be difficult to find this error.
={1}+Sheet1!$D$5:$D$13If you want the value to be zero, enter zero. Don’t completely clear an entry box, because Excel will think you simply forgot and it will retain the previous value.
Classic Excel (2003 and earlier)
It’s easy to add error bars in Excel 97 through 2003. Bring up the Format Series dialog, by double clicking on the series, by right clicking on the series and choosing Selected Object, by selecting the series and choosing Selected Data Series from the Format menu, or by selecting the series and clicking the shortcut, Ctrl+1 (numeral one). The dialog has a tab for Y Error Bars, and if it’s an XY data series, there is also a tab for X error bars.
To define custom error bars, click in the + or – data entry box (no need to select the Custom option button, Classic Excel does this automatically), then select the range containing the custom error bar values using your mouse. If you need only one value for all of the points, you can select a single cell, or even type the value you want. This seems redundant, given the Fixed Value option, but this way you can use different positive and negative fixed values or a custom range for one direction and a constant for the other.
This is what the dialog looks like with ranges used to define the custom error bar values.
If you want to leave an error bar off the chart, you can leave the data entry box blank.
Notes
The error bars overwhelm the data. To restore the importance of the data itself, use a lighter color for the error bars. Lighten up the axes while you’re at it.
If any custom values are negative, the corresponding error bar will be drawn in the opposite direction: a positive error bar with a negative value will be drawn in the negative direction.
Programmatically Defining Custom Error Bars
The command to add error bars using Excel is:
{Series}.ErrorBar Direction:={xlX or xlY}, Include:=xlBoth, Type:=xlCustom, _
Amount:={positive values}, MinusValues:={negative values}Values can be a single numerical value, for example, 1, an comma-separated array of numerical values in curly braces, such as {1,2,3,4}, or a range address in R1C1 notation. For values in Sheet1!$G$2:$G$10, enter the address as Sheet1!R2C7:R10C7. Combine both plus and minus in the same command.
In Excel 2007, if you don’t want to show a particular error bar, you must enter a value of zero in this command. In 2003, you can enter a null string “”.
In Excel 2003, the range address must begin with an equals sign, =Sheet1!R2C7:R10C7; Excel 2007 accepts the address with or without the equals sign. Single values or arrays may be entered with or without the equals sign in either version of Excel.
Error Bar Utility
To make it easier to use error bars in Excel 2007 (and in Classic Excel), I’ve built a small utility, which you can download and use for free. It’s found in ErrorBars.zip. This zip file contains two versions, ErrorBars.xls for Excel 97 through 2003, and ErrorBars.xlam for Excel 2007. Install this utility by following the instructions in Installing an Excel Add-In or in Installing an Add-In in Excel 2007.
In Classic Excel, the utility places a new item, Add Error Bars, at the bottom of the chart series context menu. All you have to do is right click on the series and select Add Error Bars.
Despite all the assurances from Microsoft that context menus work the same in Excel 2007 as in earlier versions, you cannot add an item to an Excel 2007 chart-related context menu. What I’ve done instead is to add an Error Bars item to the end of each of the three Chart Tools contextual ribbon tabs. I know the new philosophy of Office is to place a command in only one place in the whole user interface. I prefer the old style philosophy, however, which is to place the command in every place it may be relevant. I never know where I may be when I want to use a command, and some people remember different hiding places than I do.
Whether in Excel 2007 or in earlier versions, click on the added command, and the utility behaves the same. Up pops a simple dialog with four data entry boxes, for plus and minus Y error bars, and for plus and minus X error bars.
If the chart type is not XY, the X error bar entry boxes are disabled.
You can select a range or enter a constant into the entry boxes. Leave a box blank to omit the corresponding error bars.
I hope that this tutorial and the associated utility will make your life easier when working with error bars in Excel 2007.
Related Posts:
- Error Bars in Excel 2007 Charts
- Candlestick Alternative: Individually Colored Up-Down Bars
- High-Low Line Alternatives in Excel Charts
- Callout Labels with Error Bars
- Gantt Chart with Repeated Tasks via Excel XY Chart
- Excel Box and Whisker Diagrams (Box Plots)
Posted: Monday, August 30th, 2010 under Charting Principles.
Comments: 48
Comments
Pingback from Tweets that mention Custom Error Bars in Excel Charts | Peltier Tech Blog | Excel Charts — Topsy.com
Time: Monday, August 30, 2010, 2:16 am
[...] This post was mentioned on Twitter by Jon Peltier, Excel Insider. Excel Insider said: Custom Error Bars in Excel Charts: I’ve written about Excel chart error bars in Error Bars in Excel Charts for Cla… http://bit.ly/cCFxqh [...]
Comment from Michael J.
Time: Monday, August 30, 2010, 7:27 am
You can select the error bar without having to click on it, in excel 2007.
Once the error bars are added to the graph, in the “disposition” tab (Not sure, i have the French version), you can select the error bar you want to edit.
Here’s a pic:
Btw Great tool, as always, thank you for sharing it.
Comment from Jon Peltier
Time: Monday, August 30, 2010, 11:46 am
Michael -
Thanks for the reminder that chart elements can be selected from the Chart Elements dropdown, in the Current Selection group, on both the Format and Layout tabs of the Chart Tools contextual ribbon tabs.
Comment from Matt Healy
Time: Monday, August 30, 2010, 7:54 pm
My personal laptop recently died and its replacement has Office 2010 so now I’m having great fun learning its new interface. It’s like when a grocery store where I’ve been shopping for years decides to rearrange everything: “so WHERE did they move the canned soups now?!”
My wife’s company moved her to Office 2007 a while back, and I vividly remember her expostulations the first time she had to use custom error bars in a graph with Excel 2007! I’m gonna print out and save this clear explanation until I get the hang of Excel 2010.
For me the fun promises to be extended because I’ll switching back and forth between versions as long as my work computer still runs Excel 2003.
Comment from drew
Time: Monday, August 30, 2010, 11:47 pm
wait a darn tootin’ minute…
the interface is supposed to make the feature more discoverable?
i thought the interface was supposed to help you, not hide things.
wonderful read, thanks a lot.
drew
Comment from Jon Peltier
Time: Tuesday, August 31, 2010, 7:06 am
Hi Drew -
The new interface did a great job of uncluttering the window. However, for me, all that clutter was useful stuff. Hiding important controls merely overburdens my short term memory with tedious navigation details, so I am forced to forget what I was trying to accomplish. The interface designers had no ideas about human cognition or about efficiency and productivity.
Comment from Valli
Time: Sunday, October 10, 2010, 2:01 pm
Thanks so much!
Comment from Matt
Time: Wednesday, November 3, 2010, 12:01 pm
Is there a way to display error bars that are specific to each data point? As it is, it seems that the same width of error bars is assigned to all values of a series.
Thanks for this post.
Comment from Jon Peltier
Time: Wednesday, November 3, 2010, 2:05 pm
Matt -
That’s the whole point of the article. Follow the steps and use the custom option, then specify a range, so you get the errors that are listed in that range.
Comment from Saurabh
Time: Monday, November 8, 2010, 10:00 am
Somehow when I try to add custom vertical error bars, the horizontal bars are added by default and I have given up trying how to get rid of them. They are probably standard error bars. If I go to more error bar options, I dont see a horizontal error bar window. Its the vertical error bar window that always opens. Please help. I need to get this poster printed today. If I try removing error bars, everything goes away.
Comment from Jon Peltier
Time: Monday, November 8, 2010, 10:29 am
You need to select the horizontal error bars and press the Delete key. If it’s hard to select the horizontal error bars with the mouse, select another chart feature and use the up/down arrows until the horizontal error bars are selected. Or use the dropdown in the top left of the Chart Tools > Layout or Format tab.
Comment from Stuart
Time: Thursday, November 25, 2010, 12:30 am
Hi,
I am having the same problem as Matt and cant seem to apply error bars specific to a single data point within a series. I have followed the directions, and when i apply a custom std dev value to a single selected data point, that value is replecated for all the data points in that series. If I try and alter another selected data point within the same series to another std dev value, it again changes the values for the entire series.
Basically, can you have different std deviation values (and hence different error bar values) for several different data points within the same series? If so how?
Cheers Mate
Comment from Jon Peltier
Time: Thursday, November 25, 2010, 7:45 am
Stuart -
You cannot apply one custom error bar value to one data point. You have to apply a whole range of custom error bar values to a whole series of data points. Put the different standard deviation values for the whole series in the worksheet (a convenient place is in a parallel range adjacent to or near the X and Y values), then use the utility and apply these error bar values to the entire series.
Comment from Greg
Time: Saturday, December 18, 2010, 5:54 am
And again: how to solve this problem about x-axis labes in bar chart???
I have made a survey, where respondents had to evaluate their agreement with statements (scale from 1-completely disagree to 5-completely agree). I have calculated means and of course, I have made a chart, where vertical axis represents statements, horizontal bars represents values (means)…
now I don’t know how to label x-axis values using text label.
By default, there are labels from 1 to 5 (numbers). I would like to have text labels (“completely disagree”, “disagree”, etc.) instead of numbers (1,2,…). Any suggestion?
Thank you very much.
Greg
Comment from ashok
Time: Monday, January 24, 2011, 9:42 am
dear Mr Peltier,
thanks a lot for such wonderful addon, which i liked and found first when i searched in internet. However, why for me only, while adding error bars it says
RUN TIME Error ’1004′
application defined or object-defined error
from microsoft visual basic editor window and without anything happening.
if there is a solution to it i would be very helpful.
i run excel 2007 in vista.
Comment from Jon Peltier
Time: Monday, January 24, 2011, 9:40 pm
Hi Ashok -
What were you doing just before the error occurred? Had you selected a series then clicked the button on the ribbon? Or had you tried selecting data for the error bars?
Is your Excel 2007 updated to Service Pack 2?
Comment from ashok
Time: Wednesday, January 26, 2011, 8:09 am
Dear Jon,
thanks a lot for your thoughtful insight. yes, i did not have EXCEL sp2 installed. after that installation everything is clear.
and another stuff to note:
if I select the whole graph, nothing happens.
if i select the bars, then only it works. Obvious though.
it is highly simple and effective.
thanks again.
Comment from lenore
Time: Monday, February 28, 2011, 10:38 pm
Hi,
Thanks for the useful site. I would like to add different standard error values to different sections of my line graph. Do you know how to do this? I understand I can choose the custom option and then select ranges of values for the positive and negative standard error. But excel doesn’t seem to know what sections of my line graph I would like to apply each error bar, and that I don’t want one for every point, just specific ones. Any suggestsions?
Thanks,
Lenore
Comment from Jon Peltier
Time: Tuesday, March 1, 2011, 10:43 am
Set up your custom range, with one value per data point. Put the value you want in the cell corresponding to each point, leaving blanks where you do not want an error bar. Follow the protocol for adding custom error bars to your data using this range of values.
Comment from Lenore
Time: Tuesday, March 1, 2011, 2:24 pm
Thanks for the response Jon Peltier, that is helpful. One more question. I calculated standard error for each data point as you suggest. But excel asks for a “positive error value” and a “negative error value”. I find this confusing because I calculated only one standard error of the mean value for each data point (calculated as the standard deviation of my repetitions/square root of my number of repetitions). So why is excel asking for two values? For example if I calculated 4 for my standard error should I put 2 on either side of my data point or 4 on either side as the “positive” and “negative” values?
Thanks very much,
Lenore
Comment from Jon Peltier
Time: Wednesday, March 2, 2011, 12:38 am
Lenore -
Excel asks for positive and negative values because they need not be the same. There may be an asymmetric error, or you may want to show a deviation only in one direction.
If you have calculated an error value, use that value for both positive and negative error bar values. In other words, for custom error bars, use the same range for positive and negative error bars.
Comment from Lenore
Time: Wednesday, March 2, 2011, 2:54 pm
Thank you!
Comment from Lenore
Time: Saturday, March 5, 2011, 3:51 pm
Hello Jon,
As you suggested I make a column of standard error values next to my data columns that I plotted. I left a blank where I did not want a standard error bar plotted (I also tried leaving a 0 with the same result). However then it plots the horizontal standard error bars that I want, but for the ones that I left blank it plots a short horizontal line with no line “cap”. I know you suggested leaving a blank at the data values I don’t want error bars for, however it seems to be still plotting a line there. Any suggestions?
Thanks very much,
Lenore
Comment from Jon Peltier
Time: Saturday, March 5, 2011, 11:50 pm
Lenore -
I think what you’re seeing is not a short horizontal error bar with no end cap. I think it’s a zero-length vertical error bar with horizontal end caps, and it’s these end caps you think are short error bars. This is one of the things about error bars in 2007 which is a little frustrating. Try a blank in the cells for the vertical error bar values.
Comment from louise
Time: Tuesday, March 29, 2011, 10:31 am
Hi Jon,
Thanks for the explanation of error bars! It really helped, I seem to be having the same problem as Lenore,
I have managed to get the error bar correctly assigned to each point within the series by adding the column parallel but my positive and negative values are the same and it wont allow me to enter the same value for my negative as for my positive,
A dialog box pops up saying the formula I have entered contains an error so basically i only get an error bar above the column on the graph, wondering if you can help
Comment from Jon Peltier
Time: Wednesday, March 30, 2011, 2:13 pm
Louise -
What is the formula you’re entering? Are you using the built-in custom error bar value dialog, or the error bar utility I’ve written? If it’s the built-in one, are you sure there it doesn’t begin with the leftover “={1}+”
Comment from Tri
Time: Monday, April 18, 2011, 10:05 am
Dear Jon,
Thank you so much for your useful explanation and tips.
I got problem with inserting error bars one by one as you said, and I tried to install your utility, but it displays something “Visual Basic error” and also requests a password.
Do you have solution for these?
Thank you in advance
Comment from Researcher
Time: Friday, April 22, 2011, 6:06 pm
Hi Jon,
Thanks a bunch for this helpful tutorial!
Comment from Ed
Time: Wednesday, May 4, 2011, 10:29 am
Thank you! Thank you! Thank you~!! Excel 2007 is a pain in ass when it comes to error bars. I do lots of custom stats analysis and even something simple as putting std dev bars on scatter plots is insane in default settings. Your utility is a must have!
Comment from Jaime
Time: Saturday, May 21, 2011, 10:28 am
Thank you so much…u saved me…. :D
Comment from Danièle
Time: Thursday, June 2, 2011, 4:20 am
Well, I am using only custom error bars, so my questions is more about the standard choices excel gives?
I am still suing excel 2003 for work, and someone complained about my using custom error bars whereby I calculate the std errors for each data point, and place then in a separate column, and use that range for my custom error bars. I was told that I should just click on the data, and choose the standard error bar option, and excl would automatically add them. How would excel know where the original data from which I averaged the data values, and to which I am adding the error bars comes from? I have been searching, but there are no real explanation as to how excel manages to guess the standard deviation or the standard error!
As always, each time I read the blog, I am amazed by excel, but not with the error bars…
Thanks!
Danièle
Comment from Jon Peltier
Time: Thursday, June 2, 2011, 6:07 am
If you use the built-in standard deviation, Excel uses the standard deviation of the values plotted, centering the error bars on the average of the values plotted. Looks ridiculous, but maybe it’s easier for some people than using a horizontal line at, say, ±3 stdev.
If you use the built-in standard error option, Excel calculates the standard error (standard deviation divided by mean) of the plotted values, and plots this same value for each point.
Neither of these options provides custom error bar values for each data point.
Comment from Danièle
Time: Wednesday, June 8, 2011, 11:43 pm
I always wondered how excel could ‘guess” what you wnated, or at least “guess” where to get the data from! Now I know!
Thanks!
Comment from Chris
Time: Sunday, July 17, 2011, 6:00 pm
Hi Jon,
I have Excel 2010, and I’m trying to create a macro that graphs the relationship of two columns of data in a scatter plot. Everything works fine until I try to get the macro to record my use of “Format Error Bars” for custom amounts. Specifically, I want each data point in the set to have a vertical positive and negative error bar that corresponds to the values in the third column of my original chart (same values for positive as for negative). I don’t need any horizontal error bars. Do you know of a way that I could make this macro-compatible?
The error in the code is listed as:
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlBoth, _
Type:=xlCustom, Amount:=0
Thanks for your time.
Comment from Jon Peltier
Time: Monday, July 18, 2011, 6:58 am
Chris -
If you scroll down, you’ll see where I included the syntax for adding error bars:
{Series}.ErrorBar Direction:={xlX or xlY}, Include:=xlBoth, Type:=xlCustom, _
Amount:={positive values}, MinusValues:={negative values}You need to include both positive and negative error bar values, as a constant (if they are all the same, a comma-separated array of numerical values in curly braces (such as {1,2,3,4}), or a range address in R1C1 notation.
Comment from Chris
Time: Friday, July 22, 2011, 8:48 pm
Thanks a lot Jon! Your advice was extremely helpful.
Comment from bill
Time: Monday, October 3, 2011, 12:50 pm
Thanks for this blog topic and comments!
Comment from helmer
Time: Wednesday, November 2, 2011, 5:25 am
I have read your very helpful article – thank you for your work on this !!
I have followed the steps ( doing the worksheet layout as you depicted) and it works -but I have one problem :
- The y-value error bars are associated with y-values in numerical order. Say you have y-values Y1, Y2, Y3. The appropriate error values are depicted in numerical order from the largest to the lowest error value, attached to Y1. Y2. Y3 respectively.
How do I remedy this ?
Thank you very much for your time and effort.
Comment from helmer
Time: Wednesday, November 2, 2011, 5:30 am
above comment edited :
The problem was that error bars are depicted numerically by default.
The error bars are not associated with the appropriate Y-values.
I did the worksheet entirely as you described, using x and y values, error values both positive and negative. However, my X values are names and not numbers.
Comment from rohini wankhade
Time: Sunday, November 6, 2011, 10:49 pm
thank you
Comment from mahraz
Time: Tuesday, November 15, 2011, 4:13 am
Hi,
I installed the add in on excel 2007, however, still, the error bar adds to ALL of my points on the graph, is there any way to stop this? I need to add different error bars to different points on my graph.
Comment from Jon Peltier
Time: Tuesday, November 15, 2011, 7:15 am
Mahraz -
Error bar values re assigned one series at a time, not one point at a time. This means you have to put the error bar values into a worksheet range, then select this range in the utility (or in the regular dialog).
Comment from mahraz
Time: Tuesday, November 15, 2011, 12:22 pm
So do I do what you have done on the top of this page with the Y err +, Y err – ?
I am doing a scatter graph and I have three points. I need to assign three different ranges to each point…I have spent a lot of time on this, I still don’t really understand how to do this.
I would really appreciate it if you could please explain a bit more? like a step by step process?
thank you!
Comment from Jon Peltier
Time: Tuesday, November 15, 2011, 1:38 pm
Mahraz -
Yes, you have to set up your data like my example at the top. Use my dialog to enter the data in columns C, D, E, and F for the selected series.
Comment from mahraz
Time: Tuesday, November 15, 2011, 2:05 pm
I only have Y err + and Y err -. I have added these, and I have a graph for my X and Y column values… but when I highlight X, Y, Y err + and Y err -, and then graph, no error bars show up and my scatter graph looks a bit weid….
When I have my X Y graph, what do I do from there to add the Yerr + and Y err – as error ranges?
sorry if i’m asking the same question over again….im veryyyy confused.
Comment from Jon Peltier
Time: Tuesday, November 15, 2011, 2:53 pm
As the article states, you have to make your chart using the X and Y data (in columns A and B in the example). Then you add the error bars, using the built-in interface or using the utility I’ve provided.
Comment from Robert
Time: Wednesday, December 7, 2011, 11:47 pm
Hello Jon.
Thank you very much for this blog-it assisted me in dealing with issues regarding placing error bars of separate data points in a series using Excel from Office 2010.
In turn, I thought I could constructively critique one of your posts mentioned above. You mentioned that Excel calculates the error bar by dividing the standard deviation by the mean. I would like to offer a correction to this: the standard error is typically calculated by dividing the standard deviation by the square root of the sample size. I am assuming this is how Excel automatically calculates the value for the error bars.
I always calculate my error bars and manually enter the values when I add them to Excel-made graphs. Please correct me if I’m wrong about the automatic calculation of the error bars within Excel, as I know many other scientists who use Excel to express their standard error of the mean values using this error bar option.
Thank you!
Comment from Jon Peltier
Time: Thursday, December 8, 2011, 10:38 am
Robert -
You’re right about the standard error. I don’t know what I was thinking, but I’ll update those other posts. I can’t find the post where I misstated the definition of the standard error; do you recall which article it was? Thanks for the heads up.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.