Error Bar Ideosyncrasy

After using Excel 2007 for a while, getting used to the idea that everything had changed and we weren’t going back to 2003 ever again, I started fooling around with charts and chart elements. I began to find lots of little changes, most of them actually good ones, even if they were implemented in a funny way (at least until service packs and Excel 2010 cleaned up things).

Anyone who used my first charting utility knows things ain’t always done right the first time, and mine were tiny bits of software. I can’t imagine Microsoft’s task trying to coordinate thousands of developers and a bazillian lines of code.

One of the neat changes I discovered was that error bars were now treated as AutoShape lines, just like any lines in any shapes in Office. This means you had a lot of formatting options available to you, including nice embellishments like arrowheads on the ends of the error bar line segments. Then I forgot about it until a colleague brought it up recently.

To show this nice formatting, let’s look at a simple line chart with simple error bars. For clarity, and to avoid spoiling the story before I’m ready to tell it, I’ll start with positive error bars only.

Simple Line Chart with Simple Error Bars

Select the error bars and click Ctrl+1 (numeral one) to open the Format Error Bars task pane. In the main tab (below the bar chart icon, shown below left) change End Style from Cap to No Cap. Then on the formatting tab (below the paint can icon) check out the Begin and End Arrow Types.

This is the Format Error Bars task pane in Excel 2013; the Format Error Bars dialog in Excel 2007 and 2010 is substantially the same.

Format Error Bar Dialog

The error bar begins at the point, right? And ends at the, uh, end of the error bar, right? At least that’s what seems to make sense. So let’s pick a nice big round ball for the Begin Arrow Type.

Balls at Beginning of Error Bars

And let’s pick a nice big old arrow for the End Arrow Type. I’ve chosen the largest size for both ends of the error bar.

Arrowheads at End of Error Bars

And here’s our line chart with error bars, where the markers of the line chart could be replaced by the balls at the beginning of the error bars, and the arrows point away from the line.

Simple Line Chart with Fancy Ball and Arrowhead Error Bars

This is a very nice embellishment. I’ve used it in several projects already, and shown it to many people.

Anyway, back to the beginning of the story: the message from my colleague was that Excel 2010 and 2013 seem to define the beginnings and ends of the error bars differently.

Well, for positive error bars, Excel 2007, 2010, and 2013 all look the same.

Simple Line Chart with Simple Error Bars

But if we look at negative error bars, only Excel 2013 shows them beginning at the points and ending at the ends of the error bars, pointing downward. Excel 2007 and 2010 show the balls at the ends of the error bars and the arrowheads at the points, pointing upward. That’s bizarre.

Simple Line Chart with Simple Error Bars

Even more bizarre, if the error bars go both ways, only Excel 2013 has symmetric arrows. Excel 2010 shows both sets of arrows pointing upward, the negative ones toward the points, the positive ones away from the points. And Excel 2007 only has one set of arrows, beginning at the end of the negative error bars, ending at the ends of the positive error bars. Though if you had to, you could just pretend the end was the beginning and vice versa. Whatevs.

Simple Line Chart with Simple Error Bars

Excel 2007’s charting infrastructure was finished quickly at the end of the development cycle, so a few glitches weren’t ironed out of the final product. Excel 2010 fixed a lot of inconsistencies in Excel 2007’s charts, but these error bars show that not everything was totally fixed. In Excel 2007 or 2010 you could fake it with a second, hidden plotted series, and format two sets of error bars so they looked right. Excel 2013 has gotten the error bars working nicely, though.

 

Peltier Tech Charts for Excel

Custom Axis Labels and Gridlines in an Excel Chart

The Problem

I came across a post on the Super User forum whose author wanted to Get Excel to base tick marks on 0 instead of axis ends (with fixed maximum or minimum). Essentially, the user wanted to scale the axes of his plot to ±35, but have axis labels every 10 units centered on zero, not starting at -35. The desired chart is shown here:

Chart With Axis Labels and Gridlines Where You Want Them

It seems like this should be easy in any charting software. It was a built-in feature of the first charting software I ever used, but then, I wrote that software myself in the mid-1980s. However, it’s not part of Excel’s charting engine, nor of many other charting packages.

Positioning your own custom axis labels and gridlines is not too difficult to do, by dispensing with the default labels and gridlines, and using dummy series along the X and Y axes, with data labels and error bars on these series.

Here is the data used in this tutorial. The First and Second blocks are used for the chart’s main series; the Axis block is used for both dummy series; and the Err Bar block is used if you don’t want the error bars to obscure the default axis lines.

Data for Custom Axis Labels and Gridlines

The Original Chart

Here is the XY Scatter chart of the First (blue) and Second (orange) data sets. I guess it’s a question mark symbolizing the confusion expressed by the original questioner.

Default Excel 2013 Chart

Here is the chart, squared-up so the gridlines outline approximately square regions. At this scale, the default axis limits are ±40.

Squared-Up Chart with Default Axis Scales

The margin around the plotted points is wider than desired, but any attempt to place the axis labels on multiples of 10 result in something like the chart above (±40) with too wide a margin, or the chart below (±30), with no margin.

Squared-Up Chart Imposing ±30 Axis Scales

Setting the axis scales at ±35 also locks the axis labels at half-multiples of 10 (at the 5’s: -35, -25, etc.).

Squared-Up Chart With ±35 Axis Scales

Adding Custom Axis Labels

We will add two series, whose data labels will replace the built-in axis labels. The horizontal axis dummy series (gray line and circle markers) uses the column of numbers (E2:E8) as X values and the column of zeros (F2:F8) as Y values. The vertical axis dummy series (yellow line and circle markers) uses the same column of numbers (F2:F8) as X values and the column of numbers (E2:E8) as Y values.

Chart With Dummy Series Along X and Y Axes

Select the vertical dummy series and add data labels, as follows. In Excel 2007-2010, go to the Chart Tools > Layout tab > Data Labels > More Data label Options. In Excel 2013, click the “+” icon to the top right of the chart, click the right arrow next to Data Labels, and choose More Options…. Then in all versions, choose the Label Contains option for Y Values and the Label Position option for Left. The labels are (temporarily) shaded yellow to distinguish them from the built-in axis labels.

Data Labels Added to Dummy Vertical Axis Series

Select the horizontal dummy series and add data labels. In Excel 2007-2010, go to the Chart Tools > Layout tab > Data Labels > More Data Label Options. In Excel 2013, click the “+” icon to the top right of the chart, click the right arrow next to Data Labels, and choose More Options…. Then in either case, choose the Label Contains option for X Values and the Label Position option for Below. The new labels are shaded gray to set them apart from the built-in axis labels.

Data Labels Added to Dummy Horizontal Axis Series

Select each axis in turn, and select the None option for Axis Label.

Default Axis Labels Have Been Removed

Adding Custom Gridlines

We will use error bars to the dummy axis series to replace the built-in gridlines. Select the horizontal dummy series and add error bars as follows. In Excel 2007-2010, go to the Chart Tools > Layout tab > Error Bars > more Error Bar Options…. In Excel 2013, click the “+” icon to the top right of the chart, click the right arrow next to Error Bars, and choose More Options…. In all versions, default horizontal and vertical error bars of length 1 will appear on the data series.

Default Error Bars Added to Dummy Horizontal Axis Series

Select the new horizontal error bars. You may have to use the Current Selection dropdown on the Chart Tools > Layout or Format tabs or on the right click formatting popup to select them. Click Delete.

Select the vertical error bars. Format them to have an Error Amount (length) of 35 and no end caps. I’ve kept the default error bar line color of black, to distinguish them from the built-in gridlines.

Horizontal Error Bars Removed, Vertical Error Bars Scaled to Chart Height

Select the vertical dummy series and add error bars in the same way. In Excel 2007-2010, go to the Chart Tools > Layout tab > Error Bars > more Error Bar Options…. In Excel 2013, click the “+” icon to the top right of the chart, click the right arrow next to Error Bars, and choose More Options…. In all versions, default horizontal and vertical error bars of length 1 will appear on the data series.

Default Error Bars Added to Dummy Vertical Axis Series

Select the new vertical error bars. Again, you may have to use the Current Selection dropdown on the Chart Tools > Layout or Format tabs or on the right click formatting popup to select them. Click Delete.

Select the horizontal error bars. Format them to have an Error Amount (length) of 35 and no end caps. The error bars are shown below in their default black line color, to distinguish them from the built-in gridlines.

Vertical Error Bars Removed, Horizontal Error Bars Scaled to Chart Width

Select each set of built-in gridlines (not the error bars we’ve just added so painstakingly) and press Delete.

Default Gridlines Have Been Removed

Format the error bars to have a light gray line color.

Custom Gridlines (Error Bars) Have Been Lightened

Finally, hide the two dummy axis series, by formatting them to have no markers and no lines.

Dummy Axis Series Have Been Hidden (No Lines, No Markers)

Note that the error bars on the (0,0) points of the dummy series block the axis lines, which are slightly darker gray. You may wish to keep the darker axis lines to help clarify the chart. This means applying custom error bar values, with 35 for all points except zero for the point at the origin.

Custom Error Bars that Don’t Obscure the Axis Lines

Select the vertical error bars. On the format dialog, under Error Amount, select Custom (they will suddenly transform to very short error bars with length 1), then click Specify Value. Delete the “={1}” from the Positive Error Value box, and select the range with custom error bar values (G2:G8). Then delete the “={1}” from the Negative Error Value box, and select the range with custom error bar values (G2:G8). Press Okay.

Select the horizontal error bars and repeat the steps above to assign the range G2:G8 as Custom Error Amount for the positive and negative error bars.

The result is shown here, with light gray error bars and slightly darker axis lines.

Custom Error Bar Values Added to Reveal Default Axes

This tutorial is just another example showing how to enhance your chart in ways that were not built into Excel’s extensive charting capabilities. Using other capabilities, there are numerous ways to add lines, labels, and other features to an Excel chart exactly where you want them. Just remember that you are controlling Excel, and not vice versa.

 

Peltier Tech Charts for Excel

OHLC Stock Chart with Tick Marks in Microsoft Excel

Candlestick Charts

Microsoft Excel has some built-in chart types for displaying how stock prices vary during a trading period. This is usually a day, but it could also show weeks, months, or years, or even intervals of minutes or hours during a trading day. These are the prices at open and close of the market as well as the high and low while the market is open. For example, the Open-High-Low-Close (OHLC) candlestick chart below was designed to show the high and low for a trading period as the top and bottom of the vertical line for each period, and the change from open to close as a rectangle, filled white for an increasing value or black for a decreasing value.

Open-High-Low-Close Candlestick Chart in Excel

To make this chart type, you need five columns of data, in order Date (or other label of the trading period), Open, High, Low, and Close. If you omit the dates or labels, Excel will build the chart and label the periods 1, 2, 3, etc. Select the data, then go to the Insert tab > Other Charts, and pick the second stock chart type.

I guess I’m too dumb to remember such a straightforward convention, but I always have to check whether the white or black bars refer to increasing or decreasing values. So I like to color the bars red and green, like so:

Open-High-Low-Close Candlestick Chart with Color Coded Candles

The problem is that the colored bars do not photocopy well in black and white. Also, while the red and green colors in Excel’s default theme are distinguishable by most people with color vision deficiencies, I can’t imagine the colors they see are as easy to distinguish as red and green, or as black and white, for that matter.

Stock Charts with Open and Close Tickmarks

The best approach then is to use the OHLC chart style found in newspapers. These have a vertical line to indicate the spread from low to high during a trading period, with a small tickmark to the left indicating the opening price and a small tickmark to the right at the closing price. Excel has a High-Low-Close stock chart with the vertical line and a single tickmark, to the right, to signify closing price. But no leftward tickmark.

So we must resort to some smoke and mirrors. Actually, some short horizontal error bars will serve as our tickmarks.

Start with the data arranged in the same way, but create a line chart instead of a stock chart.

Line Chart Using OHLC Data

Select any series in the chart, go to the Chart Tools > Layout tab, click on Lines, and choose High-Low Lines.

Line Chart with High-:ow Lines

Format the High and Low series to remove the line color.

OHLC Line Chart with High Low Lines Hidden

Change the Open and Close series to XY: right click on each series in turn, select Change Chart Type, and choose an XY type.

OHLC Chart with Open and Close Changed to XY

These XY points don’t line up and there are misaligned secondary axes, but this is simple to fix. Format both XY series to assign them to the primary axis.

XY Series Moved to Primary Axis

Format the Open and Close series to hide the markers.

OHLC chart with Invisible Open and Close Markers

Select the Open and Close series in turn, and add error bars. There are no markers to click on, but if you select the chart you can find the Chart Element selector on the Chart Tools > Layout and Format tabs. The Chart Element selector and the pop-up tool tip are highlighted with a red border in this screen shot. This is a handy tool to add to your arsenal.

Chart Element Selector in the Ribbon

Use the Chart Element selector to select the Open and Close series, one at a time. (You could also select a visible series or another visible chart element, and use the up or down arrows to cycle through the chart’s elements until the desired series is selected.)

Select a Series Using the Chart Element Selector

With the series selected, go to the Chart Tools > Layout tab, select Error Bars, and choose one of the options. I simply used the Standard Error option, since it was easier to add everything and adjust or remove the parts as needed. Repeat for both Open and Close.

Open and Close Series with Error Bars

Reminds me of a swarm of starfighters from Star Wars.

Select the vertical (Y) error bars for each series. You may want to use the Chart Element selector for this.

Select Error Bars Using the Chart Element Selector

Select the vertical (Y) error bars, and remove them by pressing the Delete key.

Vertical Error Bars Are Gone

Select the “Open” X Error Bars, and press Ctrl+1 to open the Format dialog. Select the Minus direction and the No Cap end style, and enter a fixed value of 0.4.

Select the “Close” X Error Bars, and press Ctrl+1 to open the Format dialog. Select the Plus direction and the No Cap end style, and enter a fixed value of 0.4.

The vertical lines are one unit (one trading period) apart, so 0.4 reaches almost halfway to the next vertical line on either side.

Error Bars Have Become Open and Close Tickmarks

This style of OHLC chart works well in black and white, and after the first time you go through the protocol, it doesn’t take too long to set up.

 

Peltier Tech Charts for Excel

Custom Error Bars in Excel Charts

I’ve written about Excel chart error bars in Error Bars in Excel Charts for Classic Excel and in Error Bars in Excel 2007 Charts for New Excel. Both articles contained instructions for adding custom error bar values for individual points, but judging from the emails I receive, a separate article on custom error bars is needed. You cannot add custom error bar values to a single point in a chart. However, you can individual custom error bar values to all points in a plotted series. You need to put all of the individual error bar values into a range of the worksheet. I usually put these values in the same table as the actual X and Y values

Manually Defining Custom Error Bars

Sample Data and Charts

Suppose we have the following data: X and Y values, plus extra columns with positive and negative error bar values for both X and Y directions. The data is set up so that, for example, cells C2 and D2 have the values for the positive and negative horizontal (X) error bars for the point defined by X and Y values in A2 and B2. Cells E2 and F2 have the values for the positive and negative vertical (Y) error bars for this point. The series is plotted using all the data at once, with X in A2:A6 and Y in B2:6. The error bars are also drawn using all the error bar data at once: C2:C6 and D2:D6 for horizontal and E2:E6 and F2:F6 for vertical.

Error Bar Data

The chart itself is easy: create an XY chart using the data in columns A and B.

XY Chart That Needs Error Bars

The protocols for adding custom error bars differ between Classic Excel and new Excel. After following the appropriate protocol below, the chart will have custom error bars on each data point, based on the additional columns of data. This chart shows just the Y error bars, to show clearly that each point has custom values different from other points:

XY Chart With Custom Y Error Bars

This chart shows the X and Y error bars:

XY Chart With Custom X and Y Error Bars

Important Note

A single custom error bar value cannot be added to a single data point, and custom error bar values cannot be added to a series of data points one point at a time. If you select a single value for your custom error bars, this single value will be applied to all points in the series. A whole set of custom error bar values can be added to an entire series in one operation. Put your custom values into a range parallel to your X and Y values as I’ve done with this sample data, then use the manual technique or the utility to add all the values to the chart series in one step.

New Excel (2007 and later)

It is harder to apply error bars in Excel 2007 than in earlier versions. 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. Error Bar Commands on the Ribbon Finally, select the More Error Bars Options at the bottom of the list. X (if it’s an XY chart) and Y error bars with initial constant values of 1 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. But there is no obvious way to switch to the horizontal error bars. We are used to having two tabs, one for the vertical error bars, and also one for the horizontal error bars. Remember that Microsoft made these chart formatting dialogs non-modal, so you can click on objects behind the dialogs. Click on the horizontal error bars to change the dialog. To assign custom values to the error bars, select the horizontal or vertical error bars, and on the Horizontal or Vertical Error Bars tab of the Format Error Bars dialog. Move the dialog so it does not cover the range containing your custom values, then click on the Custom option button, and click on Specify Value. A small child dialog appears with entry boxes for selection of the custom error bar values. (It was easier in 2003, where data entry took place directly on the main dialog, but we’re not talking about productivity today.) Custom Error Bars dialog 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 the following, which will lead to an error message. The edit box is so narrow, that you cannot see the entire expression at once, and it will be difficult to find this error.

={1}+Sheet1!$D$2:$D$6

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.

Classic Excel (2003 and earlier)

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. Classic Excel Format Series Error Bar Dialog To define custom error bars, click in the + or – data entry box (no need to select the Custom option button, Classic Excel does this automatically), then select the range containing the custom error bar values using your mouse. If you need only one value for all of the points, you can select a single cell, or even type the value you want. This seems redundant, given the Fixed Value option, but this way you can use different positive and negative fixed values or a custom range for one direction and a constant for the other. This is what the dialog looks like with ranges used to define the custom error bar values. Defining Custom Error Bar Ranges in Classic Excel If you want to leave an error bar off the chart, you can leave the data entry box blank.

Notes

The error bars overwhelm the data. To restore the importance of the data itself, use a lighter color for the error bars. Lighten up the axes while you’re at it. XY Chart With Error Bars If any custom values are negative, the corresponding error bar will be drawn in the opposite direction: a positive error bar with a negative value will be drawn in the negative direction.

Programmatically Defining Custom Error Bars

The command to add error bars using Excel is:

{Series}.ErrorBar Direction:={xlX or xlY}, Include:=xlBoth, Type:=xlCustom, _
    Amount:={positive values}, MinusValues:={negative values}

Values can be a single numerical value, for example, 1, an comma-separated array of numerical values in curly braces, such as {1,2,3,4}, or a range address in R1C1 notation. For values in Sheet1!$G$2:$G$10, enter the address as Sheet1!R2C7:R10C7. Combine both plus and minus in the same command. In Excel 2007, if you don’t want to show a particular error bar, you must enter a value of zero in this command. In 2003, you can enter a null string “”. In Excel 2003, the range address must begin with an equals sign, =Sheet1!R2C7:R10C7; Excel 2007 accepts the address with or without the equals sign. Single values or arrays may be entered with or without the equals sign in either version of Excel.

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. Chart Series Context Menu Showing Add-Error-Bars Command Despite all the assurances from Microsoft 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. Chart Tools Ribbon Tab PTS-Tools/Error-Bars Command 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. Error Bars Utility XY Chart Dialog If the chart type is not XY, the X error bar entry boxes are disabled. Error Bars Utility Line Chart Dialog You can select a range or enter a constant into the entry boxes. Leave a box blank to omit the corresponding error bars. Error Bars Utility Populated Dialog I hope that this tutorial and the associated utility will make your life easier when working with error bars in Excel 2007.

 

Peltier Tech Charts for Excel

Peltier Tech Charts for Excel 3.0