Excel 2007

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 no obvious way to switch to the horizontal error bars. We are used to having 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 in the chart, 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 in that order, 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.

Custom error bars are described in more detail in Custom Error Bars in Excel Charts.

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.