Microsoft has made a number of changes to how you use error bars in Excel 2007. I wrote an article some time ago, which covered how to use Error Bars in Excel Charts, for versions 97 through 2003. Now it’s well past time for an update.
Warning: This is a long post. It contains a lot of pictures though to keep you entertained, and near the end there’s a cool utility you can download and use for free.
Review of Error Bars in Classic Excel
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.
From this dialog you can assign values to the error bars, such as a fixed value, a percentage of the data point value, a couple of statistical measures, and custom values from a worksheet range. You can also assign the direction of the error bars (plus, minus, both, or neither). One nice feature is that the custom error bar value selection controls are right on the same dialog as everything else.
To format error bars, you first select them, then bring up the dialog with a double click, a right click, the Ctrl+1 shortcut, or the Format menu. In the chart below I have clicked once, which selects both X and Y error bars in an XY chart.
The dialog has three tabs. Any changes you make on the Patterns tab affects both X and Y error bars.
The other two tabs allow you to reapply error bar values.
Rather than selecting both sets of error bars in this chart, I have clicked once to select both X and Y error bars, then again to reduce my selection to just the Y error bars.
The Format Error Bars dialog now only has a Patterns tab and a Y Error Bars tab. If you change any format on the Patterns tab it only affects the error bars you selected before opening the dialog.
Changes to Error Bars in Excel 2007
It is harder to apply error bars in Excel 2007. 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 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. There is no obvious way to switch to the horizontal error bars. We are used to having not just a tab for the vertical error bars, but also one for the horizontal error bars.
Hmm, oh yeah, modeless dialog. Click on the horizontal error bars in the chart, problem solved.
Unlike Excel 2003 and earlier, you cannot select both X and Y error bars in Excel 2007. This means that if you want the same formatting to both, you have to apply it twice, and the F4 function key shortcut for Repeat Last Action only repeats the last single itty bit of formatting (if you change line color, thickness, and dash type in that order, for example, only dash type is repeated).
An inconvenient part of the user interface for error bars in Excel 2007 is that you need to click a button to enter custom error bar values, and this pops up a small dialog. This dialog is modal, so if the Format Error Bars dialog was partially covering the range you want to use, you have to clear the small child dialog, move the main dialog, and bring up the Custom Error Bars dialog again.
Custom error bars are described in more detail in Custom Error Bars in Excel Charts.
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
={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.
For all of the problems, using error bars in Excel 2007 isn’t completely terrible. You have some nice formatting options available to you. One that appeals to me is an alternative to the regular end cap appearance of the error bars.
You can change to the end-cap-less variation, then format the error bar line style so they end with an arrow head.
I don’t remember if this is the most useful, or the only useful, new formatting feature available in Excel 2007 charts.
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 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.
I hope that this tutorial and the associated utility will make your life easier when working with error bars in Excel 2007.
Colin Banfield says
Jon,
Another great post, and thanks for the handy utility. I was baffled by the error bar logic in Excel 2007 at first and still have my reservations, but at least I figured out some of the logic. The only reason I could think for not having X and Y error bars in the same dialog has to do with the modeless dialogs i.e. select a chart element when a dialog for another element is visible and the dialog box changes to reflect the options for the newly selected element. Using this logic, X and Y error bars are independently selectable elements and therefore display different dialog boxes. However, the whole thing isn’t initially obvious.
I was also surprised when I first selected the “More Error Bar Options” button. The last thing I expected was for Excel to make a choice before I did, and here again I suspect that Excel has to use a chart selection to know which dialog box to display – so it creates and selects the error bars and displays the error bars dialog box. However, this approach breaks down for XY charts. For XY charts, you get both sets of error bars even if you don’t want one set. Invariably, I just want horizontal bars and have to endure the irritation of deleting the vertical bars.
One thing about your post though – I don’t really agree with the logic for placing error bar buttons in all of the contextual tabs because using the same logic, all of the layout options should be in the design and format tabs and vice versa. This would be a confusing (no to mention totally redundant) mess. Besides, once an element is placed in a chart, right-clicking displays the format option for the element in the context menu (of course it would be nice to have an option to create error bars from the context menu).
I think that the way the designers implemented the chart contextual tabs makes it not immediately obvious where to find stuff. For example, the “Layout” tab should be called “Options” because the tools in this tab are all chart options (objects that you can add to or remove from the chart). Error bars would logically be in this tab. In my view, “Layout” makes no sense at all, and the confusion is compounded by the “Chart Layouts” group in the Design tab. Why aren’t “Chart Layouts” in the Layout tab? I pointed out the potential for confusion (caused by the chosen nomenclature) to Microsoft during the Excel 2007 beta cycle.
BTW, the philosophy of placing a command in only one place in the whole user interface isn’t absolute. For example, Sort and Filter appears in the home tab and in the data tab. Refresh and some of the Connection options appear in the Data tab and in the contextual tabs of PivotTables and Tables.
Jon Peltier says
Colin –
Thanks for the guest post :-) You’ve made some good points.
In 2007 Microsoft decided to move many controls off the Format Series and Chart Options dialogs, where they make sense, and where they’ve been located since the dawn of time. This makes it more difficult to find the controls, especially if you “know” where they are.
Even after one learns where these controls have been relocated to, the ribbon tab graced with the desired control is likely to be hidden. A further detriment to the ribbon, as you’ve pointed out, is that the names of the Layout and Format chart tabs are not very descriptive of the controls on the tabs. I remember being asked for my personal feedback on the charting user interface, and it was so alien that I couldn’t conceive of how it could have been improved.
The modeless dialogs have created more problems than they may have solved, though I’m sure this seemed like a good idea at the time. Not being able to F4 my way through a long string of repetitive actions is much much more frustrating than having a modal dialog.
My personal choice would have been to place my error bars control (a) on the chart series context menu as I’ve done in Excel 2003, (b) on a persistent chart toolbar which I haven’t done in 2003 (but I could!), and (c) on the Format Series dialog where it is in Excel 2003. Unfortunately none of these options is possible in Excel 2007. The best I can do for my own use is to display the button in all the charting tabs. Anyone who doesn’t like that is welcome to edit the RibbonX code in the downloaded workbook. Hey, good idea for a follow-up post.
Colin Banfield says
<<Thanks for the guest post :-) <<
Lol! Did the post sound like a person who needed to get some stuff off his chest? I had to come up with some theories on Excel 2007 chart logic for my own sanity.
Jon Peltier says
I think a lot of us have gotten a few good rants about the 2007 redesign. I know I’ve had more than my share.
jatzhang says
Hi Jon,
Thanks for the utility, I’m a keen visitor here from China. :-)
There’s a mistake when I selected the plus&minus ranges, the VBA stopped at Function GetAddress with an error message “Can’t find project or library”. Is there anything to notice for installation?
Jon Peltier says
Hi Jatzhang –
In the VB Editor, go to the Tools menu > References, and look for any reference that says ‘MISSING”. Uncheck it, save the file, and see if it works.
I may have to hack at the utility a bit. When Office 2003 SP3 came out, it seemed to mess with the RefEdit control, which is used in custom dialogs like this one to let the user select a range. Most of the time things work okay, but on a small percentage of computers the bugs are persistent. A colleague of mine completely wiped his hard drive and reinstalled everything, and solved his problem. I’m looking for a less draconian solution.
John says
Thank you. Quite a read this article.
Chad says
After I added in the Error Bars tool, I get the following error when I try to use it. “Cannot run the macro ‘ptsErrorBars’. The macro may not be available in this workbook or all macros may be disabled. After enabling all macros and trusting all sources, it still does not work. Any suggestions?
Jon Peltier says
Hi Chad –
Sorry for the problems. A few questions:
What version of Excel are you using? Did you use the appropriate version of the add-in for the Excel version?
Did you install the add-in per procedures in Installing an Excel Add-In or in Installing an Add-In in Excel 2007?
How are you trying to run the utility?
What is the entire error message? “PTSErrorBars” is the name of the VB project represented by the add-in.
Daniel says
Hello,
I hope it is politically correct to point out an alternative to the utility introduced here: The XL Toolbox that I developed and published on Sourceforge.net.
This might be interesting for the audience as I took X error bar functionality one step further for XY charts that contain more than one data series. The XL Toolbox will automatically determine the best combination of ‘up’ and ‘down’ error bars so that there is as little overlap as possible. This is the preferred way in the life sciences (where I work).
Error bars can either be added in a fully automated mode that locates the error data beneath the chart series’ data, or the user can indicate a custom range of cells, similar to the addin introduced on this blog.
http://xltoolbox.sourceforge.net/errorbars.html
Daniel
Jon Peltier says
Daniel –
I welcome any relevant discussion, which includes mention of similar approaches and utilities. Your error bar approach is interesting, more specific than mine because it (a) in auto mode makes some assumptions about the location of the error bar value, (b) only creates Y error bars, and (c) selectively hides some error bars to avoid overlap.
The rest of your toolbox is interesting, too.
Jake says
Jon, Thanks for the really helpful utility. Just a few things.
1. Any chance (since I just generate my errors using stdev() ) that there could be an option to do both the positive and negative errors at the same time as they are the same values for me.
2. Do you think there is an easy solution to create a chart type that would automatically ask for a range to add as the y-error bars for a whole set of data at once?
Thanks,
Jake
Jon Peltier says
Jake –
1. This approach could be adjusted to use a single range for positive and negative error values. I’ve made a note, and when I get some more tuits around here, I’ll fix it up.
2. This is starting to get specific, but it could be done. I’ve done similar tricks where you would select a range that’s twice as large as the Y values, with half the range being Y values and half being something else. So error values is not a big stretch. Or there could be three range boxes: X, Y, and Error.
Jake says
Jon
1. Thats great
2. Exactly what I was thinking would be the easiest solution..something in that vein.
Thanks again.
Jake
Pernille Madsen says
I officially love you. This is great! Awesomeness.
Jon Peltier says
Aw shucks,
Pamela says
Help!
I hope it is not too late to get your help. I don’t understand how to make individual error bars in my 2D column chart. When I ask Excel to input error bars with standard error, it uses the values in my table but my table is made up of the final averages that I want to graph. I need the error in my readings (which are inputted in the same spreadsheet) that I actually averaged. So I thought to manually input my error bars but Excel 2007 is grouping the columns for each series so the error is the same for those columns. I have 4 horizontal categories and 2 series (therefore 8 columns) and I want to make each column have an individual error bar. I think that should be an obvious option but I can’t find it anywhere. Can you help?
Jon Peltier says
Pamela –
You can get error bars with individual values for individual points if you use the “custom” option for the error bar value, and select a range. In Excel 2007 it is so painful to deal with error bars, that I created the error bar utility I described in this post. When you open the dialog, simply use the entry boxes to select the ranges that contain your positive and negative error bars, and they’ll appear on the points.
sudha says
Hii sir,
can u pls help me out how to calculate X error and Y error….from the existing data of X and Y…
Jon Peltier says
You should search for a general description, such as Standard error (statistics) – Wikipedia, the free encyclopedia, then if necessary search on more specific keywords.
Annette says
Thanks, Jon! This is a helpful review of error bars in Excel 2007.
I’m struggling to make a graph where the error bars are different for every data point; however, every time I format the error bars–even with just one data point selected–it formats all of the error bars. Is there some way to ungroup the error bars to edit them separately? I’ve seen this question asked several other places, but haven’t found it answered. It’s a pretty standard thing in certain scientific charts, so I’d be surprised if Excel didn’t support it somehow.
Thanks!
-Annette
Jon Peltier says
All error bars within a set (series) have to have the same formatting. The same color, line thickness and style, etc. An XY series can have differently formatted X and Y error bars.
If you want to format error bars differently, you need to add a series to the chart for each different format. You can hide the extra points (no marker or line) if necessary.
The error bars do not need to show the same value. I describe this under “Custom Error Bar Values” in Error Bars in Excel Charts.
Avi says
I use your app for Y error bars in a X-Y plot. At the end I get runtime error 1004: The specified dimension is not valid for the current chart type. Never the less, the error bars seem to be OK.
Jon Peltier says
Avi – What chart type are you using, and in what version of Excel?
Jon Peltier says
Avi sent me the file, and I reproduced the error. Come to find out, I had applied a workaround in Excel 2007 SP1 to avoid errors when an error bar value was unspecified (i.e., the user did not intend to plot it). In 2003 and earlier, such an unspecified value was ignored, but 2007 doesn’t ignore any of it.
The workaround I used in Excel 2007 SP1 does not work in Excel 2007 SP2. I figured out something else, which works in both versions, and the download file has been updated as well.
Avi says
Are you sure the file has been updated? It works the same and has the same problem.
Jon Peltier says
Pretty sure. I just checked the code, rezipped it, and reposted it. Rename the existing file on your computer to avoid accidentally using the old version. Give the new one a try. If it still doesn’t work, let me know.
Naseer says
thanx a lot it was a real help!!
vinay says
Thanks a ton for the error bar add in…
cheers
Scott says
Hi Jon, My question is the same as Annette’s on July 19. I see you answering this question on a number of different forums, but each time your answer it the same- that you can do it in “custom”, but that only allows you to do it to the series. The specific question is for individual points in a series. You attempt to answer that above, but don’t go into enough detail, and you send us to the same tutorial you wrote (which is very well done), but it doesn’t answer the question about individual points. Applying specific error bar values to different bars in the same series could be done in previous versions, but apparently it can’t be done in Excel 2007. Could you give a step by step on how to design a graph so that one could apply different error bars to each bar in a bar graph? I tried it, and discovered that one basically has to create a different series for every data point in the graph. Very time-consuming and it results in a lot of other problems as well. I can’t believe Microsoft overlooked this feature. If I’m not understanding your response to Annette, could you be a little more specific. Thanks for your time.
Jon Peltier says
Scott –
A custom error bar value could never be applied to an individual point in an Excel chart series. The custom setting allows you to put the individual error bar values into a range, and select the range of error bars you’ve generated, and apply this range to the whole series.
Joe says
Hi Jon
thanks for the great tutorial as i’m desperately in need of a thing like this. I have a question similar to the ones above and I couldn’t figure it out: so i downloaded your zipfile and everything worked pretty good, then when i wanted to apply the individual error bars (im using 2007), it shows the following in VB
Function GetAddress(rRange As Range) As String
Dim sRange As String
Dim sAddress As String
sRange = rRange.Address(External:=True, ReferenceStyle:=xlR1C1)
sAddress = Left$(sRange, InStr(sRange, “[“) – 1) & Mid$(sRange, InStr(sRange, “]”) + 1)
GetAddress = sAddress
End Function
so just wondering what to do? :-/ i tried to put in the spreadsheet for my different error bars
Jon Peltier says
Joe – Are you getting an error? What’s the error message (not just the number, which is generated seemingly at random)? Which line of code is highlighted?
Mark Hovenden says
Woohoo!!!! How exceptional. I hate this change to 2007 but you have made it a lot better by this outstanding tool that makes error bars now even easier than they used to be. Thanks heaps and keep on providing this great service!
While the “new” Excel might be good for those who have never used it before, for those of us that started with Excel 1.3 or earlier, it is a complete pain. I also hate the way you can allow and disallow VBA code, seems stupid to me rather than letting you choose, you have to put “allowed” code into a specific folder… Crazy.
Thanks again,
Mark
Jon Peltier says
Mark –
You can allow macros in a workbook that you’re opening, but your security setting has to be medium or lower. And while it’s easy to miss in 2007, 2010 makes it much more obvious, as described in Improved Macro Security Warning in Excel 2010.
Avi says
Dear Jon
It has been a long time since I visited your website – but I really enjoy your explanations and help. Now, as to the error bars, when I install your add-in, it always leads into an error message and Excel crashes:
Unfortunately, I cannot add the screenshot here, but the message says:
“System Error &H80004005 (-2147467259)”
MS claims, that Add-Ins in 2007 may cause this error when FORMS are used. So what now…?! No more forms in VBA in 2007?!
Do you have any idea or help…?!
Thanks,
avi
Jon Peltier says
I have encountered that error many dozens of times. Sometimes a simple diagnose and repair fixes it, but I don’t know if that is still part of Excel 2007. Usually I have to hack the add-in, remove references to the RefEdits (yeah, it’s only forms with RefEdits or more exotic controls), recompile in Excel 2000, then redeploy.
I’ll try to fix this soon.Try it now: ErrorBars.zip.
avi says
Dear Jon
Many thanks for your quick reply. Unfortunately, I was unlucky… the same error message appears…
I did the diagnose and repair (you find it in 2007 in the Excel options/Resources) – but also this did not help…
Thank you for any help or additional idea…
avi
Jon Peltier says
Avi – I haven’t forgotten, just am swamped. You know, clients and all.
avi says
Thanks Jon
No worry – I am patient and grateful for every help! And: yes: clients DO come first :-))
Nick says
Jon,
I am having horrible troubles trying to get this graph to work. I’ll explain the graph that I am trying to produce:
I have a list of companies that all sell the same products. Let’s say company A, B, & C. We’ll say they each sell the same BALL, a BAT and ROPE.
The catch is that each company sells each item at different prices, therefore for each company and each item there is a standard deviation in price. For example, the Standard Deviation for Company A’s Ball is 1, for it’s Bat it is .003, and for it’s rope it is .9. Then for company B, there is a different standard deviation for all theree products==> This is a total of 9 standard deviations, one for each of the three products being sold of the three companies. You with me?… I’m sure you are, you are a pro at this.
What I want is a graph that will have each company on the X-axis. Above each company will be the three bars representing the mean product costs for the three items sold. On each bar is the item and company specific standard deviation.
I have a feeling that I need to have tons of different series. So far, I can get the graph to look right until i try to add the stdev bars… when I do this (even under the custom bar option) the data I put in for a single company’s standard deviation of a product (Ex. Company A’s BAll) will in turn place these stdev bars above the “ball” for all three companies.
If you can help me out, I would be very greatful.
Jon Peltier says
Nick –
I would think you would use product as your category axis labels, with one series per company. Set up your data like this:
A1: blank
B1-D1: Ball, Bat, Rope
A2-A4: Company A, Company B. Company C
B2-D4: prices (e.g., B2 has price for Company A Ball)
A5-A7: Company A, Company B. Company C
B5-D7: standard deviations(e.g., B5 has SD for Company A Ball)
Make your chart with A1-D4. Use B5-D5 as data for custom error bars (plus and minus) for Company A, B6-D6 for Company B, B7-D7 for Company C.
Jon Peltier says
Nick –
The problem with the above is that the error bars overlap, making the data hard to read. You could introduce a lateral stagger as I show in Creating Paired Comparison Charts in Microsoft Excel.
Kenny says
I am new to excel and I’m working with charts. I’m trying to add a horizontal error bar to a 2d chart any suggestions????????
Jon Peltier says
You can add horizontal error bars to XY charts only (or horizontal bar charts), but not to line charts.
Kenny says
Is a 2d chart a XY chart? I selected the first chart style on the drop down list
Kenny says
I only see vertical options
Jon Peltier says
Excel calls bar charts “bar charts” if the bars are horizontal, or “column charts” if they are vertical. An Excel line chart has markers (maybe) and lines (maybe) and the X axis is not numeric (i.e., it has category labels). An Excel XY (Scatter) chart has markers (maybe) and lines (maybe) and the X axis is numerical, that is, it recognizes numerical values, and therefore it can accommodate horizontal error bars. Horizontal error bars make no sense in the context of a column chart.
Kenny says
My bars are vertical, I am trying to set a horiontal bar to show my goal. Is this an option?
Jon Peltier says
That’s not an error bar, simply a line across the chart. I’ve written about a couple ways to accomplish this in Add a Line to a Column or Line Chart.
Lizz says
Hi, this was very helpful. Thank you so much for taking the time to make it. I spent a long time looking for the option to make the error bars and was about to go insane. I have a question, though it’s more general. Can you make error bars of different sizes on the same series?
Jon Peltier says
Lizz –
To make error bars of different sizes for each point, put the values into a range of cells (it is helpful to plase it in the same block as the X-Y data), then select this range when adding custom error bars. The protocol is described 2/3 of the way through the article, but here it is again:
Using the 2007 interface, first select the series, then on the Chart Tools > Layout tab, choose Error Bars > More Error Bars Options, select Custom under Error Amount, click the Select Values button, and select the range.
In my utility, select the series, click on the Error Bars button on any of the three Chart Tools tabs, and select the custom range using the dialog.
You can have different sized error bars for each point in the positive and negative Y directions, and if it’s an XY series, in the positive and negative X direction.
Brian says
Jon,
Thanks for taking the time to put together such a great piece. Like many others, getting used to 2007 drove me crazy for a while. I have one remaining issue that still leaves me scratching my head.
In a plotted XY graph (lets say its a scatter plot), you click on a single point and it selects the entire series. I have personally NEVER worked with a data set that has the same STDEV for every average in a series. Why is that the default? I use your recommended custom option in settng values for the bar, but still have not figured out how to individualize unique bars for individual averages within a single series.
How do I do this? I acknowledge that the answer may be obvious, but I have been trying the same thing over and over again for an hour and have made myself a crazy person.
Thanks again for a helpful post!
bd
Jon Peltier says
Brian –
Use the Custom Value setting to apply individual values to individual points. These are values you’ve calculated and placed into a range, usually adjacent to the X or Y values. I describe it about midway through this article.
Rahil says
Thank you so much, i’ve been looking for weeks for an easier way to do this.
Ian says
Thanks a lot, your addon made my life so much easier!
Bertrand says
John,
Thanks for this helpful post. I have a related issue: In order to build a dashboard, I have copied the same chart multiple times on the same page. Modifying the series source data ranges of each chart with VBA is no problem, but I have desperately searched for a solution to also change the error bars custom values. Is there a way to read the current range address so that it can be modified ? It seems you can only set it with the errorbar function, as you show in your add-in.
Jon Peltier says
Hi Bertrand –
Unfortunately, Excel does not expose the current error bar value range to VBA. In cases like this I try to put the error bar values in a range which has a standard offset from the series value range. Then VBA can still figure out what range to use for error bars.
Stefanos says
I’m trying to make a chart for an experiment. I have my own result and the class’s average result. Thus i deduced the standard deviation for the class is 607 and our teacher said our own deviation should read 0
Anyway, then we have to create a graph with an error bar showing only on the Class’s Average Results Bar while the bar showing My Result should have a non-appearable error bar (meaning it will be there but won’t stretch)
How do I have to set my Data in Positive and Negative Boxes to achieve this??
So far I get the opposite result. The error bar stretches on My Result vertical bar but won’t stretch at all on the other.
I use 2 boxes B65 (standard deviation = 607) and B66 (my deviation = 0) to set the data field and i select it for both positive and negative text boxes by clicking shift and dragging the cursor over B65 and B66.
How can I achieve the result I want?
Stefanos says
Nevermind, I found it on my own :)
thank you anyway.
It seems I shouldn’t have used box B65 which contained the formula of STDEV. Instead I used another box where I put in the value manually and it worked
Isabelle says
Hey John,
thanks a lot for your great posts and the huge amount of work that probably lays behind it.
I am now trying to deal with Excel 2007 and had the bad surprised that when opening my Panel Chart I created under Excel 2003 (according to your tutorial about Panel Charts with Vertical Panels), everything was disturbed: the primary and secondary horzontal axis are not fitting anymore: 1 space between January and February is not the same length than between 1 and 2…
Did you already adress this issue?
Thanks a lot
Arjun says
Hey Jon,
I have a plot(XY scatter) in xcel 2007 with 4 data points. I have to put Y error bars for all the 4 points but with DIFFERENT error values. By default excel gives a value of 1 to each error bar but in my case I want to put 4 different values (say ±.25, ±.5, ±.75 and ±1) for 4 data points. Is that possible?
I will be thankful to you.
Arjun
Jon Peltier says
Arjun –
Enter the different values into the worksheet, then use these ranges as the data for custom error bar values. It’s described in this article.
Sara Underwood says
Hi Jon-
Thanks for the great tutorial. I stumbled through learning the ribbon interface and wished I’d found your site sooner…
Soon after an upgrade to Excel 2007 I’ve found that the error bars in some of my XY-scatter plots have what I can only describe as black triangles where the error bars should be. I plot my data on a semi-log scale and never had this issue in the 2003 version. I thought at first this was a conversion issue between 2003 (version I created the original graphs in) and 2007; but I’ve recreated the graphs in the 2007 version and have the same issue. It appears to only occur when the negative arm of the error (vertical) bar is added and these are not off the chart error bars (custom). As I need both the +/- side of the error bar I’d like to find a fix. Have you or anyone else encountered this?
Thanks in advance for your time.
Jon Peltier says
Hi Sara –
This problem, triangles appearing when adding error bars, sounds vaguely familiar, but the details are lost in the mists of time. I haven’t seen it in a long time, so I wonder if it was fixed in one of the service packs. If you’re not up to SP2 yet, I’d strongly suggest it.
CDC says
I have not been able to get Excel 2007 pivot charts to correctly calculate standard errors to produce error bars. I believe that the Excel2007 calculation method is actually incorrect, or at least highly non-standard to put it politely. I am wondering if there is a way to calculate standard error bars correctly and simply within Excel 2007 pivot charts. I imagine this would be valuable to many people. Otherwise, it is not possible to use pivot tables/charts to sort data, and still correctly plot average values with standard errors.
Here is a detailed example to more fully illustrate the question:
–The Problem–
A typical calculation for standard error uses all of the data from each point in a series, and calculates the standard error for that point in the series as the standard deviation of the data values for that point in the series divided by the square root of the number of data values. This is a typical, ‘correct’, way of doing it. This is not how excel does it.
The excel values produced when you select standard error appear to calculate error across *all* of the points in the data series. This produces an answer that is not what people think of as a standard error.
— An Example of the Problem –
I created a small example dataset to illustrate this.
Here are the data:
Subj Type Day1 Day2 Day3
1 series1 1 2 3
2 series1 1 2 3
3 series1 1 2 3
4 series1 1 2 3
5 series1 1 2 3
1 series2 1.1 2.1 3.1
2 series2 1.1 2.1 3.1
3 series2 1.1 2.1 3.1
4 series2 1.1 2.1 3.1
5 series2 1.1 2.1 3.1
I then made a pivot chart of the average value for each Day for the data in series1. This is a line chart with the average value for each day, which is clearly 1 for the first day, 2 for the second day, and three for the third day. Excel displays this correctly. I then added standard error bars using the pivot functionality. It shows error bars with an error of 1 for each data point. Clearly, in this example the standard error for each day is 0, because all of the values within each day in the series1 data are identical. Therefore, my viewpoint is that Excel is essentially calculating these incorrectly, or that I am using the wrong options to produce the result that I am hoping for.
— An Inconvenient Solution –
The only way that I know for how to solve this is to separately calculate the standard error values somewhere else in excel, and then have the pivot chart point to these values as ‘custom values’. This is often not straightforward to do, and it essentially defeats the whole purpose of using pivot tables/pivot charts, especially using them to select data for display. In the example above, it would require pre-sorting the data to separate series1 from series2, calculating the standard errors only on series1, and then adding them to the pivot chart as custom values. This defeats the whole point of using the pivot table/chart, which is to select the data to be included.
— A Bigger Problem —
This is relatively easily solved for a trivial example like this one. However, when there is a lot of data, for example dozens of different series, then it is no longer simple to just do the standard error calculations individually and then use custom error values. Further, it defeats the whole functionality of the pivot chart/table.
— The Question –
Is there a way to get Excel 2007 to calculate the ‘correct’ standard error or standard deviation error bars based upon the values for each data point, rather than grouping the data over the whole set (which is basically an incorrect calculation). If so, this will save us, and I presume many other people, a great deal of time. If this does not exist, it would be a very worthy project to create a toolbox that did this, and we would greatly appreciate someone taking this on.
Thank you.
Jon Peltier says
I would not trust a pivot table to calculate standard error. I might use the PT to calculate standard deviation, and also provide a count, then add a column next to the PT with a formula.
I would also not waste this effort on a pivot chart, if I had the chance. They are inflexible and otherwise do not live up to their promise, though the 2007 version has improved.
I never bothered to figure out where Excel’s standard error values for error bars come from, but it does seem to involve all of the points. Not what I’d expect.
Jack Underwood says
Hi John,
I am graphing Error Bars and this is what I have so far:
1. ActiveChart.SeriesCollection(1).ErrorBars.Border.ColorIndex = 15 for color change.
2. ActiveChart.SeriesCollection(1).ErrorBars.EndStyle = xlNoCap.
3. ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _
xlPlusValues, Type:=xlFixedValue, Amount:=100
How do I change my line style and weight of my Error Bars via a VBA code?
Jon Peltier says
Jack –
In 2003 I recorded steps that provided the syntax. In 2007 these would work, but there is a whole new syntax for formatting lines, which cannot be recorded, and which I can never remember.
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlPlusValues, Type:=xlFixedValue, Amount:=100
ActiveChart.SeriesCollection(1).ErrorBars.Border.ColorIndex = 15 ‘ any value 0 (Auto) to 56
ActiveChart.SeriesCollection(1).ErrorBars.Border.Weight = xlHairline ‘ or xlThin, xlMedium, xlThick
ActiveChart.SeriesCollection(1).ErrorBars.Border.LineStyle = xlContinuous ‘ or xlDash, xlDot, xlDashDot, etc.
ActiveChart.SeriesCollection(1).ErrorBars.EndStyle = xlCap ‘ or xlNoCap
One day I’ll blog about the 2007 formatting syntax, because I’ll eventually need to know it. My clients are currently about evenly split between 2003 and 2007, with only a couple using 2000 or 2002. None use MacExcel: my Mac using clients run Excel for Windows in a Windows emulator.
Ken Morison says
Regarding Sarah’s comment 16 Dec 09. Excel 2007 SP2 doesn’t fix this black triangle problem in x-y graphs with error bars. It seems to occur when an error bar gives a negative value and the corresponding axis has a log scale. (When you add to this the automatic addition of horizontal error bars, it can be hard to see what’s happening.) The answer to this BUG is to use custom values ensuring that the negative extreme of the error bar is still positive.
Jon Peltier says
Thanks, Ken, for the explanation.
Christine says
Thank you so much for the utility! That saved my project!
Dale Dreyer says
Thanks for this. Excel 2007 was driving me nuts. Esp. since when I clicked on the error bars only the y bars showed. There was absolutely no way for me to set x error bars. Your liitle app did the trick. Thanks so much!
Clare Fisher says
Thank you soooo much, the utility is great!
Bruce Keefe says
Hi Jon,
I’m running Excel 2008 for Mac and have encountered a major setback with error bars that was absent from the 2004 version.
I have made a ‘template spreadsheet’ with numerous graphs each having custom error bars assigned from my calculation of the standard error. When I copy this spreadsheet and update the data, the data points and linear fits to the graph update. However the error bars do not, instead referencing the original ‘mother’ spreadsheet.
I’m aware that you have encountered this in the windows version of excel from your comments on: . Unfortunately, however, your fix mentioned on this website does not appear to work on Mac.
Any help would be greatly appreciated. I have around 2400 error bars in the workbook so updating them manually is not a viable option.
Thanks
Bruce
Jon Peltier says
Bruce –
Are the error bar calculations not on the sheet containing the chart? If you could move them to the same sheet, then my approach should work.
Colin Norris says
Jon 1st of all, your site is the best. I’ve used many of your utilities and idea in my own workbooks. I’ve just been upgraded to excel 2007 at work and so now need to recalibrate myself to use it.
One issue that is bugging me is that when i plot error bars they always appear behind the data series. I have 2 datasets from the same source. One is recorded every second and the other is an averaged over 2 minutes every 10 minutes. I’m using the x axis error bars on the averaged date to show the beginning and end of the averaged period. The issue is I have the averaged series set on top of the second data but the error bars of the averaged series appear behind the second data and therefore are mostly hidden. any ideas how this can be resolved?
Bruce Keefe says
Hi Jon,
Thanks for your reply. The error bar calculations are computed individually in the same spreadsheet containing the chart.
If you are willing I’d be happy to send you a copy of the spreadsheet so that you could take a look yourself. Perhaps there is something simple that I am missing?
Bruce
Jon Peltier says
Bruce sent me his workbook. It’s a large workbook, with a number of sheets, each with a number of charts, and the charts have custom error bars linked to the parent worksheet.
I copied a few sheets within the workbook, so “Sheet1” for example became “Sheet1 (2)”. In Excel 2003 SP3 and Excel 2007 SP2, charts on “Sheet1 (2)” had custom error bars that pointed to ranges on “Sheet1 (2)”. In the original release version of Excel 2007, the custom error bars on “Sheet1 (2)” pointed at “Sheet1” instead. I don’t know whether this problem was corrected in SP1, but it does work in SP2.
Upgrading to SP2 will enable this functionality.
Fink Nadine says
Hi Jon,
I follow all the instructions, my question:
I have a repeated measures design and therefore have 3 measure time points. I therefore have three different standard error values for T1, T2, T3. Excel (2008) only lets me define one standard error value (pos./neg.) correct?
is there a link I am missing? before with Office 2004 I could do this. now with snow leopard and having to use Office 2008 I am a complete novice and can’t figure it out.
many thanks for your help
Nadine
Jon Peltier says
Nadine –
When you did this before, you needed to use custom error values taken from a range of cells. While formatting the error bars, you have to select the Custom radio button under Error Amount, then click on Specify Amount, then select ranges in the tiny dialog that appears.
Or you could use my utility to make selecting the ranges much easier.
Fink Nadine says
Dear Jon,
many thanks. I am very grateful. I will try it today!
have a nice weekend
Nadine
Fink Nadine says
Dear Jon,
I just followed your instructions and it is what I had been doing. my problem is that I have 3 measure time points (repeated measures) in one figure and different standard errors for each measure point.
and excel 2008 keeps adding the same standard errors to each of the three measure time points. is there a possibility?
I couldn’t download your utility. does it work with snow leopard?
many thanks and kind regards
Nadine
Jon Peltier says
Hi Nadine –
I don’t know whether my utility will run on a Mac, I really have no way to try it out. You should be able to download it, unless there are security settings blocking it.
Send me your file, and I’ll try to work out a set of procedures for you to follow. jonpeltier at-sign gmail dot com
Fink Nadine says
Dear Jon, that is so kind of you! many thanks. and excuse my late response. I was traveling and am back in the us now.
I will send my file.
warm regards
nadine
Mahmood says
Thanks so much Jon for this great post.
A friend of mine just needed the error bar utility disparately.
Many thanks again,
Mahmood
Mahmood says
Thanks so much Jon for this great post.
A friend of mine just needed the error bar utility disparately and when I gave it to him, he was just so happy. With his big job, this error bar utility saved him a few days of work.
Many thanks again,
Mahmood
FS says
Hey John
This program is very impressive, the modeless dialog had me lost for hours. However, is there any way to have different error bars for each data point in an XY scatter-chart? Thanks!
Jon Peltier says
Hi Feras –
If you put your error bar values for each point into a range, when you use my utility, click in the data entry box and select the range. In the Excel dialog, click custom, then select the range in the mini dialog that pops up.
Natalie says
Thank you very much, Jon Peltier, I really find your website very useful and I learn a lot after reading your teaching. Thanks a lot!
J says
I have a graph but when I add error bars, the error bar does not show up on the last bar. Is there a common reason why this would occur?
Jon Peltier says
J – I don’t know of anything off the top of my head. Is it Excel 2007? Are they custom valued error bars, or constant value?
J says
It is excel 2007 and they are custom.
I’ll figure it out eventually, I think I’ve just been staring at excel too long! Thanks anyway
Jon Peltier says
Did you select a large enough range for all points in the series?
J says
Jon:
I figured how to make it work by changing the order of the columns in my graph. Thanks :)
Harini says
Hello,
I have been spending the best part of my afternoon on your website. Thanks for all the work you have put into it. However, I havent been able to find teh solution for my problem. Here is what happens, I am running MS Excel 2007 in a WIndows 7 OS. Everytime I create a custom error bar on my column and go to click on the value in a specified sheet, Excel crashes. I am not able to figure out how to cicumvent it. I have a Job interview in 2 weeks and desperately need to make this work to finish my presentation.
Also, I tried to add your utility (as per instructions) but it comes back with an error message that says “library does not exist.
Any suggestion/information you give will be helpful.
I will really appreciate any help
Thanks
Harini
Sian says
Hi, this is a very very helpful article, and the utility is brilliant.. However, despite following all the instructions word for word I can’t get mine to work.
I’m using Standard Error as Y error bars, and I’ve got a range of 4 of them to use.. I used the function in the positive and negative custom box selecting my range (and deleted what was in the box beforehand) highlighted my range and when I use excel 2003 on my friends computer this always works fine.. But on my 2007 version no matter how I do it my error bars ALWAYS all look exactly the same for each bar. I know it’s not correct.. But I just don’t understand what I am doing wrong?!
Thanks
Jon Peltier says
Sian – You should make sure you have service packs through SP2 installed for Excel 2007.
Jon Peltier says
Harini –
It is important to upgrade 2007 to service pack SP2, to avoid problems with error bars and with the library problems. I have also changed the utility so that it does not depend on a library which is present but may not be recognized.
Delta says
Dude, you’re a fucking beast, thanks SO much for this utility, can’t tell you how much easier you’ve made physics lab assignments for uni students everywhere.
Sam says
You helped me on my Ecology assignment like you would not believe!!!!! i think i am the only person in the class with error bars on my graph (sounds lame but quite significant).
Thanks alot
Your work and this webpage has not been a waste of time
Azad Padamsey says
Hi,
I am using Excell 2003.The utility works great.However,is it possible to annotate the +/-
values on the error bars?
Jon Peltier says
Azad – Error bars do not have associated labels. It is always possible to use a dummy series and its data labels, or simply text boxes, to annotate anything you want.
Azad Padamsey says
Jon,
That’s OK if it’s only a couple of charts with a few data points.I have about 20 (with 100 X Scale Dates) charts that I update each week.Your dummy series solution may help.Is there a sample file that I can download from your website?
Azad
Jon Peltier says
Azad –
In this case, the dummy series is simply an XY series, with X and Y values in the scale of your axes indicating where the points should go. The series is hidden (no markers or lines) and labeled with values from another range in the worksheet, using Rob Bovey’s Chart Labeler (http://appspro.com).
Bob Hamilton says
Jon,
I installed your add in, the item shows up in the Layout bar, but when I click on it, nothing happens. Do I need to close and restart Excel for it to work?
(By the way, when I really want to make a graph that looks like a professional did it, I use KaleidaGraph – a great program and only $200, although I realize many users are constrained to use Excel. In combo with Excel it’s great – I do the necessary spreadsheet stuff in Excel and then import the data into KaleidaGraph and BAM a publication quality chart.)
Your blog is great, keep up the good work!
Cheers,
Bob
Bob Hamilton says
Jon,
Here’s an idea for a REALLY useful Excel add-in (not that your other stuff isn’t – it’s great!).
Many times I’ve created an average of discontinuous cells. If I want the standard deviation of the same cells the hard way is to re-select them, the easier way is to copy and paste the range that’s in the =AVERAGE(copy this range) cell.
BUT that’s still a pain.
I think it should work this way:
1. I create the average cell A1 =AVERAGE(C1,D5,G21)
2. I create a standard deviation cell A2 = STDEV()
BUT instead of selecting C1,D5,G21 for the argument, I SHIFT-click on cell A1. Excel says to itself WAIT he shift-clicked : he must want me to use the ARGUMENT of the cell he click on, not the cell value! And voila – it’s done.
Cheers,
Bob
Jon Peltier says
Bob – You need to select a series in the chart, then click on the button.
Jon Peltier says
Bob –
Shift-Click on a cell means to extend the selection. For the first click it doesn’t mean anything, but for the second, it includes the rectangle defined by the two clicked cells. Also, in your example, the cell references would be relative, so it would calculate STDEV(C2,D6,G22).
ezra abrams says
re error bar utility – (1), can’t thank you enough, and (2) will it do any good if we complain to MS ? this is just one of many, many places where for the technical person, office 07 is harder to use then office 2003
Jon Peltier says
Ezra –
I’ve been complaining since the 2007 beta, in the form of bug reports, discussions with people there, and through my blog and on other forums. So have other people.
Has it done any good? Well, the interface in 2010 is as contorted as in 2007.
However, some positive changes have been made. Chart redrawing performance is much better than in 2007, and almost as good as in 2003 (which was almost as good as 2002). They have restored the macro recorder functionality for chart formatting. They have restored the ability to double click on a chart element to bring up the formatting dialog for that element.
So it’s one step forward, ten steps back.
Rebecca says
Hi,
I’m having the same issue that Colin Norris mentioned above, using Excel 2007. In a plot with multiple series, error bars on one series are plotting behind another series (a line). Changing the order of the series doesn’t help… do you have any suggestions?
Thanks!
Jon Peltier says
Rebecca –
There is a certain hierarchy of layers in which chart elements are drawn. If you have an XY chart with error bars, for example, the error bars for all series are under the lines for all series, and all the lines are under the markers for all series. There are no easy workarounds.
Rebecca says
Hmmm, well that sucks. Thanks for the info!
FATIMA says
I find this really helpful (and so I wish I had excel 2007)……this is probably the silliest question though….I can’t find the “custom” error bars buttons on excel 2008 mac. All I get when I click on error bars is either fixed, percentaje, standard deviation or standard error, but I don’t see the customize button. Is it because of the version or am I missing something? thanks in advance.
Chuck says
Hi Jon
I have been reading this email thread. I run 2007. Your error bar utility is awesome, as I cannot figure out this modeless-thing
Anyway, I am building a control chart. I use Named Ranges for the average, and upper & lower stddev values in the Chart — I combine a LINE and XY chart.
I use the horizontal error bar for the name range XY values in the chart
I have a sequence of event that are “before” and “after”. I’d like to use the x error bar to graphically show the start and stop points of the two event.
Lets say I have 100 events, and after event 76 the stddev values change.
I’d like to show the STDDEV in the first series events with the of Upper/Lower boundries (1 to 75), then show the 2nd series (76 to 100). (Does this make sense?)
Is there away to use the Positive and Negative values in the custom dialouge box to do this??
Thnx
chuck
Jon Peltier says
Chuck, since the vertical positions of the horizontal segments (error bars) are different, you need different points. You could have four points at X=76.5, where Y1 and Y2 are “before” limits and Y3 and Y4 are “after” limits. I would put -76.5 into two cells and 23.5 (100 minus 76.5) into the next two cells, then use this range as positive error bar values for the four points (the negative numbers will then reach in the negative direction).
Dan says
Unbelievable. What an amazing and expertly crafted post. I was ready to just do it all manually in Illustrator, as I was starting to get that adrenaline feeling that you get when you’re 10 and keep messing up during piano lessons. You are a true lifesaver.
Zack says
Is it possible to adjust individual error bars for each data value? I mean I have several x values in the series. I need different ranges for each x value represented in each x’s error bar. Is this possible? Thansk in advance
Jon Peltier says
Zack –
Put the individual error bar values into a range of cells, then use this range when defining custom error bars (or select this range when using my utility).
David says
Hi Jon,
I’m making Excel graphs in Excel 2004 for Mac. I have a continually occurring problem in that when I use custom error bars and select a series, sometimes there are no caps on the error bars. Sometimes I can change my error bar selection and then re-choose “plus” and get the caps to appear but sometimes I can’t. I’m stuck right now with a graph where I can get one series of error bars to have caps but the other not. (the graph is pairs of bar graphs) This has been an ongoing problem for years. When I saw your insight into excel graphing problems and error bars I was hoping you might have an idea to help. As you know, in older versions of Excel you could specifically select caps or no caps but I don’t see that option anymore.
David says
Hi Jon,
I apologize for this but I just found my solution, I believe. After all this time I had missed that under ‘Y error bars’/’colors and lines’ there is an option to choose caps. I didn’t see it because I usually just double click a bar in the graph and then choose ‘Y error bars’. When you do that, there is no option for colors and lines for the error bars. However, when I double clicked the error bar (which I had to create first) then I was able to go to ‘colors and lines’ for that and choose my caps.
All the best,
David
Tahli says
Hi Jon,
I have a graph, with 2 series, each with 3 columns. I’m trying to add error bars, but everytime all the columns in the same series end up with the same error bar. How do I put different error bars on each column when they’re in the same series?
Thanks,
Tahli, Nicola and Anthea
Jon Peltier says
Tahli –
Put the individual error bar values (one for each point) into a range of cells, then use this range when defining custom error bars (or select this range when using my utility).
bill says
Hello Jon,
This is a great discussion… I was using Excel 2010 to figure out the VBA programming for the equivalent of inserting a new range in the custom error dialogue box (Positive Error Value and Negative Error Value). The “record macro” feature does not record this operation. I have struggled to figure out the code. I gotten this far but the code won’t go through. I am missing something. Could you give me a few hints? Assume that the string in “varSr1xbebl” is equal to “=Sheet1!$G$2:$G$10” or some similar range description.
ActiveChart.SeriesCollection(1).ErrorBars.Select
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlErrorBarIncludeMinusValues, Type:=xlErrorBarTypeCustom, “”, Range:=varSr1xbebl
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlErrorBarIncludePlusValues, Type:=xlErrorBarTypeCustom, “”, Range:=varSr1xbebh
Thank you,
bill
Jon Peltier says
Bill –
The range address must be in R1C1 format, so replace “=Sheet1!$G$2:$G$10” with “=Sheet1!R2C7:R10C7”. Also in 2007, combine both plus and minus in the same command:
MySeries.ErrorBar Direction:=xlY, Include:=xlBoth, Type:=xlCustom, Amount:=varS1XPlus, MinusValues:=varS1XMinus
If the error bar only goes in one direction, use “={0}” in Excel 2007 or simply “” in Excel 2003.
Marie says
This site has been helpful, but I have a specific question about how Excel calculates its standard error bars. I calculated standard deviation for a population in my spreadsheet, and from that calculated by own standard error. But if I click on Error Bars using Standard Error for any given set of data, the error bars excel calculates look nothing like the bars I calculate for myself. Do you know perhaps what formula excel uses to do it’s internal standard error calculations?
Thank you.
Jon Peltier says
Hi Marie –
I do not know how Excel calculates its standard error bars. I always calculate my own error bar values, or at least use worksheet functions like STDEV(), so I know where the values come from.
Chris says
Hi Jon,
I have been trying to figure out how to create a bar chart with standard error bars in Excel 2007, and can’t figure it out. I have 4 data series (4 columns in the spreadsheet) and 5 response variables with 3 replications each (total of 15 rows in the spreadsheet). I want to create a bar chart that shows a single bar for the average of the 3 values per response variable for each data series, so the final chart will have 5 groups of 4 bars each. Then I need to add error bars to each bar that represent standard error based on the 3 replications per response variable.
If this is too confusing, perhaps I could send you a spreadsheet showing what I’m talking about…? In any case, thanks for your help.
Chris
bill says
Jon,
Thank you for the VBA programming advice on the error bars. Yes, the key was understanding that if using both error bars then you need to use both the “Amount:=” varS1XPlus, “MinusValues:=” varS1XMinus. In the end, I used range references to dynamic ranges so the error bars will be created only for the values being graphed.
Also, these x-y error bars have all sorts of possibilities, like getting an x-y plot to look like a bar chart (either or both horizontal and vertical axes). I cannot understand why the error bar feature is so hard to access and control.
bill
Jon Peltier says
Chris –
You have to calculate the error bar values and put them into the worksheet. Then use the Custom option for error bar values, shown in this article or in Custom Error Bars in Excel Charts.
Mason says
Is there any way to get Excel to connect the error bar caps with a line? Even better if it could fill things in. I wanted an easy way to add a +/-5% curve shaped backdrop on an XY plot showing uncertainty.
Chris says
Jon – thanks for your help!
Jon Peltier says
Hi Mason –
In addition to the error bars, add two XY series (or if it’s a line chart, two line series) with Y values at Y ± ERR, and hide the markers, to connect the ends of the error bars. You may not even need the error bars.
To fill between these lines, use the technique described in Fill Between XY Chart Series.
Scotty81 says
Jon,
I have used the suggestions you posted in the past about adding horizontal error bars on column plots (to use them as targets). i realzie the Excel error interface dialog boxes have changed in Excel 2007.
However, I’m still trying to achieve that functionality. I kow you pointed another user to a post you had on adding a line chart (I think) on top of a column plot. However, that would result in a continuous line being added. I’m looking for a discontinuous line that appears only above (or through) each column, for the purpose of showing relevant targets.
Is there any way to achieve the above or has MS taken this feature out completely?
Thanks in advance.
Scotty81
Jon Peltier says
Scotty –
Add an XY series with the markers where you need them, then using the 2007 ribbon or my utility add horizontal error bars. Conceptually the same as before, though the actual operations differ.
Frank says
I am trying to use error bars to display an upper and lower limit. Not UCL and LCL, just constants. My initial graph is an X-Y scatter graph, with frequency on the Y axis and temperature on the x axis. I followed along with your steps, adding a table with 3 rows and 3 columns. The first column A has the max temp on the X axis (85) in all 3 rows. The second column B has the max, nominal, min values of frequency. The third column C is the text Max, Nom, Min. I select the $A$1:$B$3 array, copy it, and paste as a new series into my chart with settings Values (Y) in Columns, Series Names in First Row checked, Categories in First Column checked. This resutls in 3 nice data points in my graph. I select the series and then choose Chart Tools | Layout | Error Bars | More Error Bar Options. When the next dialog opens, I only have the option of Vertical Error Bars. I need Horizontal, but cannot discover how to get them. Can you help? Thanks, Frank
Jon Peltier says
Frank –
If it’s an XY chart, then it has both X and Y error bars. The dialog shows the vertical ones, which are selected in the chart, but you can select the horizontal ones in the chart (if you can see them), or use the dropdown in the left of the Layout and Format tabs of the ribbon. Selecting the horizontal error bars will display the Horizontal Error Bars dialog.
This was a disusability feature introduced in 2007.
Frank says
Thanks, that got me going! I could not see the horizontal error bars, and didn’t realize what the Current Selection options could do. Interesting that in the list of items they are referred to as X/Y Error Bars and not Vertical/Horinzontal as in the dialogs.
Karen Youngman says
Jon,
The error bars have helped me tremendously. I was able to add both an X and Y error bar using VBA code into my bubble chart. However, I want to change the weight and I could only do this successfully on the Vertical Bar. How can I separate out the “horizontal” bar so I can change the line weight in VBA. This code only changes the weight on the Vertical bar for some reason.
ActiveChart.ChartType = xlBubble
With ActiveChart.SeriesCollection(1)
.Select
.HasErrorBars = True
.ErrorBar Direction:=xlX, Include:=xlBoth, Type:=xlCustom, Amount:=1, MinusValues:=1
.ErrorBars.EndStyle = xlNoCap
.ErrorBars.Format.Line.Weight = 2
.ErrorBar Direction:=xlY, Include:=xlBoth, Type:=xlCustom, Amount:=1, MinusValues:=1
.ErrorBars.EndStyle = xlNoCap
.ErrorBars.Format.Line.Weight = 2
End With
Jon Peltier says
Karen –
I’ve never noticed, believe it or not (I didn’t believe it), but there seems to be no way in VBA to specify just the X or Y error bars. Not just in 2007, but also in 2003. Whatever you do happens to both. Bizarre.
If I needed different X and Y error bar formatting in a VBA driven project, I think I’d have two XY series, one with just X error bars, one with just Y error bars, and I’d format the error bars differently for each series.
Karen says
Thanks for the idea John, that worked!
A says
It’s 2:44 in the morning and I’m in the middle of re-graphing about 20 charts for a scientific manuscript. This utility pretty much just made my life/saved me from a life of carpal tunnel. Thanks.
Manoj Kapashi says
Is it possible to conditionally format individual error bars, say if the value of error bar 2 is greater than error bar 1, show error bar 2 as green, else red?
Jon Peltier says
Hi Manoj –
All error bars in a set for each series have to be in the same format. A set means positive X, positive Y, negative X, negative Y.
You could cobble together a setup that has two series for your points, one of them hidden or both using identical formatting. One has red error bars, the other green. Have the error bars taken from the worksheet. Formulas in the worksheet determine whether the red or green series has a positive or negative error bar at each point, similar to the technique that can be used to Conditionally Format Excel Charts.
Manoj Kapashi says
As usual, a brilliant and effective solution! Thank you very much.
Alicia says
Hi Jon,
Thank you for this Excel add-in, I can’t believe that they could make an ‘upgraded version’ and leave out basic previously easy to use options like the error bars and adding patterns. I have been struggling to make a basic graph with 2007 for hours:(. I have installed your add-in but when I click on the button in the layout tab it doesn’t do anything. I have tried selecting different things in the chart before i try to click on the button but nothing seems to work. I installed another add-in earlier today and that one is working so I figure it is probably not how i did it as i followed your instructions which were the same as the previous add-in i installed (although i am totally open to the possibility that it is something i did wrong). Can you help me get this working?
thank you,
alicia
Jon Peltier says
Did you install the 2007 version (and not the 2003 version) of the error bars utility? Do you see the error bars utility listed in the add-ins dialog?
phillas says
Thank you for your add-on. I have Excel 2007 and am used to older versions. Also, my boss has Excel 2003, and wants error bars for some graphs he wants to present. I have to use Excel 2007 and save the graphs as an Excel 2003 file. Your add-on works just fine. You solved a big headache for me.
Thanks again!
George Vousden says
Hi Jon,
By the looks of things you have a great utility, but I appear to be unable to see any form of download link or anything resembling it? All I can see is the commnets on how good it is?
Sorry I’m missing an obvious link, but could you point how I’m supposed to download the utitlity?
Thanks!
George
Jon Peltier says
George –
Under the header “Error Bar Utility” there is a link with the text “ErrorBars.zip”. The link address is
Kelvin Tan says
Hi Jon,
I have a set of data (X: Distance, Y: Mass) and I determined the mass loss from the gradient of the trendline I plotted. However, I would require the maximum and minimum error of my data. I understand that I can use the error bar but I dont seem to be able to get any values from it.. Does it mean I have to plot the maximum and minimum plot myself to obtain the values? Please advice.
Thank you.
Kelvin
Jon Peltier says
Kelvin –
You have to calculate the error you want to display, using some appropriate algorithm.
Joel says
Hi there. I have the add-in for Excel 2007. I am using a scatter plot and I would like to add individual standard error to each data point I have. Can this be done? Thanks.
Jon Peltier says
Joel –
This article indicates that you can select a range for the error bar values, but doesn’t clearly tell you what is special about this range. As my follow-up article Custom Error Bars in Excel Charts states, put the individual error bar values for each point into this range. If you select a single cell, the value in this cell will be applied to all points in the series.
Joel says
Hi again. Thanks for the quick reply. Let me give more information. I am using scatter plot graph but using data in sets. I am plotting percent survival for different strains of bacteria after exposure to UV. I would like to put error bars for each data point in a strain (i had 3 of each strain under go these UV treatments).
I did a standard deviation and then calculated standard error of the means for each UV dosage. When i try to select the 5 SEs, I get a message that says “Compile errors in hidden module. FErrorBars” and options to click OK or Cancel.
What am I doing wrong?
Jon Peltier says
Joel –
I don’t see anything in the code that would not compile properly. Have you updated Excel 2007 to Service Pack 2?
Andrew S says
Hi Jon,
Thanks for this great explanation.
Haven’t installed your utility, but I have a question about how Excel 2007 messes with my error bars.
I have a XY scatter plot where I have 2 series with very similar data. Each series has Y error bars, which I’ve already added. They do not have X error bars, so I set them to “fixed, 0”.
The error bars for the two series overlap almost completely, so I can’t tell them apart visually. So I want to format the error bars for Series1, to make them the same color as the series points, so I can distinguish the error bars. But because all error bars overlap, I can’t tell which I’m selecting by just clicking. How can I tell? Shouldn’t there be some indicator on the modeless dialog as to WHAT I’m editing??
The first way I figured out is to go back to the Layout->Error Bars thing, and start over. BUT — and here’s the annoying part — it adds in X error bars (“fixed, 1”) where there weren’t any before (“fixed”, 0). Is there a way to stop it from doing this? It make me feel like Sisyphus, removing X every time I reformat Y.
Thanks for your help,
Andrew S.
Jon Peltier says
Andrew –
You can use the chart element selection dropdown in the top left corner of the Chart Tools > Layout and Format tabs to select the error bars. It will have listed “Series 1 Y Error Bars”, “Series 1 X Error Bars”, etc. First, select the X error bars, and press Delete. Gone, out of your way. Then select the Y error bars for one series, and press Ctrl+1 (numeral one) to open the format dialog.
Andrew S says
Wow. That just made me a factor of 10 more effective at this. Thanks a ton!
Andrew S.
Derek Hullinger says
Thanks so much for creating this free add-in! It makes creating error bars in Excel 2007 much quicker and easier!
XC says
Hi, I have a quick question. When I added the error bars, I wanted to assign different dots with different error bar, however no matter how I did it, as long as I changed one dot, it will automatically change all the error bars of all dots in the same plot.
Is there any way that can assign different dots with different error bars in the same plot?
Thanks
XC says
problem solved. Thanks
Paloma says
Hello!
I have the same problem than XC,
I wanted to add different values of error for differents dots in my graph but if I change
one dot it will automatically change all the error bars of all dots in the same plot.
how did you resolve?
Thank you very much!
Jon Peltier says
How are you trying to change the error bar for one point? You can’t do this unless you use a range for your error bar values, and change one value in the range, in the cell corresponding to the point you want to change.
Paloma says
Hello! frist, I have the spanish version, so maby the tabs’s names are not exactly the
literal translation,
I select the data serie in the plot, and then in ‘Chart tools’> the middle tab called
(literal translation) ‘Presentation’ > the penultimate control panel called (literal
tanslation) ‘Analysis’ there is ‘Error bars’,
here it comes out a new panel ‘Format of error bars’ where we can expecificate a
‘personalice value’,
here I put in the values that I have already calculated (error typic), but I can only put
in a value for all the dots of de plot, and I would like to especificate a different value foreach one,
I don’t know if I have well explined my-shelf,
thank you very much
Jon Peltier says
Hi Paloma –
You can only enter a single value into that box, and it will be applied to all points. This page explains the way to get custom error bars, but another page (https://peltiertech.com/custom-error-bars-in-excel-charts/) may be better.
Put your values into the worksheet, then on the dialog click the “Custom” button, which pops up another small dialog that lets you select a range containing the custom values. Or use the little add-in I’ve linked to from all of the error bar pages.
Sabrina says
Hi Jon, love the add-in!
I have a question though – do you have one that works in Powerpoint 2007? I usually build my charts in Powerpoint rather than copy-pasting from Excel, and I can’t include my lovely error bars when I do it this way.
Thanks!
Steve D. says
Thank you for the awesome utility. I have been struggling with error bars in Excel 2007 for over a year now and wish I had found this sooner. Better late than never, I suppose. Your add-in does exactly what Excel should do. It always baffles me why they have to make things so complicated sometimes. Anyway, thanks again.
Jon Peltier says
Sabrina –
I did not try to make any of my utilities work outside of Excel. I know Excel runs inside of PowerPoint for aking charts, but it’s more complicated than that, and I haven’t given it any special attention.
Maybe some day.
Trevor Ogden says
Brilliant, thankyou. Excel Help left me completely baffled
Annabelle says
Hello, I’m having trouble making different y-error bars for each point on my graph, every time i put in the standard error value in, all of the points have that standard error rather than different standard errors for each point, how can i do this? (i have excel 2010), thanks!
Jon Peltier says
Annabelle –
You can’t enter individual values for each point into the dialog. Any value you enter will be applied to all points in the series.
As described in this article but perhaps more directly in Custom Error Bars in Excel Charts, you have to put the set of values into a worksheet range, then use the custom error bar dialog to select this entire range, applying a different cell’s value to each point.
Or you can use the utility described near the end of this article.
Val says
Hi Jon,
In a Chart (X Y Scatter) with multiple series , is it possible to add the custom error bars for each serie all at the same time (without having to specify the positive and negative values for each individual serie)??
Thanks
Barry Powell says
Hi,
i have read this with interest and found it very helpful. I have a user here who thinks that having the error bars on each point makes the graph look very clutterd. They would like to just have the average error displayed in the top right corner of the chart.
Do you know a way of acheiving this.
Barry
Andy says
Thanks so much, this is amazing. For the benefit of others in my predicament. Follow the directions, you can make fine looking custom error bars for each point on your graph.
Gary says
Hi Jon,
I have 2 questions about Chart in Excel 2007.
1. How can I get currently selected data series index (index number of seriescollection).
2. As the source data for error bars is 2 range, I can easily set it manually in Excel. But when I set error bar in VBA, it returns a run time 1004 error: ” A series may only have a single set of error bars”.
My code is as follows:
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlX, Include:=xlBoth, Type:=xlCustom, _
Amount:=”=(Sheet1!$E$16,Sheet1!$E$26)”, MinusValues:=”=(Sheet1!$E$16,Sheet1!$E$26)”
Is it possible to set a multiple range error bars?
Thanks!
Jon Peltier says
Gary –
1. Good question. If there are no duplicate series names, it’s most reliable to identify a series by name, as in Selection.Name and ActiveChart.SeriesCollection(“Series Name”).
2. I don’t know what you mean by “multiple range error bars”. You can define a different error bar value for each point in the series, using a range that contains values for each point.
I describe VBA techniques for error bars in Custom Error Bars in Excel Charts. One thing to note is that the formulas used in VBA have to be in R1C1 notation. Another thing that may cause trouble is that I am not sure a discontiguous range can be used. Fix the cell address format first, and if that isn’t enough, put the values into a contiguous range, and use the address of this range.
Gary says
Hi Jon,
Thanks so much for your reply.
The solution for my first question works well!
For the second question, you are right, what I mean is discontinuous range, for both data points and error bars. You mentioned that I can define a different error bar for each point in the series, but Point object does not have ErrorBar method (at least it does not work in Excel 2003). Could you please let me know how to define error bar for a single point? Thanks!
Anonymous says
This is worthless without showing where to click.
Jon Peltier says
Which part of the instructions are unclear? What version of Excel are you using, and what do you need to click on?
Jon Peltier says
Gary –
If only one point needs an error bar, you still need to define error bar values for the entire series, but the other values will be empty cells. Then apply this range as error bar values for the whole series.
Gary says
Hi Jon,
I tried again and found I can assign discontinuous range to make error bar. It works well here in my computer. I am not sure what happened that day. But any way, thanks again for your valuable reply!
Geo says
Sorry Jon
I installed your AddIn error bar utility ErrorBars.xlam (excel2007) . I can see PTS tool but when I press the T error bars button the dialogue window does not appear. Have you any idea why?
Cheers
Geo says
Geo comment: got it working.
However I wanted to put multiple standard error values on on a series. I still have to do it one series at a time. After five series it becomes very tedious.
Graph description as follows
Bar categories: Group (taxonomic family)
Series; Year_month
When I try to highlight the complete set of error values for each category in all series a runtime error 1004 appears.
Is what I am trying to do possible?
Cheers
Jon Peltier says
Geo –
The nature of error bars in Excel means that you need to specify custom values one series at a time. If you specify more than a single row or column for the values, Excel complains.
I could probably extend the utility to include error bars for each series, but you would have to adhere to a prescribed data layout. An intermediate approach would be to add a dropdown to select a series, and this way apply error bars to several series in one visit to the dialog.
Enas says
It will be great to to extend the utility to include error bars for each series.
Ben says
I could kiss you for this add-in. I was about to hurl myself out of a 5th story window when the horizontal error bars were being added automatically. I have far too much data to have to manually delete the horizontal error bars each time.
Thanks again.
Elaine says
Regarding the black triangle issue on standard deviation, i have ensured the custom values on negative extreme remain positive, but i still ended up getting the black triangle (on a semi-log graph).
The problem is i have 2 data series on the graph. I could add the y-error bar like normal standard deviation bar on one of the data series, but the another data series showed the black triangle area.
Jon Peltier says
Elaine –
You have to ensure that the values at the ends of the error bars, that is, the data point values minus the negative error bar lengths, are greater than zero. I did some tests, and found that if and only if the value at the error bar tip in the log chart was negative, Excel 2007 draws the ugly triangles. Excel 2003 doesn’t draw any such error bar (so no triangles), while Excel 2010 draws an error bar with a length of zero (no triangles, but an end cap coincides with the data marker). So Excel 2010 has improved on 2007’s mishandling of the error bars, Excel 2003 still did it better.
Manish says
Can I set error range for individual data point ????
Jon Peltier says
Manish –
This is what the custom error bar values do. I wrote more extensively about this in Custom Error Bars in Excel Charts.
alex says
Thank you so much for this!!!
I could not figure out where they’ve done with the eroor bars in the new vezion and how to operate with them. You truly saved my life!
frank says
well i anted to show standard deviation in my clusterd chart, but for every data point its different (and too different), cant enter same for all values so thinking of not entering it all, so that it cant be misinterpreted.
Geo says
Thanks Jon
a drop down menu for multiple series would be the way to go then. Thanks for building this the error bar tool, you make peoples work life much more bearable and excel a whole lot less tedious.
cheers
Don Gilbert says
Hi Jon
Am getting very frustrated.
I often need two vertical axis, in bar chart format, to illustrate two sets of data.
I have Microsof 7 (version 2010)
Don
Monica says
Hi Jon,
Thanks for this! It will be so incredibly time saving, especially if you add the drop down for selecting a series. I actually like your “intermediate approach” better here – it seems like this would work faster despite the extra clicks, especially if there would be a way to include “next series” and “previous series” buttons… though I have no idea how easy or complicated that would be. I’m so glad you posted this!
I just got Excel 2010 on my home computer, and I’m wondering if this is compatible. I saw posts further up, but I couldn’t tell if they were adding more, or if they were figuring out compatibility. I’m worried about running an Add-On that I shouldn’t, and somehow causing problems.
Monica
Jon Peltier says
Hi Monica –
The add-in should work fine in Excel 2010. It’s very simple, and shouldn’t interfere with anything else. Even if it did, you could uninstall it easily enough.
Anne Beasley says
Hi Jon,
I am having a truely frustrating time trying to plot some data in excel 2007. I have plotted many scatter plots with custom error bars with no problems at all….until I try to plot this one particular data set. For some reason, witout fail, regardless of which computer I am on, regardless of which spreadhseet the data is in, I get the old “Microsoft has encountered a problem and needs to close…”. I am at a total loss. Something about this particular data set (which is very similar to all the others I have plotted previously) is causing this. Any tips or advice would be super!
Cheers
Anne
Ash says
Hi Jon.
I usually want my Excel chart as part of a Word doc and therefore embed it in the Word doc rather than doing it separately and pasting it in. This has always worked brilliantly with the 2003 version but the 2010 version appears to have a software glitch. I have found that at the final step of adding Y error bars it not only crashes the programme but freezes my computer as well. A real pain. Have you come across this?
Regards
Ash
Aaron says
Thanks so much for this very handy utility. I had literally no control over Excel 2010 until I implemented your utility- and it worked great even though I thought it might be outdated.
Keep up the great work!
Aaron
Marie says
Thanks so much for this tutorial! Im working on my biology lab report and was completely lost until I read this. Keep it up!
–Marie
Edmund says
Hey
Hahahahaha I am doing a biology report and agree, this is a super great read and has helped a lot. Thanks for taking the time to do this.
Regards
Alex says
This has saved me so much time. Thank you so much for this add-in.
Penny Lane says
I am doing a project in school and i tried to figure out how excel works and yet I believe you have confused me even more.
Jon Peltier says
What in particular has confused you?
Deepak Nimbalkar says
Thanks , this article is very usefull
George Fang says
Hi Jon,
Great post you have here. I am amazed that excel 2010 does not have a standard stepwise type of chart built-in. With your error bars hacking, it seems to work pretty well for creating a decent stepwise type of line graph except for one thing.
When I try to create the chart using code (C#), I have no way to select horizontal bars to change the line color and/or line weight. I know that we can do those through the excel interface manually. But not being able to modify horizontal bar lines in code is pretty bad. Do you have any way to get around that?
Thanks,
George.
Adnan Zulfiqar says
Hi, Your article was greeeeeeeeeaaaaaaaaaaat. I found it very helpfull for my study. Keep it up.
adam says
Hi Jon
just a couple of concerns, my teacher did a template of the error bars on a chart for an assignment that we have to do, but i notice that each of the three columns have different standard deviations. When i went to do mine, all three columns had the same standard deviation, im just wondering how i can change this to look like the template to have different deviations.
please help me!!
thanks, adam
Jon Peltier says
When selecting the range for custom error bars, did you select all three cells, or just one cell? If you select just one, Excel uses the same value for all bars.
adam says
Thanks Jon!! this really helped my work process!!:)
Nona says
Thank you very much, Mr. Peltier! Your add in helped me a lot! I was having a big trouble because I cannot find the horizontal error bars. Thank you very much! :)
David says
Hello
I have the same problem Pamela had in May 2009. Here is my spreadsheet. I cannot get the individual SD’s as error bars in a bar chart (two categories with 7 variables). I tried using your utility but didn’t undertand how, sorry! Can you help?
Weaned SD Still BF SD
Grain 90.4 2.4 80.5 4.3
Legumes 51.3 3.1 49.3 6.3
Dairy 82.7 3.6 41.5 4.4
Flesh 68.7 5.3 55.4 6.6
Eggs 70.9 4.3 66.5 5
Vit A-rich v 76 3.5 61.6 5.2
Other veg 87.3 2.8 75.6 4.1
Thanks
David
Jon Peltier says
Using the utility, you first select the series you want to add the error bars to, then start the utility. Then click in the Plus Y error bars box and select the SD values for the series, click in the Minus Y error bars box, and select the same SD values, and click OK. Repeat for the other series.
David says
Thanks so much Jon, it worked perfectly, and for “Add-in” first timers – see https://peltiertech.com/installing-an-add-in-in-excel-2007/
Levan says
Thank you Jon very much. You made my life easier :))
Anonymous says
Hi,
I can see how to add error bars to a graphs one series at a time but am sure in previous versions of Excel it was possible to add error bars (individual) to all series at once in a similar way to the data being added to the graph? Is this possible?
Thanks
Jon Peltier says
Nope. Error bars have always been added one series at a time. You can add data labels to all of a chart’s series at one time, maybe that’s what you’re remembering.
Anonymous says
Thanks for getting back to me about that, good to have a definitive answer
meko says
Hi Joan,
Really it helps a lot.Could you send me any scientific paper which has this kind of x and y error bars with grouped mean.Thanks
Jon Peltier says
Meko –
To get started, search Google for “examples of charts with error bars”.
V says
Hi Jon,
Thanks for this. Very helpful. Is there anyway to show the data label for the error bars ?
Have been trying to create a column chart (example Q1 2014 & Q1 2015 columns next to each other and want to show the yoy change (have managed to use the error bar above to do so). But unable to show the value or label for that error or change. Or is there a simpler or more elegant way to do this instead ?
Thanks
Nikhil
Jon Peltier says
Nikhil –
There is no data label for error bars. What you could do is add additional series, one with Y corresponding to Value + Error and the other to Value – Error, hide the lines and markers for these series, but add data labels.
Laura says
Hi, Very nice and helpful tool, however it is not working for me. I installed the add-in and the Error Bar button appears now in the format tab but when I click on it the menu does not open… Any idea of why and how I can I solve it? I am using Office Professional Plus 2010.
Many thanks, Laura
Jon Peltier says
Laura –
Windows might be blocking the file. This behavior was introduced in a recent Windows Update, and affects files on your computer that came from another computer (like my add-in). Read about the problem and workarounds in Ribbon Disappears from Excel.
Agnes says
Hi Jon,
I found this post today.
Thank you for the add in, I installed it according to your instructions, and it helped me a lot to plot error bars quickly!
For me it works only if I double click the column series I want to add error bars to. After I discovered that single click is not enough, only double click, it works really nice and saves me hours.
Thanks again!
Agnes
Agnes says
Now I tried again, and it works by single click.. Even better! No idea, what exactly happened. (Closed and opened files in between, tried another add in, which did not work though.)
Thanks,
Agnes
Jon Peltier says
I don’t know what is stranger, that it needed a double click at first, or that it works with a single click now. Thanks for sticking with it.