Excel generate and format horizontal error bars

Solution 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.