Stock Chart in Excel (4 Different Cases)

Creating a stock chart in business is often essential for visualizing price trends and patterns over time that help in investment decision-making and market analysis. And Excel is such a tool that helps create a stock chart just within a moment.

In this article, we have illustrated the step-by-step procedure of creating a stock chart in Excel. We have demonstrated all types of available stock charts that can be created in Excel. We have simplified the basics of stock charts in a way so that one can easily understand a stock chart just by looking at it after going through this article. Also, you will get to know how one can create a box and whisker plot and a surface chart in Excel.

Download Practice Workbook

You can download the workbook used to elaborate methods in this article from here.

Stock Chart in Excel.xlsx

What is a Stock Chart?

A stock chart illustrates the historical price development of a particular stock or resource over a specific period of time. This type of chart shows the trend of a stock’s performance over time. Investors, traders, and analysts utilize it as an essential tool to monitor the performance of a stock and make wise decisions.

What are Open, High, Low, and Close Prices in a Stock Chart?

The terms open, high, low, and close prices in a stock chart are data points that represent the price action of a stock over a specific period of time.

The opening price is the price at which a stock’s trading starts at the beginning of a particular time period, like a trading day.

The high price indicates the highest price a stock may be purchased for within the given time frame, which could be a trading day, an hour, or any other period of time.

The low price represents the stock’s lowest price within the given time period.

And, the closing price is the final price at which a stock’s trading ends at the end of the specified time period.

Let’s have a look at the stock chart below. The highest point represents the high price and the lowest point represents the low price. The difference between open and close prices is shown in a bar plot. But remember, the opening price can be lower than the closing price.

Create a Stock Chart in Excel: 4 Different Cases

You can create 4 types of stock charts in Excel. We will be using the following dataset as an example to demonstrate those all. The dataset represents Amazon.com’s volume, open, high, low, and close prices for a specific period of time.

1. High-Low-Close Stock Chart

This type of stock chart displays the fluctuations of high, low, and close prices of a stock over a specific period of time.

First, from the following dataset, select the range B5:B21 and range E5:G21 holding the Ctrl key.
Follow these steps: Insert tab >> Insert Waterfall, Funnel, Stock, Surface, or Radar Chart >> High-Low-Close.

The following chart will appear on your worksheet.
But it’s really hard to read the following chart isn’t it?

So, let’s improve its transparency so that one can easily understand it.
First of all, add a title to your chart.
Simply select the Chart Title and type down your desired text for your charts’ title. We have set the title of our chart as Amazon High-Low-Close Stock Chart.

Now, double-click on your chart.
A separate window titled Format Chart Area will appear on your worksheet.
Click on Chart Options.
Select Series “Close”.

From the sidebar, click on Fill & Line.
Select Marker.
Click on Marker Options.
Check Built-in.
Left-click on the Type drop-down menu.
Select Cross symbol as shown below.

Increase the size. We have increased it to 7.
From the Border section, select an appropriate color. We have selected orange color.

Finally, look at the stock chart now. All the cross points within the chart represent the Closing prices.
The highest point represents the high price and the lowest point represents the low price.

2. Open-High-Low-Close Stock Chart

This type of stock chart illustrates the fluctuations of opening, high, low and close prices of a stock over a specific period of time.

Select range B5:B21 and range D5:G21 together holding the Ctrl key.
Go through these steps: Insert tab >> Insert Waterfall, Funnel, Stock, Surface, or Radar Chart >> Open-High-Low-Close.

The following stock chart will be created.

To improve its clarity, double-click on the chart first then the Format Chart Area sidebar will appear on your screen.
Select Series “Open” from the Chart Options.

Follow these steps: Fill & line >> Marker >> Marker Options >> Built-in >> Circled Symbol.
Select any color you want.

At this moment, click on Series Options as follows then select Series “Close”.

Go to the Fill & Line option.
Click on Marker.
Select Marker Options.
Mark Built-in.
Select the cross symbol from the Type drop-down.
Select a convenient color.

Finally, the chart will appear as follows. Isn’t it easier now to analyze the chart than before?

3. Volume-High-Low-Close Stock Chart

When you have four series of values that are volume, high, low and close prices, you should create this type of stock chart in Excel.

Select ranges B5:C21 and E5:G21 from the following dataset.
Follow these steps: Insert tab >> Insert Waterfall, Funnel, Stock, Surface, or Radar Chart >> Volume-High-Low-Close.

The following chart will appear on your sheet.
The clustered column bars represent volume of stock.

From the Format Chart Area sidebar, select Chart Options.
Select Series “Close”.

Follow these steps: Fill & Line >> Marker >> Marker Options >> Built-in >> Cross Symbol.

After that, set the size as 8 or whatever you want.
Select any color.

Lastly, the chart is as follows.

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

When you have five series of values that are volume, open, high, low, and close prices, you should create such type of stock chart.

Select range B5:G21.
Follow these steps: Insert tab >> Insert Waterfall, Funnel, Stock, Surface, or Radar Chart >> Volume-Open-High-Low-Close.

The following stock chart will appear on your screen.

Double-click on the chart.
Then from the Format Chart Area, click on Chart Options.
Select Series “Open”.

Go through these steps: Fill & Line >> Marker >> Marker Options >> Built-in >> Circled symbol.
Select any color.

Click on Series Options drop-down menu.
Select Series “Close”.

Follow these steps: Fill & Line >> Marker >> Marker Options >> Built-in >> Cross symbol.
Choose a convenient color.

The chart is now as follows.

You can change the representation of Volume as well.
Let’s assume, you want the Volume as line type instead of clustered column bar.
Right-click on any Volume column.
Click on Change Series Chart Type.

Click on the Volume drop-down menu.
Select Line.
Click on OK.

And the stock chart is now as follows. Some people may find columns easier to read, while others may prefer it in a line format. Choose the one that suits you best.

You can even add trendline to your chart for better data analysis.
Let’s assume, you want to add a trendline for closing prices.
Select any close price point from the chart. Here in the chart, all the cross points represent closing prices.
Click on the plus (+) icon at the top-rightmost of your chart.
Click on the arrow sign beside Trendline.
Select More Options.

Mark Moving Average.

And trendline is added as follows.

Create a Box and Whisker Plot in Excel

You can analyze the trend of a stock’s performance over time using Box and Whisker Plot also. Suppose, we have a dataset as follows and now we want to create a Box and Whisker Plot.

Select the range B5:E21.
Go to the Insert tab.
Click on the Insert Statistic Chart menu.
Select Box and Whisker.

The graph will appear on your sheet.
For better understanding, we will add legend to our chart.
Click on plus (+) icon.
Click on the arrow symbol beside Legend.
Select Bottom.

And now, Legend is added to the bottom of your chart as follows.

Create a Surface Chart in Excel

Suppose, we have a dataset as follows where some products sales data in some major cities are mentioned. We want to create a surface chart out of it.

Select range B4:E9.
Go to the Insert tab.
Click on Insert Waterfall, Funnel, Stock, Surface, or Radar Chart menu.
Select 3-D Surface.

And the surface chart is created as follows.

Things to Remember While Creating a Stock Chart in Excel

While creating any type of stock chart in Excel, you have to follow the sequence of values that is- Volume > Open > High > Low > Close or Volume > High > Low > Close or Open > High > Low > Close or High > Low > Close.

Frequently Asked Questions

1. Can I create a dynamic stock chart that updates automatically with new data?

Yes, you can create a dynamic stock chart that updates automatically with new data. You may use named ranges and tables in Excel to create a dynamic stock chart. The chart will automatically update as new data rows are added to the table if you create a named range for your data and turn it into a table.

2. What is the best chart type for displaying stock price trends?

The type of chart you choose will depend on your preferences and the type of your data. However, OHLC (Open-High-Low-Close) is usually the most used type for creating a stock chart.

3. Can I customize the appearance of my stock chart in Excel?

Yes, you can change how your chart looks. Excel provides tools for customizing the colors, fonts, gridlines, and other visual components.

Conclusion

This article demonstrates all the basics of creating a stock chart in Excel. Along with that, one can also easily create a box and whisker plot in Excel. Also we have illustrated the simple method of creating a surface chart in this article. Hope, you have found whatever you were looking for. Visit our site ExcelDemy for more relevant articles.

<< Go Back To Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!