Here you can adjust settings pertinent to error bars.

General error bar settings

Preferred error bar direction:

  • Automatic: Lets the XL Toolbox automatically determine the most appropriate direction for your error bars. In bar/column charts, if a series consists of positive values only, error bars will be upward-pointing (positive); they will be downwards-pointing (negative) for series consisting entireley of negative values; and they will point in both directions if there are both positive and negative data in the chart series. In line/scatter plots, the XL Toolbox finds the best combination of positive/negative error bars where there is the least overlap of data points and error bars. There will often be some overlap because error bar directions can only be set for a whole series, not for individual data points.

  • The other options (positive, negative, both positive and negative) are self-explanatory.

User control

Holding the SHIFT key while clicking on the “Error bars” button in the toolbar/Ribbon alters the behavior of the command.

The options are: “Look for error data in my non-preferred location” (i.e., to the right instead of underneath and vice versa) or “Switch to interactive mode”. Switching to interactive mode is not useful in Excel 2007 where both modes have individual buttons on the Ribbon.

Automatic detection of error data

You can determine where the XL Toolbox should look for your error data: Either to the right of or underneath the chart data.

The Toolbox can locate your error values in two different modes:

  • Standard mode (offset-based): In standard mode, the XL Toolbox takes the error values from a certain number of rows to the right or (or columns below) the chart data. Adjust the ‘offset’ setting to your liking.
  • Advanced mode (look for empty cells): When this option is used, the Toolbox employs a more sophisticated algorithm to automatically detect the cells containing the error values: It looks for an empty column to the right of (or an empty row below) the cells containing the chart data, and uses the adjacent block of values as error values. This enables automatic error bars in more complex settings; you can read more about it on the page about smart error bars.

Error bar style

The XL Toolbox can also apply basic formatting to the error bars. Check the appropriate boxes if you want the chart series’ color or line weight (width) to be applied to the error bars.

Note: When you create a chart in Excel, the color of the chart series is initially set to “automatic”. The XL Toolbox cannot access the color if it is set to “automatic”. If you want to use the chart series’ color for the error bars, make sure that the chart series has a user-defined color.