How to Enable Stock Data Type in Excel

Tired of manually copying and pasting stock prices into your spreadsheets? Excel’s built-in Stocks data type can transform your static financial sheets into dynamic dashboards that update automatically. This feature connects directly to live market data, letting you pull everything from current prices to historical highs and P/E ratios with just a few clicks. This guide will walk you through exactly how to enable and use this powerful tool.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What Exactly is the 'Stocks' Data Type in Excel?

The Stocks feature is one of Excel’s “linked data types.” Think of it as a smart cell that's connected to a massive online database of financial information. When you type a company name or ticker symbol and convert it to the Stocks data type, Excel recognizes it and flags it with a small stock exchange icon. That cell is no longer just simple text, it’s an interactive gateway to a wealth of data about that specific security.

Once enabled, you can pull dozens of different data fields related to that stock, including:

• Pricing Info: Price, Change, % Change, Bid, Ask, High, Low, Close
• Fundamental Data: P/E Ratio, Beta, Earnings per Share (EPS), Market Cap
• Historical Data: 52-week High, 52-week Low
• Company Info: Company Headquarters, CEO, Number of Employees, Industry

This data is provided by Refinitiv, a leading global provider of financial market data, ensuring you get reliable and up-to-date information. In essence, it automates the tedious task of data collection, saves you tremendous amounts of time, minimizes typos and errors, and lets you build responsive portfolio trackers and financial models directly within your spreadsheet.

First Things First: Can You Access this Feature?

Before you start digging through Excel's menus, it's important to know that the Stocks data type isn't available on all versions. If you can’t find the button, it’s almost certainly due to one of the following requirements.

Graphed

Still Building Reports Manually?

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

Watch the Demo

1. Excel Version Requirement

The Stocks data type is a premium feature available exclusively to Microsoft 365 (formerly Office 365) subscribers. It is not available in perpetual license versions of Excel like Excel 2016, 2019, or 2021.

To check your version:

1. Open Excel and click on File in the top-left corner.
2. Select Account from the left-hand menu.
3. Under "Product Information," you should see "Subscription Product" and "Microsoft 365." If you see "Microsoft Office Professional Plus 2019" or something similar, you will not have access to this feature.

2. Language Setting Requirement

For the feature to work, the English editing language must be installed in your Office language preferences. The Stocks button will disappear completely if English isn't included as an authoring language.

To check and add an editing language:

1. Go to File > Options > Language.
2. Look under "Office Authoring Languages and Proofing."
3. If English is not listed, click Add a Language..., select English, and then click Install.
4. After it’s installed, you may need to restart Excel for the changes to take effect.

3. An Active Internet Connection

Since the Stocks data type pulls live data from an online source, you must have an active internet connection for it to work. If you are offline, you can still view any data you’ve already pulled, but you won't be able to convert new tickers or refresh the existing data.

How to Enable and Use the Stocks Data Type: A Step-by-Step Guide

Once you've confirmed you meet the requirements, turning your boring list of tickers into dynamic data points is incredibly easy. Here’s how to do it.

Step 1: Create Your List

Start by entering a list of company names or their official stock ticker symbols into a single column on your spreadsheet. It’s a good practice to use tickers for accuracy, but full company names often work just as well.

For example, in column A, you might list:

• MSFT
• AAPL
• Amazon
• TSLA
• GOOG

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 2: Convert Your Text to the 'Stocks' Data Type

Now, you need to tell Excel that this isn't just a list of text - it's a list of financial securities.

1. Select the cells containing your company names or tickers.
2. Navigate to the Data tab on the Excel ribbon at the top of the screen.
3. In the "Data Types" group, click on the Stocks button. It has an icon that looks like a little bank building with columns.

Excel will then scan your list and attempt to match each entry with a known security from its database. If successful, you'll see the text align to the left and a small stock exchange building icon will appear next to each company name.

Step 3: Resolve Ambiguous Entries with the Data Selector

Sometimes, Excel isn’t 100% sure which stock you’re referring to. For instance, the company name might be a common word, or the ticker could be used on multiple exchanges. When this happens, a question mark icon will appear next to the cell.

When you click on the cell with the question mark, the Data Selector pane will open on the right side of your screen. This pane shows you a list of possible matches. Simply browse the options and click Select next to the correct security you want to track. Once you do, the question mark is replaced with the proper Stocks icon.

Extracting Stock Data after Enabling the Data Type

You've successfully converted your list into 'smart' cells. Now for the fun part: pulling in the actual data.

Method 1: The 'Add Column' Icon (Fast and Easy)

This is the quickest way to pull in standard data fields for your entire list.

1. Select one or more of your converted stock cells. A small icon with a plus sign labeled "Insert Data" will appear just to the top right of your selected cell(s).
2. Click on that icon. A dropdown list will appear with dozens of available data fields like "Price," "P/E," "Market Cap," etc.
3. Click on any field you're interested in (e.g., "Price").
4. Excel will instantly create a new column to the right and populate it with the current price for every stock in your list. You can repeat this process to add as many data columns as you need.

Method 2: Using Formulas (Powerful and Flexible)

For greater control and flexibility, you can use formulas to pull specific data points into any cell on your sheet. This is especially useful for building dashboards or custom calculations.

The formula syntax is very intuitive. You simply reference the cell containing the Stocks data type, add a period (.), and then specify the field name.

Let's say your "MSFT" stock data type is in cell A2. To get its price in another cell, you would type:

=A2.Price

You can even type =A2. and Excel's formula IntelliSense will pop up with a list of all available fields you can use, so you don't have to memorize them.

A few popular formula examples:

• To get the market capitalization: =A2."Market cap" (note the quotes for fields with spaces)
• To see the 52-week high: =A2."52 week high"
• To get the P/E ratio: =A2.P/E

Graphed

Still Building Reports Manually?

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

Watch the Demo

Practical Example: Build a Basic Portfolio Tracker

Let's tie this all together by creating a simple but useful stock portfolio tracking sheet. For best results, convert your data range into an Excel Table first (select your data and press Ctrl + T).

1. Column A (Ticker): Enter your tickers and convert them to the Stocks data type.
2. Column B (Shares Owned): Manually enter the number of shares you own for each stock. This is static input.
3. Column C (Current Price): In the first data row (let's say C2), enter the formula =[@Ticker].Price. Because you’re in an Excel Table, it will automatically fill this formula down the entire column.
4. Column D (Market Value): In cell D2, enter a simple calculation: =[@[Shares Owned]]*[@Current Price]. This multiplies your shares by the current price to give you the total value.
5. Column E (% Change): In cell E2, pull in the daily percentage change with the formula =[@Ticker]."% Change" and format the column as a percentage.

You now have a simple, live tracker showing your portfolio's value and daily performance, which updates whenever you refresh the data!

Common Issues and Troubleshooting Tips

• "I can't see the Stocks data type button on my ribbon!": This is almost always because you are not using a Microsoft 365 subscription. Refer to the prerequisites section. A secondary cause could be that you don't have English installed as an editing language.
• Data Looks Stale or Isn't Updating: The data doesn’t refresh in true real-time. To update it, go to the Data tab and click Refresh All. You can also right-click a Stocks data type cell and choose Data Type > Refresh.
• "#FIELD!" Error: This error means the data field you're requesting isn't available for that specific security. For example, trying to pull a P/E ratio for an index fund (which has no earnings) could produce this error.
• "#VALUE!" Error: You'll see this if your formula is referencing a cell that isn’t a valid Stocks data type. Check that the source cell has the stock exchange icon.

Final Thoughts

Mastering the Stocks data type in Excel elevates your spreadsheet from a simple calculator into a sophisticated financial analysis tool. It removes the grind of manual data entry, letting you focus on analyzing information and making better decisions. Once you start automatically pulling live price changes, market caps, and P/E ratios into your models, you’ll never go back to copying and pasting again.

While Excel is fantastic for this sort of hands-on data tracking, the challenge gets much bigger when you need to answer questions across all your sales and marketing platforms. We built Graphed because we know the pain of constantly logging into Google Analytics, Salesforce, Shopify, and a dozen other apps just to pull data into a spreadsheet. Graphed connects to all your tools, letting you build real-time dashboards and get instant insights by simply asking questions in plain English, transforming hours of repetitive work into a quick, simple conversation.