To set error bars for an XY plot, it is necessary to select first the chart and then one of the options shown when "Error Bars" is clicked in the Layout
section of the Chart Tools
tab.
Excel's default behavior is to create both vertical and horizontal error bars on the chart. If you want to have only horizontal error bars, there are two different ways to go.
One way is to hide the vertical bars. That can be accomplished at the very first step when "Error Bars" is clicked. From the dropdown menu, select More Error Bars Options
.
A dialog box will open up for the settings for the vertical bars, with no reference at all to setting the horizontal bars, as you discovered. Choose Line Color
and set the radio button for "No line". The vertical bars will still be there, but not be visible.
Another way is to actually delete the vertical bars. In order to do that, they must be selected with a left-click of the mouse. Pressing the Delete key will remove them. (As a result of the deletion, Excel will rescale the vertical axis. If you don't like the result, you can set a fixed maximum value for the axis).
To format the horizontal bars, they must be selected. Once they are, a formatting box for the horizontal bars will pop up if you select "More Error Bar Options" from the ribbon or if you right-click and choose Format Error Bars
from the context menu.
![enter image description here](../../I/static/images/4e23ad7e5a434992f4d513fe11fcb1d96c3600f6d099085baf667021185e63b6.png)
However, the problem you were running into was that, due to the nature of your data, neither the vertical or the horizontal bars could as a practical matter be selected. They were just too small relative to the size of the data markers.
Two options for getting around this:
The first is what I suggested in my comment: Zoom the view of the spreadsheet to 400%, the maximum possible. Then, select one of the data points and temporarily remove the data marker (just the marker, not the point itself). The error bars that were covered by the data marker will be revealed and can be selected in order to delete (or hide) the vertical bars and to format or rescale the horizontal bars.
![enter image description here](../../I/static/images/9427ce5bbcc19c9fdda26e18ca6f18b0f8ec6f1856b1de8cf67ce86debb02362.png)
The second option is less fun, but still easy. At normal magnification, select the chart and make sure the Layout tab of the Chart Tools on the ribbon is chosen. At the far left of the ribbon is the Selection bar for the layout tools. Click the drop-down menu on the bar and you will see that the x-axis error bars and the y-axis error bars (as well as other chart elements) can be directly chosen.
![enter image description here](../../I/static/images/9283f79b28ff1d6685c6e8b8bd2e9a7711e5820ee5a8c9e67e1e5385a5238984.png)
Choose the x-axis bars to format or rescale the horizontal bars; choose the y-axis bars to delete them or to hide them. If you select the x-axis bars and then choose Error Bars
->More Error Bars Options
from the ribbon, the dialog box for formatting the horizontal bars will open up. Similarly for the y-axis, or vertical bars.
Is there some other way other than with the Layout tab of Chart Tools? (Yes, it is for an XY plot.) As far as selection goes, for me clicking anywhere near the error bars always just selects the series, possibly because the error bars start very small. Try shrinking the horizontal error bars in your chart to a very small size, and then see if you can select them. How can it be done? (By the way, the "Format Error Bars" box you have pictured is for vertical bars. Are you even able to change the length of your horizontal error bars? I see they are of default length (1) now.) – A.M. – 2013-07-27T22:15:56.853
Yes, you can do it directly by selecting elements of the chart and right-clicking. The picture shows the effect of setting the
Line Color
of the vertical error bars to "No line." I was also able to select the horizontal bars; on right-clicking them, aFormat Error Bars
dialog for the horizontal bars comes up, with the same options as the vertical bars. To figure out a work-around with narrow bars, I set the bar width to 1%. In order to select the bars, I zoomed the view to 400%, temporarily hid one of the data points (withMarker Options
), and was able to select the bars. – chuff – 2013-07-27T23:04:59.510If I right-click a point, I do not get any options for error bars, so as far as I can tell,
Chart Tools
>Layout
is the only way to get error bars. Zooming to 400% and setting "Marker Type" to "None" did make the error bars (already created throughLayout
) selectable, though, and the horizontal and vertical error bars were individually selectable. From there, a right-click got me to the right formatting box. Thanks. If you change your answer to that, I will accept it. (Showing a different screenshot of just the small horizontal bars selected would be helpful too, of course.) – A.M. – 2013-07-27T23:43:41.200See my amended (and improved) answer! – chuff – 2013-07-28T05:31:36.240
Wow. The last part of your answer is even better. I couldn't believe hiding markers and zooming was necessary. That *drop-down menu* is key! – A.M. – 2013-07-28T15:24:28.053