How to Create a Stock Chart in Excel

Tracking stock performance doesn’t have to mean staring at a complex trading platform. With a bit of data and a few clicks, you can build your own customized stock charts directly in Microsoft Excel. This article will show you exactly how to get your data, format it correctly, and create clear, professional-looking stock charts to analyze market trends.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Gathering Your Stock Data

Before you can make a chart, you need accurate data. For a standard stock chart, you’ll typically need four key data points for each day you want to analyze, often referred to as OHLC:

• Open: The price of the stock when the market opened.
• High: The highest price the stock reached during the day.
• Low: The lowest price the stock reached during the day.
• Close: The final price of the stock when the market closed.

You may also want to include the Volume, which is the total number of shares traded during the day.

So where do you find this information? Fortunately, several major financial sites provide free historical data for public download. Yahoo Finance is one of the most popular and user-friendly options.

How to Download Stock Data from Yahoo Finance:

1. Navigate to https://finance.yahoo.com.
2. Use the search bar to find the stock you’re interested in (e.g., Apple or AAPL).
3. On the stock's summary page, click the "Historical Data" tab.
4. Select your desired "Time Period". You can choose a preset range like one year or set a custom start and end date.
5. Ensure "Show" is set to "Historical Prices" and "Frequency" is set to "Daily".
6. Click "Apply". A table of data will appear.
7. Click the "Download" link. This will save a CSV (Comma-Separated Values) file to your computer, which you can open directly in Excel.

Once you open the file, you’ll have a clean table with columns for Date, Open, High, Low, Close, Adjusted Close, and Volume.

Excel’s STOCKHISTORY Function: The Easiest Way to Get Data

Instead of manually downloading CSV files, Microsoft 365 subscribers can pull historical stock data directly into a sheet using the STOCKHISTORY function. This saves a ton of time and ensures your data is always up to date without leaving Excel.

The function’s basic structure is:

=STOCKHISTORY(stock, start_date, [end_date], [interval])

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Using STOCKHISTORY Step-by-Step:

Let's say you want to pull the daily closing prices for Microsoft (MSFT) for the past 90 days.

1. Set up your parameters. In separate cells, enter your start date and end date. A simple way to get a date 90 days ago is with the formula =TODAY()-90. For the end date, you can use =TODAY(). In another cell, enter the ticker symbol "MSFT". This makes your formula easy to update later.
2. Write the formula. Click an empty cell where you want the data to appear. Let's assume your ticker is in cell A1, start date in B1, and end date in C1. You would type:

=STOCKHISTORY(A1, B1, C1, 0, 1)

1. Breakdown of the formula:

When you press Enter, Excel will automatically fill the cells below with two columns: Date and Close. If you need the full spectrum of data (Open, High, Low, Close, Volume), you'll use a slightly more advanced version of the function like this:

=STOCKHISTORY(A1, B1, C1, 0, 1, 1, 2, 3, 4, 5)

The numbers at the end (1 through 5) tell Excel to pull the Open, High, Low, Close, and Volume columns, respectively, in addition to the Date. Double-check your numbers to make sure you get the data you need for your chart.

How to Create the Stock Chart: A Step-by-Step Guide

Once you have your data organized in columns, creating the chart takes just a few clicks. The crucial part is making sure your columns are arranged in the exact order Excel expects. Otherwise, the chart won’t generate correctly.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Organize Your Data Correctly

Before selecting any data, verify your column order. Depending on the chart you want to create, you’ll need one of these specific sequences from left to right:

• For a High-Low-Close chart: Your columns must be in the order Date, High, Low, Close.
• For an Open-High-Low-Close chart: Your columns must be in the order Date, Open, High, Low, Close. This type is also known as a candlestick chart.
• For a Volume-High-Low-Close chart: The required order is Date, Volume, High, Low, Close.
• For a Volume-Open-High-Low-Close chart: The required order is Date, Volume, Open, High, Low, Close.

This is the most common point of failure. If your chart looks weird, double-check that your columns are in one of these exact sequences.

Step 2: Selecting Your Data and Inserting the Chart

Let's create the most common type, the Open-High-Low-Close (OHLC) chart, also known as a Candlestick Chart.

1. Make sure your columns are organized as: Date, Open, High, Low, Close.
2. Click and drag to select the entire data range, including the column headers.
3. Navigate to the Insert tab on the Excel ribbon.
4. In the Charts group, click the icon for "Insert Waterfall, Funnel, Stock, Surface, or Radar Chart" (it looks like a set of funnels and mountains).
5. A dropdown menu will appear. Under the Stock section, select the second option, "Open-High-Low-Close."

Excel will instantly generate a candlestick chart on your worksheet. Each "candlestick" gives you a visual summary of the day's trading:

• The Box (or "Body"): This represents the range between the open and close price.
• The Line (or "Wick"/"Shadow"): The thin vertical line shows the full range for the day, from the low to the high.
• Color: By default, Excel uses two colors. A white (or green) box means the closing price was higher than the opening price (a positive day). A black (or red) box means the stock closed lower than it opened (a negative day).

Creating a Chart That Includes Volume

If you want to analyze trading volume alongside price, the process is very similar - just mind the column order.

1. Arrange your columns in this precise sequence: Date, Volume, Open, High, Low, Close.
2. Select all your data, including headers.
3. Follow the same path as before: Insert > Waterfall, Funnel, Stock... > Stock.
4. This time, choose the last option: Volume-Open-High-Low-Close.

Excel will create a chart with the candlesticks on top and a secondary chart with blue bars at the bottom, representing the daily trading volume. This allows you to quickly see if a big price move happened on high or low volume.

Customizing and Improving Your Stock Chart

A plain chart gives you the data, but good charting practice makes it easier to understand. Here's how to make a few-minute adjustments for maximum clarity.

Add a Clear Title and Labels

Always title your chart something descriptive like "Microsoft (MSFT) Stock Performance - Q4 2024". You can add the title by clicking the "Chart Title" text that appears when you first click the chart, or from the Chart Design > Add Chart Element menu.

It's also a good idea to label your Y-axis (vertical) as "Price" to make the chart easier to read.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Changing Up/Down Colors

You may prefer the traditional red for down days and green for up days instead of Excel's default black and white.

1. Double-click on any of the candlestick boxes in your chart. This will open the "Format Data Series" pane on the right.
2. Excel automatically creates rules for "Up Bars" and "Down Bars". To change these colors:

Adding a Moving Average

A moving average, like a simple moving average (SMA), can help smooth out price data to see trends more clearly. Here's a step-by-step guide:

1. Right-click on any candlestick in your chart and select "Add Trendline."
2. In the Trendline Options pane, select Moving Average.
3. Set the period. For example, you can enter 5 for a 5-day moving average (5 SMA) or 20 for a 20-day SMA.

The new line will appear on your chart, providing a smoothed view of the price data. You can experiment with different periods to see short-term and medium-term trends.

Final Thoughts

Excel is a powerful tool for tracking stock performance by letting you create customized stock charts. This can help identify market patterns and make informed decisions based on visual data clarity. Once you've mastered these basics, you can build your expertise and craft charts that are tailored to your specific needs, enhancing your ability to analyze and present financial information effectively.

Don't forget that consistency in formatting and clear labeling can significantly enhance the readability and professionalism of your charts. Practice these techniques regularly, and consider using advanced Excel features as you become more confident.