Reading Time: minutes remaining

4 Excel Stock Charts to Track Stock Price and Volume

Jason Morrell

by Jason Morrell 
February 27, 2023

Stock charts allow you to plot the price of a stock over time, such as daily, weekly, monthly, quarterly, 6-monthly, yearly or longer such as 5 years or 10 years.

On each stock chart in Excel, the variance in stock price is shown using candlesticks.

There are four types of stock chart in Excel, each of which are explained in this post:

  • The High-Low-Close Stock Chart
  • The Open-High-Low-Close Stock Chart
  • The Volume-High-Low-Close Stock Chart
  • The Volume-Open-High-Low-Close Stock Chart

1. High-Low-Close Stock Chart

The High-Low-Close stock chart plots the highest, lowest and closing price over time.

  • The top of the candlestick shows the highest price reached during the day (or whatevr time period you are plotting).
  • The bottom of the candlestick shows the lowest price reached during the time period.
  • The horizontal marker indicates the closing price.
The High-Low-Close stock chart

2. Open-High-Low-Close Stock Chart

The Open-High-Low-Close stock chart plots the highest and lowest price as well as the opening and closing price.

  • The candlestick shows the highest and lowest price.
  • A black block means that the stock price fell. These are called Down Bars.
  • A white block means that the stock price rose. These are called Up Bars.
The Open-High-Low-Close stock chart

3. Volume-High-Low-Close Stock Chart

The Volume-High-Low-Close stock chart plots the highest, lowest, opening and closing price and the volume of shares traded over time.

  • The candlestick shows the highest and lowest price.
  • The black marker indicates the closing price.
  • The shaded column shows the volume traded.
  • The left vertical axis shows the trading volume.
  • The right vertical axis shows the stock price.
The Volume-High-Low-Close stock chart

4. Volume-Open-High-Low-Close Stock Chart

The Volume-Open-High-Low-Close stock chart plots the highest, lowest, opening and closing price and the volume of shares traded.

  • The candlestick shows the highest and lowest price.
  • The black marker indicates the closing price.
  • The black and white block markers show the opening and closing price.

    A black block means that the stock price fell. These are called Down Bars.
    white block means that the stock price rose. These are called Up Bars.
  • The shaded column shows the trading volume.
  • The left vertical axis shows the trading volume.
  • The right vertical axis shows the stock price.
The Volume-Open-High-Low-Close stock chart

5. Example data

Consider this data.

Example data to build any kind of stock chart in Excel
  • The first column may contain dates or stock names.
  • The Volume is the number of trades made (bought or sold).
  • The openhighlow and close figures are for the stock price.

6. How to create a stock chart in Excel

To create a stock chart in Excel:

1.  Select the specific data ranges you need from your data, including the headings.

In the example data above:     

  • For a High-Low-Close stock chart,
    select the ranges A3:A8 and D3:F8.
  • For an Open-High-Low-Close stock chart,
    select the ranges A3:A8 and C3:F8.
  • For a Volume High-Low-Close stock chart,
    select the ranges A3:B8 and D3:F8
  • For a Volume-Open-High-Low-Close stock chart,
    select the range A3:F8:

2.  Click the Insert tab then the Waterfall, Funnel, Stock, Surface or Radar chart icon in the Charts group, or …

Click the Recommended Charts icon | All Charts tab | Stock

3.  Select the type of stock chart you wish to use.

The Stock chart type is found on the Waterfall, Funnel, Stock, Surface or Radar chart icon in the Charts group or on the All Charts tab of Recommended Charts

7. Changing the stock chart style

It is always worth checking out the Chart Styles on the Chart Tools Design ribbon for different configurations.

The Chart Styles gallery has lots of professional finishes for your stock chart. Choose one then customise

8. Reduce the data clutter

To change the amount of data shown on the stock chart, either:

1.  Click the Filter icon in the chart icon controls on the top-right edge of the chart and untick items you wish to exclude, or ...

2.  Click the Select Data icon on the Design tab under Chart Tools and select a new range of data that you wish to work with.

9. Customise the candlesticks

To customise the candlesticks:

1.  Right-click on any candlestick and choose Format High-Low lines to display the side panel.

2.  In the side panel, change the candlestick colour (including gradients etc.), thickness, dash type and other characteristics in the side bar.

10. Customise the markers

To customise the High, Low, Open or Close markers

1.  Right-click on any candlestick and choose Format High-Low lines to display the side panel.

2.  In the side panel, click the drop-down arrow on the High-Low Line Options and switch to Series “High”, Series “Low”, Series “Open” or Series “Close”.

Format the Data Series for the chart to set the high-low line options

3.  Switch to the Marker option.

4.  Under Marker Options, change the Built In Type (square, triangle, dash etc) and Size.

5.  Under Fill and Border, set the outline and fill colour options.

Customise the marker options for the stock chart

11. Customise the up bars and down bars

To customise the up bars or down bars
(High-Low-Close or Volume-High-Low-Close charts only)

1.  Right-click on any candlestick and choose Format High-Low lines to display the side panel.

2.  In the side panel. click the Options drop-down arrow and switch to Up-Bars 1 or Down-Bars 1.

3.  Set the Border and Fill options as appropriate

Customise the up bars and down bars for the stock chart

12. Add a closing price trend line

To show a Closing price trend line:

1.  Double-click any closing price marker to display the appropriate settings in the side bar.

2.  Under Series Options, choose Fill and Line.

3.  Under Line Settings choose anything other than No Line and set the line settings as necessary.

13. Wrap

I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.

Have a fantastic day.

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.


He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.


Purely for amusement he sometimes talks about himself in the third person.

SHARE

more from

Office Mastery logo
Read More
How to Consolidate Data in Excel: 2 Easy Ways to use Excel’s Consolidation Tool
Read More
Excel Screen: A Beginners Tour

JOIN THE DISCUSSION

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>