Custom Error Bars in Excel Charts

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. You cannot add custom error bar values to a single point in a chart. However, you can individual custom error bar values to all points in a plotted series. You need to put all of the individual error bar values into a range of the worksheet. I usually put these values in the same table as the actual X and Y values

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 data is set up so that, for example, cells C2 and D2 have the values for the positive and negative horizontal (X) error bars for the point defined by X and Y values in A2 and B2. Cells E2 and F2 have the values for the positive and negative vertical (Y) error bars for this point. The series is plotted using all the data at once, with X in A2:A6 and Y in B2:6. The error bars are also drawn using all the error bar data at once: C2:C6 and D2:D6 for horizontal and E2:E6 and F2:F6 for vertical.

Error Bar Data

The chart itself is easy: create an XY chart using the data in columns A and B.

XY Chart That Needs Error Bars

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. This chart shows just the Y error bars, to show clearly that each point has custom values different from other points:

XY Chart With Custom Y Error Bars

This chart shows the X and Y error bars:

XY Chart With Custom X and Y Error Bars

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. Error Bar Commands on the Ribbon 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.) Custom Error Bars dialog 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, that you cannot see the entire expression at once, and it will be difficult to find this error.

={1}+Sheet1!$D$2:$D$6

If 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. Classic Excel Format Series Error Bar Dialog 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. Defining Custom Error Bar Ranges in Classic Excel 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. XY Chart With Error Bars 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. Chart Series Context Menu Showing Add-Error-Bars Command 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. Chart Tools Ribbon Tab PTS-Tools/Error-Bars Command 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. Error Bars Utility XY Chart Dialog If the chart type is not XY, the X error bar entry boxes are disabled. Error Bars Utility Line Chart Dialog You can select a range or enter a constant into the entry boxes. Leave a box blank to omit the corresponding error bars. Error Bars Utility Populated Dialog I hope that this tutorial and the associated utility will make your life easier when working with error bars in Excel 2007.

Peltier Tech Chart Utility

Comments

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

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

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

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

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

  6. Thanks so much!

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

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

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

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

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

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

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

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

  15. 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?

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

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

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

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

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

  21. Thank you!

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

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

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

  25. 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}+”

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

  27. Hi Jon,

    Thanks a bunch for this helpful tutorial!

  28. 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!

  29. Thank you so much…u saved me…. :D

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

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

    Built-In Error Bars, Standard Deviation Option

    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.

    Built-In Error Bars, Standard Error Option

    Neither of these options provides custom error bar values for each data point.

  32. I always wondered how excel could ‘guess” what you wnated, or at least “guess” where to get the data from! Now I know!
    Thanks!

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

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

  35. Thanks a lot Jon! Your advice was extremely helpful.

  36. Thanks for this blog topic and comments!

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

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

  39. rohini wankhade says:

    thank you

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

  41. 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).

  42. 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!

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

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

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

  46. 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!

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

  48. Jon,
    I cant find a way to put error bars in that change from data point to data point..pls help

  49. Daniel –

    Did you put the individual error bar values into a range of cells? Did you use the Custom option to define the error bar values? Did you select the entire range of error bar values that you entered into the worksheet? What value do the error bars have, if not the range of different values?

  50. I’m having the same problem as many others who’ve commented. My data are formatted as you explain with my SE series in a corresponding range. I enter the SE range as described in your instructions for custom error bars. I get error bars but they are not at all the values in the range. It seems like Excel is selecting the major unit value from the y-axis.

    I can’t tell from reading the comments if people actually fixed this problem after reading your response or if they gave up as I’m about to.

    Also, I downloaded your add-on utility and installed it. It shows on the chart options tabs but nothing opens when I click on it. I have Office 2010.

    I have benefitted many times from your posts and hope you’ll be able to help on this one, as well. Thank you.

  51. Kathy –

    You have to select “Custom”, then select the ranges in the dialog, then not select a different option.

    When using the Error Bar Utility, you have to select the series that you want to add error bars to, then click the button on the ribbon. Without selecting a series, the utility doesn’t know where to add the error bars, so it bails out.

  52. This issue has been brought up before but I still don’t understand the answer! My problems is with applying separate error bars within a series. You state above:

    “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.”

    If there is no way to apply a separate error bar to each data point with in a series, are my only options are to create single data points so there are no series (which I’m not sure how to do) or find a earlier version of Excel? I am edited a graph I created in an early version of Excel where I could apply different error bars to different data points within a series.

    Thanks so much for your help. Your instructions on Excel are the best out there!

  53. Sue –

    The very next paragraph begins “A whole set of custom error bar values can be added to an entire series in one operation.” It then describes how to put custom (different) error bar values onto a series of data points.

  54. Jon-

    FINALLY figured it out. Sorry for not reading carefully enough! Thanks for your help.
    Sue

  55. Jon, I am so excited to add real error bars to my graphs. I followed your instructions, and everything worked perfectly until I actually tried to use it, and then I am asked for a password!

    What do I do now?

  56. Kathy –

    The password prompt happens because some add-in (not mine) is misbehaving. It isn’t even always an add-in for Excel. The prompt usually does not interfere with operation of my program, only with closing Excel, and you just need to click Cancel. When I have a chance I’ll write an article about what causes it and how to get around it.

  57. Thanks for this. Excel 2010 doesn’t half make it hard to put the standard error on a graph. What is particularly dangerous is that the default value it puts on a summary graph (e.g. a column chart, which is when you are most likely to want to use a standard error) is completely invalid (I presume the default it is the standard error between the different summary datapoints). Also to define the values to use with the custom standard error you have to select ‘specify value’ (rather than values) which suggests that you can only select on value for the entire series, which is where I think some others have understandably got confused.

  58. Jon

    Simple request. I want to put 95% confidence limits around some bar chart means. I cannot believe excel (2007) needs me to calculate the Confidence Limits in separate columns in order to then plot them. Is there no excel add in that provides a drop down tool for CL’s in the same way as it automatically plots 5%, SE, SD around the means? It is only a small step to plotting CL’s automatically. Surely it can be automated? What am I missing?

  59. Every branch of statistics must have its favorite way to indicate some kind of largest acceptable or largest expected deviation from its favorite calculated middle. If Microsoft tried to include them all, development in other areas would grind to a halt. Sure, 95% CI seems common enough, but they had to draw the line somewhere.

    Personally, I prefer to calculate my own error bar values. Any parameters entrusted to Excel come from a black box. I can look in the column of cells to make sure it’s doing what I expect. Of course these things could be automated, and probably they have, but I don’t know of a specific place you could look.

  60. Very helpful! Thanks!

  61. Most helpful, Thanks a million!

  62. Hi Jon, I have spent many an hour reading books in the library and Computer outlets ,looking for some mention about Error bars and how to progress from the custom button. But to no avail . Why didn’t I look on Google and your Blog, who knows ? It was so good to be part of a community . Although by the time I had read the complete thread I was a little overwhelmed. Onwards into an uneasy future. Best regards Graham.

  63. Jon, thanks for the Excel 2007 Error Bar utility tool. After reading your detailed description of options, this was the only way I found to add horizontal error bars to a particular series on a simple Excel 2007 chart.

  64. Thank you for your clear explanation which helped solve my problem!

  65. Hi,

    I’ve read this page over and over again but still can’t solve my problem (and unfortunately can’t download you’re tool either because its blocked here at work). I’m having the same problem as Sue E – I’ve got a column of my values, the next colum of the lower confidence interval value, and the next column of the upper.
    I go to custom, specify value and highlight the relavent columns as a range in the ‘positive error value’ and the ‘negative error value’. But Excel still doesn’t seem to use these and i end up with a wide confidence interval on each point that goes into the negative (even though none of my confidence interval values do). Please help, i have read the page over and over again, but somehow still can’t figure it out. I’m working in Excel 2007 by the way. Thanks.

  66. Claire –

    You need to enter the values that represent the lengths of the error bars, not the values at the ends of the error bars. That is, half the width of the intervals, not the upper and lower values the mark the boundaries of the confidence intervals.

  67. Ahhh i see. Thanks so much for your quick response.

  68. Thank you so much for this. I have just spent an hour and a half trying to do this with no success. I even tried to do it in R (with no success) because I was getting so annoyed with Excel! With your article it took me two minutes. You are a life/time saver!

  69. I have been horribly frustrated. I want to get ONE error bar for ONE bar which is the average of a series of data points.

    I have, say 10 bars. Each bar is an average of a separate column of data. (So, 10 bars, 10 columns of data. I want 10 different error bars: for each set of elements that make up each bar.)

    But when I drop in an error bar instead all I get is the error bar for the value of all the columns in the same series. And each bar is counted as a single data point (how useless is THAT?). If I make each bar a separate series, I get no error bar at all; (it’s treating it as if I had only 1 data point even though the single bar is an average of a whole bunch of different data points — for which I want the standard error.)

    My very lengthy and very clumsy workaround is to:

    A.) Make every bar a separate data series (ick).
    B.) Independently calculate the error bar using =STDEV(Data Series)/SQRT(COUNT(Data Series)) for (Standard Error) for each and every miserable data series that makes up each and every bar. (Around 3000 bars of data — so far . . .)
    C.) Manually cut/pasting each and every result in each and every bar of data (double-ick).

    Not only does this take forever+1, but it is prone to the usual cacophony of cut/paste errors and typos. And the paper my sheets are supporting is going into the teeth of the most implacable, virulently hostile peer review this side of Hell. If I don’t get it RIGHT, they are going to dangle me by my toesies — and that’s just my co-authors.

    There has GOT to be a better way! Right? Right . . . ? RIGHT?

  70. Great article, thanks Jon! Aside from the ribbon itself, MS have done an abysmal job with custom error bars (hence my stumbling on your article) and in Excel2010 it seems to have some bugs. Minor annoyance is the default ={1} does not get replaced with range selection, so that seems to first need to be deleted. More significant is that sometimes it won’t take a custom range at all (stays stuck on default/won’t accept custom range), and does not seem to display the new range once entered so is not simply manually editable?! Maybe it doesn’t like my range, but I don’t see why it shouldn’t and there is no feedback/error trapping. Maybe we should all roll-back to 2003. I liked it so much better and still regret the ‘up’grade.

  71. Evan –

    I hope the 3000 bars are not all on the one chart!

    So in your example, you have 10 columns of data. Put the bar category labels in A1:J1.
    Put the data into A:J starting in row 4. So if there are 25 points in each, this will fill A4:J28.
    Put the average calculations (which you are plotting) in A2:J2.
    Put the error bar calculations in A3:J3.
    Make a bar chart using A1:J2.
    Using the approach outlined in this article, add custom error bars to the data, using the range A3:J3 for both positive and negative error bars.

  72. Thanks for your reply. I will figure out your suggestion and try it out and let you know how I fare.

    (The 3000 bars are all in one workbook, spread over ten separate spreadsheets. 10 charts per sheet, 30 bars per graph.)

  73. Jon,

    I wonder whether you or someone else having the same issue that I have on Custom Error Bar on Excel 2010. I can only specify value on the Custom Error Bar and I can not specify a cell reference at all(like =Sheet1!$A$1, which I can do that on Excel 2003!
    My excel 2003 using cell reference like Sheet1!$A$1 will work correctly when open with Excel 2010 but I just can not modify it any more unless I am in Excel 2003(and I am going to lost access to Excel 2003 very soon).
    Using VBA inside Excel 2010 or using VBScript to modify the Custom Error Bar field will give me error just like other fields that won’t allow formula or cell reference.
    Just wonder whether you or anyone else having the same issue and know any work around(or MS certain patch fixing the issue)? Thanks!

  74. You have to make sure to delete the contents of the range entry boxes. Even though they are totally selected, when you select a range, the range is appended to the box contents. The default is

    ={1}

    If you don’t clear the box, but just select Sheet1!$A$1, the box now contains

    ={1}+Sheet1!$A$1

    which is illegal. But because they made the dialog so infuriatingly small, you can’t even see all of “Sheet1!$A$1″ at once.

  75. Peter Lancashire says:

    I have installed the add-in into Excel 2010. It works fine. Thanks. The buttons appear in the ribbon only for standard charts, not for PivotCharts. If you are updating the add-in, a nice addition would be a way to step through all the series in the chart in the dialog box itself.

  76. Peter –
    The buttons appear on the Chart tools tabs, but I neglected to also put them onto the Pivot Chart tabs.
    I’m thinking about adding the Error Bar tool to my utility. It will be available for any chart, and a series selection dropdown on the dialog would certainly make it easier to add error bars to multiple series.

  77. Great article. I am plotting the 95% CIs around odds ratios. I am able to do this by selecting the range of values that comprise the upper and lower limits. However, the error bars are not reflective of the numbers on the Y axis. For example, I have an OR = 1.06 with a Lower Limit if .50 and upper limit of 2.28. The bar looks like it ranges from .75 to 3.75. Any ideas on this?

  78. The values used for the error bars are the lengths of the error bars, not the values at the endpoints of the error bars.

  79. Thank you! I just needed to compute the differences and it worked great.

  80. Hallo-
    I’m having the same problem as mentioned by Matt and Stuart, and I have applied the custom error values in series as you instructed. What I get are error whiskers who’s values do not seem correct- higher or lower by anywhere from 50-500 units. when I check which cells are supplying those values, they are correct, and entered in the same order as the original data points. I cannot find a pattern that would indicate that and error like error values for series c being applied to series d, for example. this is so frustrating

  81. Rachel –
    Matt and Stuart were trying to add error bars to a single point, not to an entire series.
    Are you using the desired lengths of the error bars for your custom values? Are you adding the error bars to the corresponding series?

  82. Is there any way to remove the brackets that appear at then end of error lines? It’s some what annoying having to increase the size of my data points to cover those lines.

  83. Kendal –
    Format the error bars, and select the option without end caps:
    Error Bar End Caps

  84. Hi Jon,
    This looks like an awsome utility.
    However, after installation under Excel 2013 I do’nt see it at the right hand side of “Move Chert Location”.
    In VBA I see the project PTSErrorBars, a password is required.
    Thanks,
    Guus

  85. Guus –

    I’d noticed that once before, but never really looked at it. But I checked it out today, and thanks to a little help from my old colleague Ron de Bruin, it’s now appearing in Excel 2013. Delete the old version and download it again.
    http://peltiertech.com/Excel/Zips/ErrorBars.zip

    By the way, you don’t need the password to use the utility.

  86. i would just like to say thank you. Having started with a dos package called FigP and gone through early excel I have been boring family and friends about how putting custom error bars on charts has became an increasingly embedded feature from whatever version of excel it was (2007?). Have been through 2010 it was just as difficult but at least largely unchanged. I have just got 2013 and its no easier and there is the usual moving the feature to a different tab just for a bit of fun. BUT downloaded your utility and you have answered all my dream, its easy now!
    Thank you again

  87. Jon, too bad your Utility does not work on a MAC – I tried to install it and gave me an error about iy having a macro.
    I am racking my brain here…
    Great Tutorial though!

  88. Mark –
    What was the exact error message? I’ve never tested that add-in on a Mac, but I don’t know offhand what would make it fail. If you’re installing the file as an add-in, it shouldn’t warn you that it has macros, because add-ins need code to work.

  89. Jon, I tried both files (xla and xlam) – here is the error message – http://www.primaveraphoto.com/Add-in-error-for%20mac.png

    Error Message

  90. Up in Error Bar Utility – the links give a 404 error
    “Installing an Excel Add-In or in Installing an Add-In in Excel 2007.”

    I got Excel running on my virtual machine and went to install the Error Bar Utility and it asked for password?
    Almost have it working :)

  91. Mark –
    What version of Office for Mac are you using? VBA worked in 2004, and works in 2011, but Microsoft suspended VBA support in Office 2008. This message looks like it relates to Office 2008.
    If you are using 2011, was VBA not activated when Office was installed?

  92. What else is installed for Excel on your VM? You don’t need a password, but sometimes interference with other add-ins and third party programs makes VBA think it needs access to the VB project of my add-in in order to shut down Excel properly. It’s a spurious message which Microsoft has given us developers no means to address.

  93. Jon, I sent you an email earlier – I went to my office and tried your add-on in the new Excel – It worked out but now cannot get custom error bars for each point. Please refer to the file I sent you via email.

    I am not too worried about getting it to run on my home VM, but when I did use it I received the following error http://www.primaveraphoto.com/compile-error.png

    Like I say I am not too worried about getting it to run at home.

    Mark

  94. Mark –
    Which add-in gave you this error (xla or xlam)? I haven’t tried either in Excel 2000; I don’t think I even have 2000 any more. The xlam freshly downloaded from the blog has just worked for me without issue in Excel 2010.

  95. Hi Jon

    Can we use range name as Error Value please?
    Does not seem to be able to in my Excel 2007.

    KC

  96. KC –
    I just tested in 2010 (I don’t have 2007 on this machine). I named a range “plus” for the positive error bar values and “minus” for the negative values. I applied error bars to a set of data, added error bars, then formatted the error bars to use custom values. I tried entering the Names in the dialog (e.g., “=plus” and “=minus”), but that did not work. I had to prefix the sheet name in front of the Name, that is, I needed to use
    =Sheet1!plus
    =Sheet1!minus
    for the references to the positive and negative error bar values. With this minor modification, it worked fine.

  97. Hi Jon

    Thank you. I just want to confirm it works.
    I should kick myself for clearing one of the value boxes, causing Excel to retain previous value.
    Problem solved please.

  98. Hi Jon,

    Apologies if this is an obvious fix, but here’s my problem: I can add custom error bars to my data series just fine (I have a bar graph with 6 bars on it, paired into 3 major categories each), but the error bars for the last two bars (both in the last/third category) don’t show up–all I get is a little selection dot over the top of each graphed bar ( the bars representing a data set), but no actual error bars. How can this be happening, when my SEMs do NOT equal zero and I’m inputting the values as directed–the error bars work for the other data, after all? I am working in Excel 2013.
    Thank you for your help!

  99. Liz –

    If you post the workbook (e.g., Dropbox) I can take a peek.

  100. Hi Jon,
    Is it possible to programmatically get the ranges used for error bars (not set them)?
    Thanks a lot!

  101. Sancho –

    This isn’t exposed to VBA. I’ve thought about trying the dreaded “SendKeys” to select the comments of the input ranges in the dialog, then copy and extract the comments if the clipboard. But such thoughts less to migraines.

  102. Thank you Jon!
    I would face the SendKeys way only if benefiting significantly from such automation.
    BTW, did you ever use SendKeys for “similar” purposes? Is it expected to succeed?
    Thanks

  103. Well, knowledge of the ranges used for custom error bars would be a significant benefit. But SendKeys is a significant source of pain.

  104. how to calculate positive and negative value of standard error bar excel?

  105. Standard error is standard deviation divided by square root of sample size.

Subscribe without commenting

Trackbacks

  1. [...] 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 [...]

  2. […] Excel Custom Error Bars http://peltiertech.com/WordPress/custom-error-bars-in-excel-charts/ […]

  3. […] Excel Custom Error Bars http://peltiertech.com/WordPress/custom-error-bars-in-excel-charts/ […]

  4. […] Now add in custom horizontal confidence intervals using the last two columns. (more info on adding custom error bars in Excel) This, more or less, gets you to the place where previous […]

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.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites