Excel generate and format horizontal error bars

Question

I have been trying to generate and format horizontal and vertical error bars in Excel 2010, and there is no problem with the vertical ones, but I cannot figure out how to manage horizontal error bars.

The main problem is described by this problem with the menu system:
(When you choose errors bars, Excel serves up menu options only for vertical error bars.)

I did notice that when you choose add error bars, while Excel only pops up the formatting options box for the vertical ones, it does also seem to generate horizontal ones. A second problem, though, is that I have not been able to select the horizontal error bars (any error bars, actually) in the chart area.

How can I get access to a Format Error Bars box for horizontal error bars?

• Ideally the box would just have a section for vertical error bars and a section for horizontal ones.
• The next best thing would be the ability to click on the "Vertical Error Bars" tab to change it.
• The next best thing would be to be able to select vertical or horizontal error bars when first choosing More Error Bars Options....
• At very least you should be able to select error bars in the chart area and right-click them to access the formatting options.

Can any of these things or anything like them be done?)

Answer 1

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.

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.

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.

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.