Author: Oscar Cronquist Article last updated on August 06, 2017

This article demonstrates how to create a stock chart with two series.

two-stock-series-in-one-chart21

The picture above shows a weekly stock bar chart of Microsoft and Caterpillar. One bar shows you the highest price and the lowest price during that specific week, it also shows the closing price which is the price of the last deal done that week.

two-stock-series-in-one-chart1

Instructions

  1. Rearrange columns like this: high, low and close
    two-stock-series-in-one-chart2
  2. Select columns High, Low and close
  3. Go to tab "Insert" on the ribbon
  4. Click "Other Charts" and then "High-Low-Close"
    two-stock-series-in-one-chart3
  5. A chart is inserted on your active sheet
    two-stock-series-in-one-chart4
  6. Right click on chart and click on "Select Data..."
  7. Click Edit button below Horizontal (Category) Axis Labels
  8. Select your date range
    two-stock-series-in-one-chart5
  9. Click OK button twice
    two-stock-series-in-one-chart6
  10. Right click on chart again
  11. Click on "Select Data"
  12. Click on "Add" button below Legend Entries (Series)
  13. Select High column for the other stock you want plotted
    two-stock-series-in-one-chart7
    two-stock-series-in-one-chart8
  14. Click OK twice
  15. Select the chart
  16. Go to tab "Layout" on the ribbon
  17. Select series 4
    two-stock-series-in-one-chart9
  18. Click "Format Selection" below Series 4
  19. Change to "Secondary Axis"
    two-stock-series-in-one-chart10
  20. Click Close
  21. Right click on chart again and click on "Select Data..."
  22. Add Low and Close series
  23. Go to tab "Layout" on the ribbon
  24. Select "Series 4"
  25. Click on "Lines" button and then "High-Low" Lines
    two-stock-series-in-one-chart11
    two-stock-series-in-one-chart12
  26. Select "Series 6" (Close)
  27. Click "Format Selection"
  28. Go to "Marker Options", choose built-in marker type and the sixth symbol from the top.
    two-stock-series-in-one-chart13
  29. Select size 3
  30. Go to "Marker Line Color", select "Solid Line" and finally pick a color. I chose dark blue.
    two-stock-series-in-one-chart14
    two-stock-series-in-one-chart15
  31. Select "High Low Lines 2"
    two-stock-series-in-one-chart16
  32. Select "Solid Line" and select a color
    two-stock-series-in-one-chart17
  33. Click Close
    two-stock-series-in-one-chart18

You now know how to change the color of high low lines and the closing line. Change the line color of all series (High, Low and Close) on the primary axis.

two-stock-series-in-one-chart19

I chose black. Now delete the secondary axis and remove entries on the legend except "Close" and "Series 6". Change the name of series "Close" to Microsoft and "Series 6" to Caterpillar.

two-stock-series-in-one-chart20

Download excel *.xlsx file

build-a-stock-chart-with-two-series.xlsx

Related articles