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. Press with left mouse button on "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. Press with right mouse button on on chart and press with left mouse button on "Select Data..."
  7. Press with left mouse button on Edit button below Horizontal (Category) Axis Labels
  8. Select your date range
    two-stock-series-in-one-chart5
  9. Press with left mouse button on OK button twice
    two-stock-series-in-one-chart6
  10. Press with right mouse button on on chart again
  11. Press with mouse on "Select Data"
  12. Press with mouse 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. Press with left mouse button on 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. Press with left mouse button on "Format Selection" below Series 4
  19. Change to "Secondary Axis"
    two-stock-series-in-one-chart10
  20. Press with left mouse button on Close
  21. Press with right mouse button on on chart again and press with left mouse button on "Select Data..."
  22. Add Low and Close series
  23. Go to tab "Layout" on the ribbon
  24. Select "Series 4"
  25. Press with mouse 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. Press with left mouse button on "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. Press with left mouse button on 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

Get excel *.xlsx file

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

Related articles

test
Improve worksheet readability
Making your sheets easy to read is a fundamental approach of creating useful worksheets. Your message must be crystal clear, […]