Error Bars in Excel 2007 Charts
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
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.
Possibly Related Posts:
- Candlestick Alternative: Individually Colored Up-Down Bars
- Waterfall Chart Utility
- Callout Labels with Error Bars
- Draw an Axis With Tick Marks But No Line
- Marimekko Charts
- Deming Regression Utility
- How To: Assign a Macro to a Toolbar or Menu
- Simple Waterfall Chart with Up-Down Bars
- Excel Utilities in the Works
- Growth Rates in a Panel Chart
Posted: Wednesday, December 10th, 2008 under Excel 2007.
Comments: 61
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
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.