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.
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.
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.
<<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.
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.
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?
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.
Thank you. Quite a read this article.
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?
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.
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
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.
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
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.
Jon
1. Thats great
2. Exactly what I was thinking would be the easiest solution..something in that vein.
Thanks again.
Jake
I officially love you. This is great! Awesomeness.
Aw shucks,
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?
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.
Hii sir,
can u pls help me out how to calculate X error and Y error….from the existing data of X and Y…
You should search for a general description, such as Standard error (statistics) – Wikipedia, the free encyclopedia, then if necessary search on more specific keywords.
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
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.
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.
Avi – What chart type are you using, and in what version of Excel?
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.
Are you sure the file has been updated? It works the same and has the same problem.
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.
thanx a lot it was a real help!!
Thanks a ton for the error bar add in…
cheers
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.
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.
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
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?
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
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.
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
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.
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
Avi – I haven’t forgotten, just am swamped. You know, clients and all.
Thanks Jon
No worry – I am patient and grateful for every help! And: yes: clients DO come first :-))
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.
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.
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.
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????????
You can add horizontal error bars to XY charts only (or horizontal bar charts), but not to line charts.
Is a 2d chart a XY chart? I selected the first chart style on the drop down list
I only see vertical options
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.
My bars are vertical, I am trying to set a horiontal bar to show my goal. Is this an option?
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.
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?
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.
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
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.
Thank you so much, i’ve been looking for weeks for an easier way to do this.
Thanks a lot, your addon made my life so much easier!
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.
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.
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?
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
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
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
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.
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.
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.
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.
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.
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?
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.
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.
Thanks, Ken, for the explanation.
Thank you so much for the utility! That saved my project!
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!
Thank you soooo much, the utility is great!
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: http://www.eggheadcafe.com/software/aspnet/34290636/custom-error-bars-bug-.aspx. 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
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.
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?
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
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.
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
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.
Dear Jon,
many thanks. I am very grateful. I will try it today!
have a nice weekend
Nadine
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
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
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
Thanks so much Jon for this great post.
A friend of mine just needed the error bar utility disparately.
Many thanks again,
Mahmood
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
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!
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.
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!
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?
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?
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
Did you select a large enough range for all points in the series?
Jon:
I figured how to make it work by changing the order of the columns in my graph. Thanks :)
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
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
Sian – You should make sure you have service packs through SP2 installed for Excel 2007.
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.
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.