Excel data types: Stocks and geography

Make a big impact with Announcements

You can get stock and geographic data in Excel. It's as easy as typing text into a cell, and converting it to the Stocks data type, or the Geography data type. These two data types are considered linked data types because they have a connection to an online data source. That connection allows you to bring back rich, interesting information that you can work with and refresh.

Looking for other kinds of linked data types? See How to use linked data types with Excel and What linked data types are available in Excel? for more info.

Note: The Stocks and Geography data types are only available to Worldwide Multi-Tenant clients (standard Microsoft 365 accounts).

Stocks

Column A contains company names and icons, Column B contains Price values, and Column C contains Change values

In the picture above, the cells with company names in column A contain the Stocks data type. You know this because they have this icon: Linked record icon for Stock . The Stocks data type is connected to an online source that contains more information. Columns B and C are extracting that information. Specifically, the values for price, and change in price are getting extracted from the Stocks data type in column A.

Geography

Column A contains icons and country names, Column B contains Population values, and Column C contains Gasonline Prices

In this example, column A contains cells that have the Geography data type. The Linked record icon for Geography icon indicates this. This data type is connected to an online source that contains more information. Columns B and C are extracting that information. Specifically, the values for population, and gasoline price are getting extracted from the Geography data type in column A.

  1. Type some text in cells. If you want stock information, type a ticker symbol, company name, or fund name into each cell. If you want geographic data, type a country, province, territory, or city name into each cell.

  2. Then select the cells.

  3. Although it's not required, we recommend creating an Excel table. Later on, this will make extracting online information easier. To create a table, go to Insert > Table.

  4. With the cells still selected, go to the Data tab, and then click either Stocks or Geography.

  5. If Excel finds a match between the text in the cells, and our online sources, it will convert your text to either the Stocks data type or Geography data type. You'll know they're converted if they have this icon for stocks: Linked record icon for Stock and this icon for geography: Linked record icon for Geography

  6. Select one or more cells with the data type, and the Insert Data button Add Column button will appear. Click that button, and then click a field name to extract more information. For example, for stocks you might pick Price and for Geography you might pick Population.

  7. Click the Insert Data button again to add more fields. If you're using a table, here's a tip: Type a field name in the header row. For example, type Change in the header row for stocks, and the change in price column will appear.

Note: If you see Question mark icon instead of an icon, then Excel is having a hard time matching your text with data in our online sources. Correct any spelling mistakes and when you press Enter, Excel will do its best to find matching information. Or, click Question mark icon and a selector pane will appear. Search for data using a keyword or two, choose the data you want, and then click Select.

More you can do

Cell with linked record for France; cursor clicking icon; card revealed

After you convert text into the Stocks or Geography data types, an icon will appear in the cell. Click the icon to see the card. The card reveals a list of fields and corresponding values. Depending on the data, there could be numerous field/value pairs that you can see and work with.

For example, in this picture the card for France is shown. Capital is one of the fields available for France. And Paris is the value for that field. Leader(s) is another field, and the leader names are the values.

If you want to see more field/value pairs, scroll down inside the card.

If you're curious where the fields and values are coming from, you'll notice the "Powered by" note at the bottom of the card.

Cursor resting over population field in card, and Extract button

You can also add fields from cards. With the card open, rest your cursor over a field. Then click the Extract button Extract button .

Cell A2 contains "France"; Cell B2 contains =A2. and formula autocomplete menu appears with fields from linked record

It is also possible to write formulas that use the values from the Stocks or Geography data types. This can be helpful if your data is not in a table. For example, type =A2 and then Excel's AutoComplete menu will appear, showing you the available fields for "France." You can also type a "dot", for example: =A2. and that will show the menu as well. For more information, see How to write formulas that reference data types.

Need help with linked data types?

Linked data types FAQ and tips

What linked data types are available in Excel? 

How to use linked data types with Excel

How to write formulas that reference data types

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×