Excel’s Linked Data Types connect your spreadsheet to an online database. This feature lets you pull real-time information directly into your cells. You can get current stock prices, company details, and geographic data like population or area. This article explains how to convert your text into linked data and use the new fields that appear.
Key Takeaways: Using Excel’s Linked Data Types
- Data > Stocks or Geography: Converts a list of company names or locations into interactive data types connected to the cloud.
- Field Chooser (Ctrl+Shift+F5): Opens a pane to select specific data points like price or population to insert into adjacent cells.
- Refresh Data (Right-click > Data Type > Refresh): Updates all linked information in your workbook to get the latest values from the online source.
Understanding Excel’s Linked Data Types
Linked Data Types are a feature available in Microsoft 365 subscriptions. They turn standard text into rich, connected objects. When you type a company name like “Microsoft” and convert it, Excel links it to an online source. The cell then contains a live data object, not just text.
Two primary data types are Stocks and Geography. The Stocks type pulls financial data from a provider like Refinitiv. The Geography type gets information from sources like Microsoft Bing. Your workbook must have an internet connection to create and refresh these data types. The data is stored in the cloud, not within the Excel file itself.
Prerequisites for Using Linked Data Types
You need a Microsoft 365 subscription and the latest version of Excel for desktop. The feature is not available in older perpetual versions like Excel 2019. You must be signed in with a Microsoft account. An active internet connection is required to convert text and to refresh data. The feature may not be available in all regions due to data provider restrictions.
Steps to Convert Text to Stocks or Geography Data
The core process involves selecting your text and applying the data type. Excel will recognize and convert valid entries.
- Enter your data list
Type your list of items in a single column. For stocks, use company names or ticker symbols like “MSFT” or “Apple”. For geography, use country, state, or city names like “Japan” or “Tokyo”. - Select the cell range
Click and drag to highlight all the cells containing the text you want to convert. - Apply the data type
Go to the Data tab on the ribbon. In the Data Types group, click either the Stocks icon or the Geography icon. Excel will add a small icon to each cell it successfully converts. - Confirm the match
If Excel is unsure about a match, a question mark icon appears. Click the icon to see a card with suggestions. Select the correct entity from the list to finalize the link.
Extracting Specific Data Fields into Your Sheet
Once cells are converted, you can pull specific details into adjacent columns. This is done using the field chooser or a simple formula.
Method 1: Using the Field Chooser Pane
- Select a linked data cell
Click on a cell that has the Stocks or Geography icon. A small “Insert Data” button appears at its top-right corner. - Open the field list
Click the Insert Data button. A dropdown menu lists available fields like “Price”, “Change”, or “Population”. You can also press Ctrl+Shift+F5 to open the field chooser pane on the right side of the window. - Insert a field
Click any field name from the list. Excel will instantly add that data point into the cell immediately to the right of your selected cell. To add more fields, select the next empty column and choose another field.
Method 2: Using a Formula with the Dot Operator
- Start a formula in a new cell
Click in an empty cell next to your linked data cell. Type an equals sign (=) to begin a formula. - Reference the linked cell
Click on the cell containing the Stocks or Geography data type. The cell reference will appear in your formula. - Add the field name
Type a period (.) after the cell reference. A list of available fields will appear. You can select one from the list or type its name, such as .Price or .Population. Press Enter to complete the formula and display the data.
Common Mistakes and Data Limitations
Excel Shows a Question Mark Instead of a Data Icon
This means Excel could not confidently match your text to its online database. Click the question mark icon. A card appears with possible matches. Review the list and select the correct one. For stocks, using the official ticker symbol often gives a more precise match than the company name.
Data Fields Appear as #FIELD! or #VALUE! Errors
The #FIELD! error means the specific data point is not available for that entity. The #VALUE! error often occurs if the source cell is no longer a valid linked data type. Reapply the Stocks or Geography data type to the source cell. Then refresh the data by right-clicking the cell and selecting Data Type > Refresh.
Stock Prices or Geography Data Are Out of Date
Linked data does not update automatically in real time. You must manually refresh it. To update all linked data in your workbook, right-click any cell with a data type and select Data Type > Refresh. You can also go to Data > Refresh All. Note that stock prices have a delay, typically 15-20 minutes, depending on the data provider.
Stocks vs. Geography Data Types: Key Differences
| Item | Stocks Data Type | Geography Data Type |
|---|---|---|
| Primary Data Source | Refinitiv financial database | Microsoft Bing geographical database |
| Common Use Cases | Tracking share price, market cap, P/E ratio | Getting population, area, capital city, leader |
| Key Identifier | Company name or stock ticker symbol | Country, city, state, or province name |
| Refreshable Data | Yes, prices and metrics update | Yes, but core data like area changes less often |
| Common Error Cause | Ambiguous company name or private company | Misspelled location or unrecognized territory |
You can now pull live financial and demographic data directly into your Excel sheets. Start by converting a column of company names using the Data tab. Use the field chooser to add current price and other metrics to your analysis. For a more advanced workflow, try using the linked data in PivotTables to summarize information by country or sector.