Horizontal Stock Chart.

Want a stock chart that's always going up? Seriously, how do you switch a stock chart's Date and Value axes? Well, you can't do this to Excel's built in stock charts, but you can chart exactly the same information in a scatter chart with error bars.

Start with this data in cells A1:F15. Instead of Date-Hi-Lo-Close required by Excel's stock charts, move the Date to the right of the closing price to facilitate creation of the chart. Cell E2 contains the formula =A2-C2, which computes how long the High error bar should be, and cell F2 contains the formula =C2-B2, which computes how long the Low error bar should be. These formulas are copied or dragged as far down the columns as needed.

 Hi      Lo    Close      Date      Up    Down
1092    1006    1058    11/15/02    34     52
1073    1020    1053    11/14/02    20     33
1080     956     993    11/13/02    87     37
1051     902     982    11/12/02    69     80
1054     957    1013    11/08/02    41     56
1091     963    1007    11/07/02    84     44
1092     946    1003    11/06/02    89     57
1049     915     989    11/05/02    60     74
 998     911     929    11/04/02    69     18
1010     903     941    11/01/02    69     38
1001     897     916    10/31/02    85     19
 962     816     912    10/30/02    50     96
1008     909     933    10/29/02    75     24
 972     864     911    10/28/02    61     47

Select the Close and Date data in C1:D15, and run the Chart Wizard to create an XY Scatter Chart. The closing values are plotted along the horizontal axis, the dates along the vertical axis. I selected a cross-shaped marker, and I deleted the legend. I scaled the vertical axis to start on 10/27/02, a Sunday; you can enter the date, "10/27/02", right into the Minimum box, and let Excel convert it to its integer value. I scaled the axis to have major ticks and tick labels every Sunday (i.e., Major Unit = 7) and minor ticks every day (Minor Unit = 1).

Double click on the closing price series in the chart. Go to the X Error Bars tab, click in the Custom Positive (+) box, and with the mouse select cells E2:E15 (the "Up" data). Click in the Custom Negative (-) box, and select cells F2:F15 (the "Down" data). Click the Okay button. Double click on the error bars, and on the Patterns tab, select the marker style without the cross on the end.

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Custom Solutions | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2010. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile