AJP Excel Information AJP Excel Information

Label high-low line with values

 
 

Use an additional data series plotted on the secondary axis to add values to the high-low lines. 
 
 
A B C D E
1 Actual Expect

HI-Lo

Label

2 Jan 8 6 =B2+((C2-B2)/2) =C2-B2
3 Feb 7 5 =B3+((C3-B3)/2) =C3-B3
4 Mar 7 1 =B4+((C4-B4)/2) =C4-B4
5 Apr 1 3 =B5+((C5-B5)/2) =C5-B5
6 May 9 10 =B6+((C6-B6)/2) =C6-B6
7 Jun 3 5 =B7+((C7-B7)/2) =C7-B7
8 Jul 1 7 =B8+((C8-B8)/2) =C8-B8
9 Aug 8 4 =B9+((C9-B9)/2) =C9-B9
10 Sep 6 6 =B10+((C10-B10)/2) =C10-B10
11 Oct 1 10 =B11+((C11-B11)/2) =C11-B11
12 Nov 6 6 =B12+((C12-B12)/2) =C12-B12
13 Dec 8 6 =B13+((C13-B13)/2) =C13-B13
The formulas in column D define the mid-point position between the 2 lines.
The formulas in column E define the actual distance between the 2 lines and are also used as the category labels for the secondary x axis. Whilst the secondary x axis is not displayed the values can be used automatically by the data labels.
 
 
Using the chart wizard create a line chart based on the range A1:D13
 
 
Double click the Hi-Lo data series and change the series to the secondary axis
 

 
Use the chart options dialog to enable the secondary x axis.
 

 
Use the Source data dialog do set the secondary category axis labels to the range E2:E13
   

 
Format the secondary x axis so the tick marks and labels are not displayed.
 

 
Apply data labels to the Hi-Lo series. Display the Category values.
 
   
 
Apply the High-Low lines the to series and format the line to have no pattern.
   
   
 
Double click the data labels and change the label position to Center.
   

   
 
Final tidy up the formatting of the high-low lines.
Set the background font to Opaque so the values are not obscured by the lines.
Remove the extra data series from the legend.
   

   
   


Created August 2004
Last updated 5th August 2014 


Return to main page Chart Section VBA section Fun and games section Forum files Tips section Links section Book section Site information Site Search RSS feed Top of page


Microsoft® and Microsoft® Excel are registered trademarks of the Microsoft Corporation.
andypope.info is not associated with Microsoft. Copyright ©2007-2016 Andy Pope