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.
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).
Here are these four series in a line chart:
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.
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.
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.
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.
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.
Add high-low lines to Connect 1 and 2 on the primary axis. These lines now ignore Series 1 and 2.
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 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.
Delete the secondary axis, or better yet, move Series 1 and 2 to the primary axis.
Add high-low lines to Connect 1 and 2.
And format as required.
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.
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.
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.
Error bars can be formatted like any other line element, so format away.
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.
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.
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.
If desired change the X axis so the Y axis crosses “between categories” (2003) or “between tick marks” (2007).
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.
Floating Columns/Bars
Floating bars or columns in a chart can be a good alternative to High-Low Lines. See Floating Bars in Excel Charts for a detailed discussion of all the ways to create floating bars in Excel charts.
DMurphy says
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!
Jon Peltier says
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.
Kim_Office_Team says
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
Jon Peltier says
Hi Kim – Anyone should feel free to add a link on the Facebook page to any of these articles.
Kim_Office_Team says
Thanks, Jon. And feel free to start up conversations and add your thoughts to the community, too!
Cheers!
Kim
Microsoft Office Outreach
Debbi Barnes-Josiah says
Thanks Jon, once again you’ve solved an annoying problem! (adding an additional series to a high-low graph).
:)
Mark Smith says
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 …
Jon Peltier says
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.
Marcus Plyhr says
Hi,
Is it possible to have low/high bars in the same charts as a column and line chart?
Jo Weller says
Hi Jon,
This article is very insightful and I like this function because when the high low values are changed, the lines move with it. That said, I am now using MS-Office 2013 and Excel appears to have removed this feature from regular graphing. I went to the MS help and it tells me to draw my high low lines. By drawing them, when the values change the drawings do not. Any suggestions?
Jon Peltier says
Hi Jo –
It’s still there, of course. Microsoft just moved it. On the Chart Tools > Design tab, click the Add Chart Element button at the far left of the ribbon, click on Lines in the dropdown, then you’ll see High-Low Lines in a pop-up list.
Jo Weller says
Thanks Jon, it makes sense where Micro Soft placed the High Low option for graphs but I couldn’t see it for the “forest”.
So now, would you have a blog about combining 2 different data sets using the high low option? The Table below has sample data and when graphed the Data Series are displayed as a vertical bar and the FY 2015 Benchmarks are the High Low on the primary axis and the FY 2013 Benchmarks are the High Low on the secondary axis. There is a Data Series XXX and a Data Series YYY and I’d like to place them on one single graph. I can make a graph for each separately, but I’ve never been able to successfully overlay one graph with another and I’m not sure that can be done for this type of graph. If you know how to do what I’m asking for, could you please share on your blog? Thanks
Data Series Q1FY2015 Q2FY2015 Q3FY2015 Q4FY2015
Data Series XXX 1.12 0.95 1.02 0.83
FY 2015 Top Quartile Benchmark Data Series XXX 0.83 0.83 0.83 0.83
FY 2015 Median Benchmark Data Series XXX 0.94 0.94 0.94 0.94
FY 2013 Top Quartile Benchmark Data Series XXX 0.81 0.81 0.81 0.81
FY 2013 Median Benchmark Data Series XXX 0.97 0.97 0.97 0.97
Data Series YYY 0.74 1.22 1.01 1.10
FY 2015 Top Quartile Benchmark Data Series YYY 1.04 1.04 1.04 1.04
FY 2015 Median Benchmark Data Series YYY 0.65 0.65 0.65 0.65
FY 2013 Top Quartile Benchmark Data Series YYY 1.20 1.20 1.20 1.20
FY 2013 Median Benchmark Data Series YYY 0.94 0.94 0.94 0.94
Jon Peltier says
Instead of using high-low lines, you could compute the difference in another column, and use this as the values in a set of custom error bars.
Jo Weller says
Bingo! That’s exactly what I was looking for. Thank you.