High-Low Line Alternatives in Excel Charts

High-low lines are a feature of Excel line charts that connect the high and low points at a given category. For example, in a stock chart the high-low lines connect the high and low value for each date in the chart.

Stock Chart with High Low Lines and Open-Close Ticks

A high-low line connects the highest of the values for a given category to the lowest of the values at that same category. All that is needed is two or more series with a point at that category.

If there are line series on primary and secondary axes, then you can have high-low lines for the primary axis group of series, and an independent set of high-low lines for the secondary axis group.

Connecting Any Two Points With High-Low Lines

An Excel user emailed me with a question: How can I connect any two points with high-low lines?

The user did not provide context for this question, so I’ve invented my own context.

Suppose I have two sets of points I want to use for high-low lines, and I want to plot two other series to show how (or whether) they fit within the limits defined by these high-low lines. The following data shows data for the two lines to be connected (Connect 1 and 2) and the two series to be compared (Series 1 and 2).

Data for the Arbitrary High-Low Lines

Here are these four series in a line chart:

Line Chart for the Arbitrary High-Low Lines

When high-low lines are added, they do not automatically connect the two series we want connected. The high-low lines connect the highest and lowest points, just like the name implies.

Line Chart with non-Arbitrary High-Low Lines

Excel is nothing if not flexible, and there are at least three ways to accomplish the goal of connecting only the Connect 1 and 2 series with high-low lines.

Adding High-Low Lines to an Excel Chart

In Excel 2003 and earlier it is easy and straightforward to add high-low lines to a set of line chart series. Double click any of them to open the Format Series dialog. Click on the Options tab, and check the High-Low Lines box. The high-low lines are automatically assigned to all series in the same axis group.

Adding High-Low Lines in Excel 2003

In Excel 2007 adding high-low lines is a bit more obscure. Select the series, then go to the Chart Tools > Layout tab. Over towards the right, in the Analysis group, click Lines, then High-low Lines.

Adding High-Low Lines in Excel 2007

High-low lines can be formatted the same way as any other line elements in a chart.

Arbitrary High-Low Lines Using Secondary Axis

Move Series 1 and Series 2 to the secondary axis.

Arbitrary High-Low Lines via Secondary Axis

Delete the secondary Y axis (Excel usually doen’t give us a secondary X axis if the chart types are the same). This keeps the series in separate axis groups, but all series use the existing axis scales.

Arbitrary High-Low Lines via Secondary Axis

Add high-low lines to Connect 1 and 2 on the primary axis. These lines now ignore Series 1 and 2.

Arbitrary High-Low Lines via Secondary Axis

Format as desired. I’ve used the high-low lines to indicate a range within which Series 1 and 2 values might be expected to lie.

Arbitrary High-Low Lines via Secondary Axis

Arbitrary High-Low Lines Using XY Series

High-low lines only connect line chart series, while ignoring XY and other series. We exploit this behavior by changing Series 1 and 2 to XY chart type series.

Arbitrary High-Low Lines via XY Series

Delete the secondary axis, or better yet, move Series 1 and 2 to the primary axis.

Arbitrary High-Low Lines via XY Series

Add high-low lines to Connect 1 and 2.

Arbitrary High-Low Lines via XY Series

And format as required.

Arbitrary High-Low Lines via XY Series

High-Low Lines Using Error Bars

You can use Y error bars to simulate high-low lines. You need to add a column to the data table which has the difference between Connect 2 and Connect 1. This column will be used for custom error bar values.

Data for the Arbitrary High-Low Lines

No need to change any series chart types or move series between axes. Just add error bars to Connect 1 using the added Delta column above as positive Y custom values.

Arbitrary High-Low Lines via Error Bars

Hide Connect 1 and 2 (format with no markers and no lines). In fact, Connect 2 doesn’t even need to be in the chart.

Arbitrary High-Low Lines via Error Bars

Error bars can be formatted like any other line element, so format away.

Arbitrary High-Low Lines via Error Bars

Adding Error Bars to an Excel Chart

I’ve covered error bars in Error Bars in Excel 2007 Charts. It’s straightforward in Excel 2003 and earlier, but remarkably obscure in Excel 2007. Progress, eh?

I didn’t just describe the Excel 2007 error bar aggravation in the article above. I built an add-in to make error bars easier to create in 2007. The add-in works in Excel 2007 but also in earlier versions. You can download the add-in from a link near the end of the article.

Dialog for Peltier Tech Error Bar Utility

High-Low Line Alternative Without Lines

I’m not sure why the user wanted high-low lines to apply to only some series in the chart. Possibly he was adding a data series to a stock chart, some kind of market index, and it was changing the high-low lines for his stock data. I covered this problem in Stock Charts in Excel 2007.

If the high-low lines are there to indicate for example an acceptable range for Series 1 and 2, keeping the Connect 1 and 2 lines might help delineate the limits defined by the high-low lines.

Arbitrary High-Low Lines via Secondary Axis

But all these lines are a distracting way to indicate a particular range on a chart. Why not build a shaded region to do this?

Change the data for Connect 2 so it uses the Delta column, then convert Connect 1 and 2 into stacked area chart type series.

High-Low Line Replacement via Area Chart Series

If desired change the X axis so the Y axis crosses “between categories” (2003) or “between tick marks” (2007).

High-Low Line Replacement via Area Chart Series

Format Connect 1 so it’s invisible (no border, no fill), then remove the border of Connect 2 and use a light shade for its fill.

High-Low Line Replacement via Area Chart Series

Peltier Tech Chart Utility

Comments

  1. Thanks Jon for this article as it helped me resolve a visualisation I needed to complete. One of the company’s problems is accuracy of the Forecasting process – basically, every month countries in the group provide a 3-month forecast on Revenue by Activity and, to date, this has often been way off the Actual results. Here is the link to how I have been able to complete it:

    We use Essbase as a repository for our Finance reporting, so the grey area is where I can get the user to retrieve the slice of data they want to look at (Country, Activity, Account…). I then do some calcs to get the data into the right shape for the chart (blue area) and the chart is automatically refreshed for their requested slice – Voila! as they say in these parts.

    Enjoy the coffee – well worth it!

  2. David –

    Thanks for the coffee. Your chart is a great example of the use of a shaded band to Show Uncertainty in Predictions with Shaded Bands.

  3. Hi Jon,

    Thanks so much for sharing this great tip. I know the Excel team would really love it if you shared your expertise with the rest of the Excel community at the Excel page on Facebook. Users are regularly looking for tips and tricks, and this would be a great one! You can join the community, here: http://www.facebook.com/office.

    Thanks again for sharing this great post with your readers! Great stuff!

    Cheers,
    Kim
    Microsoft Office Outreach

  4. Hi Kim – Anyone should feel free to add a link on the Facebook page to any of these articles.

  5. Thanks, Jon. And feel free to start up conversations and add your thoughts to the community, too!

    Cheers!
    Kim
    Microsoft Office Outreach

  6. Debbi Barnes-Josiah says:

    Thanks Jon, once again you’ve solved an annoying problem! (adding an additional series to a high-low graph).
    :)

  7. Greetings,
    In a discussion of controlling High-Low Lines, directions include: “Move Series 1 and Series 2 to the secondary axis. Delete the secondary Y axis (Excel usually doesn’t give us a secondary X axis if the chart types are the same)” …
    I read this as first create a secondary Y-Axis & then turn right around and delete it (i.e., the secondary Y-Axis). Is my understanding correct? If so, I do not see (or understand) how one would “Delete the secondary Y axis …” …
    Thank you …

  8. Mark –

    This is pretty confusing. We want to plot the series using the same axes, but we need them to be treated as though they belong to different groups of data. Placing some on the secondary axis provides this grouping.

    So select each of the indicated axes, and format them so they’re on the secondary axis. Behind the scenes, this puts these series into the secondary axis group. Then when you delete the secondary axis that appears, these series are still considered in the secondary axis group, even though they are plotted using the primary axes.

Subscribe without commenting

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.