AJP Excel Information

Charts













 
 

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.
To keep the series on separate axes aligned you can then delete the secondary value axis
 

 
Apply data labels to the Hi-Lo series. Display the Category information.
 
   
 
Select either the Actual or Expected series and apply High-Low lines.
Select the Hi-Lo 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.
   

   
 
 
 
   

Last updated 8th April 2010

 
 
  Home | Charts | VBA Code | Fun Stuff
Newsgroups | Tips | Links | What's New | Book List
Contact | About
Microsoft® and Microsoft® Excel are registered trademarks of the Microsoft Corporation.
andypope.info is not associated with Microsoft. Copyright ©2007-2009 Andy Pope