 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
| |
Also see our huge range of Charting Software . Got a Excel Chart question? Use our FREE Excel Help
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. (for older version of Excel that's display the Show Label option).
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.
Back to Excel Charts Index
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates