A stock chart, also known as a candlestick chart, is a visual representation of a multitude of data on a timeline. This visualization type is typically almost exclusively used for visualizing stock market prices. The data series appear like candles with a rectangular body and a vertical thin line called shadows. While the body represents the area between the open and close prices, the shadows indicate the high and low ranges on specific timeframes. The volume data is displayed as columns in a stock chart.

 

Stock Chart Basics

Sections

A stock chart mainly consists of 6 sections.

• Plot Area: This is where the visual representation takes place.
• Chart Title: The title of the chart. Giving your chart a descriptive name will help your users easily understand the visualization.
• Legend: The legend is an indicator that helps distinguish between the data series.
• Horizontal axis: The axis that contains the categories of the data, also known as the x-axis. This axis usually shows the date values. Alternatively, you can set it to show different stock data for specific dates.
• Primary Vertical axis: The axis that represents the price or volume values based on the selected chart type, also known as the primary y-axis.
• Secondary Vertical axis: The axis that represents price or volume values based on the selected chart type. If volume values are omitted, this axis will not be displayed. It is also known as the secondary y-axis.

Types

There are 4 types of commonly used surface charts.

• High-Low-Close: The default version of the stock charts in Excel. This version displays the close price of a stock, as well as its highest and lowest points in a given timeframe. Since it doesn’t show the open price value, this type doesn't have a body.
• Open-High-Low-Close: This is the most common version of stock charts. In addition to High-Low-Close, this type also shows the open The filled area between the open and close values represents the body.
• Volume-High-Low-Close: The volume is drawn as in a traditional column chart. Thus, this type is essentially a combination of the volume column and High-Low-Close
• Volume-Open-High-Low-Close: A chart type that can contain all possible values.

 

Inserting a Stock Chart in Excel

Begin by selecting your data in Excel. If you include data labels in your selection, Excel will automatically assign them to each column and generate the chart.

Go to the INSERT tab in the Ribbon and click on the Combo Chart icon to see the pie chart types, then select Radar, Surface and Stock Chart icon to see the stock chart types. Click on the desired chart to insert the section Stock. In our example, we’re going to be using Volume-Open-High-Low-Close.

Clicking the icon inserts the default version of the chart. Continue to read for customization options.

 

Customizing a Stock Chart in Excel

You can customize pretty much every chart element and there are a few ways you can do this. Let’s look at each method.

Double-Clicking

Double-clicking on any item in the chart area pops up the side panel where you can find options for the selected element. Please keep in mind that you don’t need to double click another element to edit it once the side panel is open, the side menu will switch to the element. The side panel contains element specific options, as well as other generic options like coloring and effects.

Right-Click (Context) Menu

Right-clicking an element displays context menu with bunch op items as it happens in any application as well. You can modify basic styling properties like colors or delete item as well as activating side panel for more options. To display the side panel choose the options which starts with Format string. For example; Format Chart Area… in the following image.

Chart Shortcut (Plus Button)

In Excel 2013 and newer versions, charts also support shortcuts. You can add/remove elements, apply predefined styles and color sets and filter values very quickly.

With shortcuts, you can also see the effects of options on the fly before applying them. In the following image, the mouse is on the Data Labels item and the labels are visible on the chart.

Ribbon (Chart Tools)

Whenever you activate a special object, Excel adds a new tab(s) to the Ribbon. You can see these chart specific tabs under CHART TOOLS. There are 2 tabs - DESIGN and FORMAT. While the DESIGN tab contains options to add elements, apply styles, modify data and modify the chart itself, the FORMAT tab provides more generic options that are common with other objects.

 

Customization Tips

Preset Layouts and Styles

Preset layouts are always a good place to start for detailing your chart. You can find styling options from the DESIGN tab under CHART TOOLS or by using the brush icon on Chart Shortcuts. Below are some examples.

Applying a Quick Layout:

Changing colors:

Update Chart Style:

Changing the chart type

You can change the type of your chart any time from the Change Chart Type dialog. Select one of the datasets (series) on the chart, and click on Change Chart Type in the Right-Click (Context) Menu, or from the DESIGN tab. Alternatively, you can change the chart types for all datasets by right-clicking on an empty chart area.

In the Change Chart Type dialog, you can see the options for all chart types with their previews. However, unlike other chart types, Excel doesn't allow selecting another stock chart type if your dataset is not suitable. For example, Volume-Open-High-Low-Close type uses 5 columns to generate the chart. However, other types use 3 or 4 columns. If you select one of these types, you will only see a description about the active chart type.

Switch Row/Column

By default, Excel assumes that vertical labels of your data are the categories, and the horizontal ones are the data series. If your data is reversed, click Switch Row/Column button in the DESIGN tab, when your chart is selected. However, this transformation typically won’t make any sense in stock charts since they rely on a specific type and column count relationship.

Move a chart to another worksheet

By default, charts are created inside the same worksheet as the selected data. If you need to move your chart into another worksheet, use the Move Chart dialog. Begin by clicking the Move Chart icon under the DESIGN tab or from the right-click menu of the chart itself. Please keep in mind you need to right-click in an empty place in chart area to see this option.

In the Move Chart menu, you have 2 options:

• New sheet: Select this option and enter a name to create a new sheet under the specified name and move your chart there.
• Object in: Select this option and select the name of an existing sheet from the dropdown input to move your chart to that sheet.