Error Bars in Excel 2007 Charts
by Jon Peltier
Wednesday, December 10th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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 not obvious way to switch to the horizontal error bars. We are used to having two 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, 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, 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.
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$13
If you want the value to be zero, enter zero. Don’t completely clear an entry box. because Excel will think you simply forgot and it will retain the previous value.
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.
Related Posts:
- Custom Error Bars in Excel Charts
- Candlestick Alternative: Individually Colored Up-Down Bars
- High-Low Line Alternatives in Excel Charts
- Callout Labels with Error Bars
- Draw an Axis With Tick Marks But No Line
- Deming Regression Utility
- Marimekko Charts
- How To: Assign a Macro to a Toolbar or Menu
- Simple Waterfall Chart with Up-Down Bars
- A Belated Review of Excel 2007
Posted: Wednesday, December 10th, 2008 under Excel 2007.
Comments: 129
Comments
Comment from Colin Banfield
Time: Wednesday, December 10, 2008, 10:57 am
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.
Comment from Jon Peltier
Time: Wednesday, December 10, 2008, 11:59 am
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.
Comment from Colin Banfield
Time: Wednesday, December 10, 2008, 12:51 pm
<<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.
Comment from Jon Peltier
Time: Wednesday, December 10, 2008, 7:17 pm
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.
Comment from jatzhang
Time: Wednesday, December 10, 2008, 10:23 pm
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?
Comment from Jon Peltier
Time: Wednesday, December 10, 2008, 11:40 pm
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.
Pingback from Excel Links of the Week [Dec 15] | Pointy Haired Dilbert – Chandoo.org
Time: Monday, December 15, 2008, 10:51 am
[...] Introduction to Error Bars in Charts [...]
Comment from John
Time: Monday, December 15, 2008, 9:26 pm
Thank you. Quite a read this article.
Comment from Chad
Time: Tuesday, December 30, 2008, 2:16 pm
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?
Comment from Jon Peltier
Time: Tuesday, December 30, 2008, 3:25 pm
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.
Comment from Daniel
Time: Sunday, January 4, 2009, 12:10 pm
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
Comment from Jon Peltier
Time: Sunday, January 4, 2009, 1:01 pm
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.
Comment from Jake
Time: Monday, March 2, 2009, 6:19 pm
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
Comment from Jon Peltier
Time: Monday, March 2, 2009, 6:57 pm
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.
Comment from Jake
Time: Monday, March 2, 2009, 7:03 pm
Jon
1. Thats great
2. Exactly what I was thinking would be the easiest solution..something in that vein.
Thanks again.
Jake
Comment from Pernille Madsen
Time: Monday, May 4, 2009, 3:12 am
I officially love you. This is great! Awesomeness.
Comment from Jon Peltier
Time: Monday, May 4, 2009, 6:06 am
Aw shucks,
Comment from Pamela
Time: Thursday, May 7, 2009, 3:17 pm
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?
Comment from Jon Peltier
Time: Thursday, May 7, 2009, 8:05 pm
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.
Comment from sudha
Time: Saturday, July 4, 2009, 3:20 am
Hii sir,
can u pls help me out how to calculate X error and Y error….from the existing data of X and Y…
Comment from Jon Peltier
Time: Saturday, July 4, 2009, 7:25 am
You should search for a general description, such as Standard error (statistics) – Wikipedia, the free encyclopedia, then if necessary search on more specific keywords.
Comment from Annette
Time: Sunday, July 19, 2009, 12:46 am
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
Comment from Jon Peltier
Time: Sunday, July 19, 2009, 9:53 am
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.
Comment from Avi
Time: Wednesday, July 29, 2009, 3:06 pm
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.
Comment from Jon Peltier
Time: Wednesday, July 29, 2009, 3:35 pm
Avi – What chart type are you using, and in what version of Excel?
Comment from Jon Peltier
Time: Thursday, July 30, 2009, 1:15 pm
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.
Comment from Avi
Time: Thursday, July 30, 2009, 6:12 pm
Are you sure the file has been updated? It works the same and has the same problem.
Comment from Jon Peltier
Time: Thursday, July 30, 2009, 6:49 pm
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.
Comment from Naseer
Time: Wednesday, August 12, 2009, 6:11 am
thanx a lot it was a real help!!
Comment from vinay
Time: Thursday, August 13, 2009, 3:15 pm
Thanks a ton for the error bar add in…
cheers
Comment from Scott
Time: Monday, August 24, 2009, 4:19 pm
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.
Comment from Jon Peltier
Time: Monday, August 24, 2009, 9:09 pm
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.
Comment from Joe
Time: Friday, September 4, 2009, 5:28 am
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
Comment from Jon Peltier
Time: Friday, September 4, 2009, 10:15 am
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?
Comment from Mark Hovenden
Time: Wednesday, September 16, 2009, 12:58 am
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
Comment from Jon Peltier
Time: Wednesday, September 16, 2009, 6:31 am
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.
Comment from Avi
Time: Wednesday, September 16, 2009, 7:48 am
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
Comment from Jon Peltier
Time: Wednesday, September 16, 2009, 11:43 am
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.
Comment from avi
Time: Wednesday, September 16, 2009, 3:24 pm
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
Comment from Jon Peltier
Time: Thursday, September 17, 2009, 10:19 pm
Avi – I haven’t forgotten, just am swamped. You know, clients and all.
Comment from avi
Time: Friday, September 18, 2009, 12:48 am
Thanks Jon
No worry – I am patient and grateful for every help! And: yes: clients DO come first :-))
Comment from Nick
Time: Thursday, September 24, 2009, 6:16 pm
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.
Comment from Jon Peltier
Time: Thursday, September 24, 2009, 11:01 pm
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.
Comment from Jon Peltier
Time: Thursday, September 24, 2009, 11:03 pm
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.
Comment from Kenny
Time: Monday, September 28, 2009, 2:37 pm
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????????
Comment from Jon Peltier
Time: Monday, September 28, 2009, 2:39 pm
You can add horizontal error bars to XY charts only (or horizontal bar charts), but not to line charts.
Comment from Kenny
Time: Monday, September 28, 2009, 2:48 pm
Is a 2d chart a XY chart? I selected the first chart style on the drop down list
Comment from Kenny
Time: Monday, September 28, 2009, 2:50 pm
I only see vertical options
Comment from Jon Peltier
Time: Monday, September 28, 2009, 3:08 pm
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.
Comment from Kenny
Time: Monday, September 28, 2009, 3:10 pm
My bars are vertical, I am trying to set a horiontal bar to show my goal. Is this an option?
Comment from Jon Peltier
Time: Monday, September 28, 2009, 3:30 pm
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.
Comment from Lizz
Time: Friday, October 9, 2009, 12:54 am
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?
Comment from Jon Peltier
Time: Friday, October 9, 2009, 7:07 am
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.
Comment from Brian
Time: Monday, October 19, 2009, 4:09 pm
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
Comment from Jon Peltier
Time: Monday, October 19, 2009, 6:00 pm
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.
Comment from Rahil
Time: Sunday, October 25, 2009, 8:51 am
Thank you so much, i’ve been looking for weeks for an easier way to do this.
Comment from Ian
Time: Wednesday, October 28, 2009, 10:40 am
Thanks a lot, your addon made my life so much easier!
Comment from Bertrand
Time: Monday, November 2, 2009, 1:57 pm
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.
Comment from Jon Peltier
Time: Monday, November 2, 2009, 2:12 pm
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.
Comment from Stefanos
Time: Wednesday, November 4, 2009, 3:01 pm
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?
Comment from Stefanos
Time: Wednesday, November 4, 2009, 3:14 pm
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
Comment from Isabelle
Time: Thursday, November 26, 2009, 12:04 pm
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
Comment from Arjun
Time: Tuesday, December 8, 2009, 6:46 pm
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
Comment from Jon Peltier
Time: Tuesday, December 8, 2009, 10:06 pm
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.
Comment from Sara Underwood
Time: Wednesday, December 16, 2009, 4:46 pm
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.
Comment from Jon Peltier
Time: Wednesday, December 16, 2009, 10:13 pm
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.
Comment from CDC
Time: Monday, December 21, 2009, 6:46 pm
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.
Comment from Jon Peltier
Time: Tuesday, December 22, 2009, 12:29 am
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.
Comment from Jack Underwood
Time: Wednesday, December 23, 2009, 7:48 am
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?
Comment from Jon Peltier
Time: Wednesday, December 23, 2009, 12:34 pm
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.
Comment from Ken Morison
Time: Tuesday, January 12, 2010, 3:31 am
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.
Comment from Jon Peltier
Time: Tuesday, January 12, 2010, 9:03 am
Thanks, Ken, for the explanation.
Comment from Christine
Time: Monday, February 8, 2010, 7:21 pm
Thank you so much for the utility! That saved my project!
Comment from Dale Dreyer
Time: Friday, February 26, 2010, 3:28 pm
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!
Comment from Clare Fisher
Time: Monday, March 1, 2010, 5:23 pm
Thank you soooo much, the utility is great!
Comment from Bruce Keefe
Time: Tuesday, March 2, 2010, 3:28 pm
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
Comment from Jon Peltier
Time: Wednesday, March 3, 2010, 8:07 am
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.
Comment from Colin Norris
Time: Wednesday, March 3, 2010, 8:51 am
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?
Comment from Bruce Keefe
Time: Wednesday, March 3, 2010, 9:22 am
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
Comment from Jon Peltier
Time: Wednesday, March 3, 2010, 10:50 pm
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.
Comment from Fink Nadine
Time: Thursday, March 4, 2010, 10:38 am
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
Comment from Jon Peltier
Time: Thursday, March 4, 2010, 11:30 am
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.
Comment from Fink Nadine
Time: Saturday, March 6, 2010, 4:11 am
Dear Jon,
many thanks. I am very grateful. I will try it today!
have a nice weekend
Nadine
Comment from Fink Nadine
Time: Saturday, March 6, 2010, 4:27 am
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
Comment from Jon Peltier
Time: Saturday, March 6, 2010, 8:56 am
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
Comment from Fink Nadine
Time: Monday, March 8, 2010, 7:49 pm
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
Comment from Mahmood
Time: Tuesday, March 9, 2010, 6:01 pm
Thanks so much Jon for this great post.
A friend of mine just needed the error bar utility disparately.
Many thanks again,
Mahmood
Comment from Mahmood
Time: Tuesday, March 9, 2010, 6:03 pm
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
Comment from FS
Time: Wednesday, March 31, 2010, 11:05 am
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!
Comment from Jon Peltier
Time: Wednesday, March 31, 2010, 12:57 pm
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.
Comment from Natalie
Time: Sunday, April 4, 2010, 9:07 pm
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!
Comment from J
Time: Friday, April 9, 2010, 2:19 pm
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?
Comment from Jon Peltier
Time: Friday, April 9, 2010, 6:44 pm
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?
Comment from J
Time: Friday, April 9, 2010, 6:45 pm
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
Comment from Jon Peltier
Time: Friday, April 9, 2010, 6:59 pm
Did you select a large enough range for all points in the series?
Comment from J
Time: Friday, April 9, 2010, 8:02 pm
Jon:
I figured how to make it work by changing the order of the columns in my graph. Thanks :)
Comment from Harini
Time: Tuesday, April 20, 2010, 3:04 pm
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
Comment from Sian
Time: Wednesday, April 28, 2010, 12:40 pm
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
Comment from Jon Peltier
Time: Wednesday, April 28, 2010, 7:39 pm
Sian – You should make sure you have service packs through SP2 installed for Excel 2007.
Comment from Jon Peltier
Time: Wednesday, April 28, 2010, 8:36 pm
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.
Comment from Delta
Time: Tuesday, May 11, 2010, 8:38 pm
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.
Comment from Sam
Time: Thursday, May 13, 2010, 8:15 pm
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
Comment from Azad Padamsey
Time: Thursday, June 3, 2010, 4:05 am
Hi,
I am using Excell 2003.The utility works great.However,is it possible to annotate the +/-
values on the error bars?
Comment from Jon Peltier
Time: Thursday, June 3, 2010, 6:30 am
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.
Comment from Azad Padamsey
Time: Thursday, June 3, 2010, 7:54 am
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
Comment from Jon Peltier
Time: Thursday, June 3, 2010, 8:11 am
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).
Comment from Bob Hamilton
Time: Thursday, June 24, 2010, 5:34 pm
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
Comment from Bob Hamilton
Time: Thursday, June 24, 2010, 5:50 pm
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
Comment from Jon Peltier
Time: Thursday, June 24, 2010, 7:57 pm
Bob – You need to select a series in the chart, then click on the button.
Comment from Jon Peltier
Time: Thursday, June 24, 2010, 8:00 pm
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).
Comment from ezra abrams
Time: Tuesday, July 6, 2010, 5:28 pm
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
Comment from Jon Peltier
Time: Tuesday, July 6, 2010, 9:00 pm
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.
Comment from Rebecca
Time: Friday, July 9, 2010, 8:49 pm
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!
Comment from Jon Peltier
Time: Saturday, July 10, 2010, 7:37 am
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.
Comment from Rebecca
Time: Monday, July 12, 2010, 7:36 pm
Hmmm, well that sucks. Thanks for the info!
Comment from FATIMA
Time: Monday, August 2, 2010, 4:51 pm
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.
Comment from Chuck
Time: Monday, August 9, 2010, 6:26 pm
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
Comment from Jon Peltier
Time: Monday, August 9, 2010, 6:55 pm
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).
Comment from Dan
Time: Friday, August 13, 2010, 11:11 am
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.
Comment from Zack
Time: Sunday, August 15, 2010, 4:56 pm
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
Comment from Jon Peltier
Time: Sunday, August 15, 2010, 6:57 pm
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).
Comment from David
Time: Tuesday, August 17, 2010, 5:13 pm
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.
Comment from David
Time: Tuesday, August 17, 2010, 5:21 pm
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
Comment from Tahli
Time: Wednesday, August 18, 2010, 9:24 pm
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
Comment from Jon Peltier
Time: Thursday, August 19, 2010, 9:28 am
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).
Comment from bill
Time: Monday, August 23, 2010, 1:24 pm
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
Comment from Jon Peltier
Time: Monday, August 23, 2010, 4:23 pm
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.
Comment from Marie
Time: Monday, August 30, 2010, 10:05 am
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.
Comment from Jon Peltier
Time: Monday, August 30, 2010, 11:40 am
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.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.