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.