Excel Volume-High-Low-Close Stock Market Chart

01
of 09

Excel Stock Market Chart Overview

Excel Volume-High-Low-Close Stock Market Chart Tutorial
Excel Volume-High-Low-Close Stock Market Chart Tutorial. © Ted French

A Volume-High-Low-Close stock market chart is a type of bar chart or graph used primarily to show changes in the value of tradable assets - such as stocks - over a given period of time.

The component parts of the chart and their function are:

  • The thin vertical lines on the chart shows the range of value for the stock for the time period with the top of the line indicating the highest share price and the bottom of the line indicating the lowest share price
  • The close marker - the yellow triangle. The upper point of the triangle indicates the closing price for the stock
  • The blue vertical bars show the volume or number of shares traded for the time period

Excel Stock Market Chart Tutorial

This tutorial walks you through creating a Volume-High-Low-Close stock market chart in Excel.

The tutorial first creates a basic stock chart and then uses formatting options listed under Chart Tools on the ribbon to produce the chart seen in the image above.

Tutorial Topics

  1. Entering and Selecting the Chart Data
  2. Creating a Basic Volume-High-Low-Close Chart
  3. Using the Chart Tools to add Chart and Axes Titles
  4. Formatting Chart Labels and Values
  5. Formatting the Close Marker
  6. Changing the Chart Area Background Color
  7. Changing the Plot Area Background Color
  8. Adding the 3-D Bevel Effect and Re-sizing the Chart
02
of 09

Entering and Selecting the Chart Data

Entering and Selecting the Stock Market Chart Data
Entering and Selecting the Stock Market Chart Data. © Ted French

Entering the Chart Data

The first step in creating a Volume-High-Low-Close chart is to enter the data into the worksheet.

When entering the data, keep these rules in mind:

  • Whenever possible, enter your data in columns as shown in the image above
  • Lay out your worksheet with the title for each data series at the top of the column and the data in the cells below
  • Don't leave blank rows or columns when entering your data as this will create blank areas in the chart

Note: The tutorial does not include the steps for formatting the worksheet as shown in the image above. Information on worksheet formatting options is available in this Basic Excel Formatting Tutorial.

Selecting the Chart Data

Once the data has been entered, the next step is to select the data to be charted.

In an actual worksheet, only a portion of the data would normally be included in a chart. Selecting or highlighting the data, therefore, tells Excel what information to include and what ignore.

In addition to the number data, be sure to include all column and row titles that describe your data.

Tutorial Steps:

  1. Enter the data as seen in the image above into cells A1 to E6.
  2. Drag select cells A2 to E6 to highlight them
03
of 09

Creating a Basic Volume-High-Low-Close Chart

A Basic Volume-High-Low-Close Stock Market Chart
A Basic Volume-High-Low-Close Stock Market Chart. © Ted French

All charts are found under the Insert tab of the ribbon in Excel.

Placing your mouse pointer over a chart category will bring up a description of the chart.

Clicking on a category opens a drop down showing all of the chart types available in that category.

When creating any chart in Excel, the program first creates what is called a basic chart using the data selected.

After that, it is up to you to format the chart using the available Chart Tools.

Tutorial Steps:

  1. If you are using Excel 2007 or Excel 2010, click on Insert > Other Charts > Stock > Volume-High-Low-Close in the ribbon
  2. If you are using Excel 2013, click on Insert > Insert Stock, Surface or Radar Charts > Stock > Volume-High-Low-Close in the ribbon
  3. A basic Volume-High-Low-Close stock market chart, similar to the one seen in the image above, should be created and placed in your worksheet.

The remaining steps in the tutorial cover formatting this chart to match the image shown on​ Page 1.

04
of 09

Using the Chart Tools

Formatting the Stock Market Chart Using the Chart Tools
Formatting the Stock Market Chart Using the Chart Tools. © Ted French

Chart Tools Overview

When it comes to formatting charts in Excel, it is important to remember that you do not have to accept the default formatting for any part of a chart. All parts or elements of a chart can be changed.

The formatting options for charts are mostly located on three tabs of the ribbon that are collectively called the Chart Tools

Normally, these three tabs are not visible. To access them, simply click on the basic chart you just created and three tabs - Design, Layout, and Format - are added to the ribbon.

Above these three tabs, you will see the heading Chart Tools.

In the tutorial steps below we will add and rename the axes titles and chart title as well as move the chart's legend using options located under the chart tool's Layout tab.

Adding the Horizontal Axis Title

The horizontal axis shows the dates along the bottom of the chart.

  1. Click on the basic chart in the worksheet to bring up the chart tool tabs
  2. Click on the Layout tab
  3. Click on Axis Titles to open the drop down list
  4. Click on the Primary Horizontal Axis Title > Title Below Axis option to add the default title Axis Title to the chart
  5. Drag select the default title to highlight it
  6. Type in the title "Date"

Adding the Primary Vertical Axis Title

The primary vertical axis shows the volume of shares sold along the left side of the chart.

  1. Click on the chart if necessary
  2. Click on the Layout tab
  3. Click on Axis Titles to open the drop down list
  4. Click on the Primary Vertical Axis Title > Rotated Title option to add the default title Axis Title to the chart
  5. Drag select the default title to highlight it
  6. Type in the title "Volume"

Adding the Secondary Vertical Axis Title

The secondary vertical axis shows the range of stock prices sold along the right side of the chart.

  1. Click on the chart if necessary
  2. Click on the Layout tab
  3. Click on Axis Titles to open the drop down list
  4. Click on the Secondary Vertical Axis Title > Rotated Title option to add the default title Axis Title to the chart
  5. Drag select the default title to highlight it
  6. Type in the title "Stock Price"

Adding the Chart Title

  1. Click on the chart if necessary
  2. Click on the Layout tab of the ribbon
  3. Click on Chart Title > Above Chart option to add the default title Chart Title to the chart
  4. Drag select the default title to highlight it
  5. Type in the title below on two lines - use the Enter key on the keyboard to split the lines:
    The Cookie Shop
    Stock Volume and Price

 

Moving the Chart Legend

By default, the chart legend is located on the right-hand side of the chart. Once we add the secondary vertical axis title, things get a little crowded in that area. To alleviate the congestion we will move the legend to the top of the chart below the chart title.

  1. Click on the chart if necessary
  2. Click on the Layout tab of the ribbon
  3. Click on Legend to open the drop down list
  4. Click on Show Legend at Top option to move the legend to below the chart title
05
of 09

Formatting the Chart Labels and Values

Formatting the Stock Market Chart Labels and Values
Formatting the Stock Market Chart Labels and Values. © Ted French

Font Formatting Options

In the previous step, it was mentioned that most of the formatting options for charts were located under Chart Titles.

One group of formatting options that are not located here are the text formatting tools - such as font size and color, bold, italics, and alignment.

These can be found under the Home tab of the ribbon - font section.

Excel Right Click Menus and Toolbar

An easier way, though, of accessing these options is by right clicking on the element you want to format.

Doing so opens the right-click or context menu which includes a small formatting toolbar.

Since it is part of the context menu, the formatting options on the toolbar change depending on what you have clicked on.

For example, if you right-click on one of the blue volume bars in the chart you the toolbar contains only formatting options that can be used with this chart element.

Right-clicking one of the tiles or legends gives you text formatting options the similar to those found under the Home tab of the ribbon.

Chart Formatting Shortcut

In this step of the tutorial, we want to change the color of all titles, the legend, and values - the numbers and dates in the axes scales - to a blue color similar to that of the volume bars.

Rather than doing each one separately though, we can save some time by changing the color of all labels and values in the chart at one time.

This shortcut involves selecting the entire chart by clicking on the white background rather than clicking on the individual elements,

Formatting All Labels and Values

  1. Right click on the white chart background to open the chart context menu
  2. Click on the small down arrow to the right of the Font Color icon in the context toolbar to open the Theme Colors panel
  3. Click on Blue Accent 1, Darker 25% to change all labels and values in the chart to that color

Shrinking the Chart Title Font Size

The default font size for the chart title is 18 point which dwarfs the other text and also reduces the plot area of the chart. To remedy this situation we will drop the chart title font size to 12 points.

  1. Click on the Chart Title to select it - it should be surrounded by a box
  2. Drag select the title to highlight it
  3. Right-click on the highlighted title to open the context menu
  4. Click on the small down arrow to the right of the Font Size icon - the number 18 in the top row of the context toolbar - to open the drop down list of available font sizes
  5. Click on 12 in the list to change the chart title font to 12 point
  6. Click on the chart background to clear the highlight on the chart title
  7. The plot area of the chart should also increase in size
06
of 09

Formatting the Close Marker

Formatting the Stock Market Chart Close Marker
Formatting the Stock Market Chart Close Marker. © Ted French

The default close marker for the chart - which shows the closing stock price - is a small black horizontal line. In our chart, the marker is almost impossible to see - especially when it is located in the middle of the blue volume bars as is the case for the 6th, 7th, and 8th of February.

To remedy this situation, we will change the marker to a triangle where the top point represents the closing price of the stock for that day.

We will also change the size and the color of the triangle to yellow so that it stands out against the blue background of the volume bars.

Note: If we change an individual Close marker - say for February 6th - only the marker for that date will change - meaning we would have to repeat the same steps four times to change all of the markers.

To change the marker for all four dates at once we need to change the Close entry in the chart's legend.

Tutorial Steps

As in the previous step of the tutorial, we will use the context menu to complete this step.

Changing the Marker Color

  1. Click once on the legend to select it - it should be surrounded by a box
  2. Click once on the word Close in the legend to select it - just the word Close should be surrounded by the box
  3. Right click on the word Close to open the context menu
  4. Click on Format Data Series option in the context toolbar to open the dialog box
  5. Click on Marker Fill in the left-hand window of the Format Data Series dialog box
  6. Click on Solid Fill in the right-hand window of the dialog box
  7. Click on the down arrow to the right of the Color icon in the right-hand window to open the Colors panel
  8. Click on yellow under the Standard Colors to change the marker color to yellow
  9. Leave the dialog box open for the next step in the tutorial

Changing the Marker Type and Size

  1. Click on Marker Options in the left-hand window of the Format Data Series dialog box
  2. Click on Built-in under the Marker Type options in the right-hand window of the dialog box
  3. Click on the down arrow to the right of the Type icon in the right-hand window to open the drop down list
  4. Click on the triangle in the list to change the marker
  5. Under the Size, option increase the size of the triangle to 8
  6. Click on the Close button to close the dialog box and return to the worksheet.
07
of 09

Changing the Chart Area Background Color

Changing the Chart Area Background Color
Changing the Chart Area Background Color. © Ted French

To change the background color of the entire chart, we will again make use of the context menu. The color choice in the context menu is listed as an off-white color even though it appears to be more gray than white.

Tutorial Steps:

  1. Right click on the white chart background to open the chart context menu
  2. Click on the small down arrow to the right of the Shape Fill icon - the paint can - in the context toolbar to open the Theme Colors panel
  3. Click on White, Background 1, Darker 25% to change the chart background color to gray
08
of 09

Changing the Plot Area Background Color

Changing the Plot Area Background Color
Changing the Plot Area Background Color. © Ted French

The steps for changing the background color of the plot area are almost identical to those for changing the background color for the entire chart.

The color chosen for this chart element appears as light blue even though it is listed as dark blue in the color panel.

Note: Be careful not to select the horizontal grid lines running through the plot area rather than the background itself.

Tutorial Steps:

  1. Right click on the white plot area background to open the plot area context menu
  2. Click on the small down arrow to the right of the Shape Fill icon - the paint can - in the context toolbar to open the Theme Colors panel
  3. Click on Dark Blue, Text 2, Lighter 80% to change the plot area background color to light blue.
09
of 09

Adding the 3-D Bevel Effect and Re-sizing the Chart

Adding the 3-D Bevel Effect
Adding the 3-D Bevel Effect. © Ted French

Adding the 3-D Bevel Effect

Adding the 3-D bevel effect is truly a cosmetic touch that adds a bit of depth to the chart. It leaves the chart with an embossed-looking outside edge.

  1. Right click on the white chart background to open the chart context menu
  2. Click on Format Chart Area option in the context toolbar to open the dialog box
  3. Click on 3-D Format in the left-hand window of the Format Chart Area dialog box
  4. Click on the down arrow to the right of the Top icon in the right-hand window to open the panel of bevel options
  5. Click on Convex option in the panel to set a convex edge to the chart
  6. Click on the Close button to close the dialog box and return to the worksheet

Re-sizing the Chart

Re-sizing the chart is another optional step. The benefit of making the chart larger is that it reduces the crowded look created by the second vertical axis on the right-hand side of the chart.

It will also increase the size of the plot area making the chart data easier to read.

The easiest way to resize a chart is to use the sizing handles that become active around the outside edge of a chart once you click on it.

  1. Click once on the chart background to select the entire chart
  2. Selecting the chart adds a faint blue line to the outside edge of the chart
  3. In the corners of this blue outline are sizing handles
  4. Hover your mouse pointer over one of the corners until the pointer changes into a double-headed black arrow
  5. When the pointer is this double-headed arrow, click the left mouse button and pull outward slightly to enlarge the chart. The chart will re-size in both length and width. The plot area should increase in size as well.

If you have followed all of the steps in this tutorial at this point your Volume-High-Low-Close Stock Market Chart should resemble the example displayed in the image on page 1 of this tutorial.