Stock quotes and geographic data

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

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 new, and they 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.

Note: June 12, 2018: These data types are currently beta features, and only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Office 365 subscribers.

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

Cell A1 contains "Country", and cells A2 through A4 contain country names: France, Spain, Sweden

Type some text in cells. In this example, we wanted data based on geography, so we typed country names. But you could also type province names, territories, states, cities, etc. If you want stock information, type company names, fund names, ticker symbols, and so on.

2. Create a table

Insert tab, Table button

Although it's not required, we recommend creating an Excel table. Select any cell in your data and go to Insert > Table. Later on, this will make extracting online information easier.

3. Select some cells

Four cells selected, each has a country name

Select the cells that you want to convert to a data type.

4. Pick a data type

Data tab, Stocks and Geography buttons

On the Data tab, click either Stocks or Geography.

5. Icons appear

Four cells, each has a map icon and a country name

If Excel finds a match between the text in the cells, and the 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. Add a column

Population field selected in menu

Click the Add Column button Add Column button , and then click a field name to extract more information, such as Population. If you don't see this button, make sure to follow step 1 and create an Excel table first.

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, scroll to the very bottom of the card and click the source links.

If your data is in an Excel table, you can retrieve more information by adding columns that extract information from the Stocks or Geography data types.

1. Select a cell in a table

Linked record cell selected in a table

Make sure that your data is inside an Excel table, then select a cell in the table.

2. Add a column

Cursor clicking Add Column button

Click the Add Column button that appears at the upper-right corner of the table.

3. Select a field

Population field selected in menu

A menu will appear with a list of the available fields. Select one you're interested in.

4. A new column is created

New column added, values coming from linked record

A new table column is created, showing the values for that field.

5. Do it again

Second column of data added

Repeat that process to get even more data.

6. Another new column

Another new column added to the table

Another new column is added, showing the values for the field you chose.

Tip: When your data is in an Excel table, you don't have to use the Add Column button. Instead, just start typing a new column head. For example, if you typed Population as a column head, Excel would automatically retrieve the Population field for the nearest data types to the left. The same thing works for rows: Type a new row, and if there is matching information found online, Excel will connect to it, and return the fields available.

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.

Just use the dot operator (.) which tells Excel to use the data type's field as a parameter. In this example, we typed =A2. and then Excel's AutoComplete menu appeared, showing us the available fields for "France." For more information, see How to write formulas that reference data types.

Tips and tricks

Press Ctrl+Shift+F2.

Press Alt+Shift+F10 to open the Add Column menu. Then use your arrow keys to move to a field, and press Enter to choose a field.

The Stocks and Geography data types are considered linked data types. This is because they refer to an online data source. Once you convert text to a linked data type, an external data connection is established in the workbook. That way, if the data changes online, you can update it by refreshing it in Excel. To refresh the data, right-click the cell with the linked data type and click Data Type > Refresh. That will refresh the cell you selected, plus any adjacent cells that have a linked data type.

If you want to refresh all linked data types and all data connections that may be in the workbook (including queries, other data connections, and PivotTables), click Data > Refresh All or press Ctrl+Alt+F5.

You can switch out the information for a Stock or Geography data type. Just right-click the cell, and click Data Type > Change.... A pane will appear on the right. Search for the data you want, and then click Select to put that information in place of the original one.

If you don't want the Stocks or Geography data type anymore, just right-click the cell, and click Data Type > Convert to Text. The data type is removed, there's no longer an online connection, and the value in the cell is converted to text. Keep in mind that if you convert the data type to text, any column or formulas that had extracted values from that data type will display the #FIELD! error.

Filter menu, Display Value menu, fields from linked data type listed

You can always sort and filter data in Excel by using the filter buttons on the column headers. (Turn on filter buttons by clicking Data > Filter.)

But here's a tip for cells with data types: Click the filter button above the cells with icons. Then decide how you want to sort or filter. If you want to do so using the displayed name, keep Display Value selected under Select field. If you want to sort or filter by another field from the linked data type, select that field under Select field.

In this example, we selected the field Area. Excel then used that as a basis for the filter checkboxes below, and displays the populations for the countries we have in the table. Also good to know: Excel will change the Number Filter menus depending on the field you select under Select field.

Frequently asked questions

Excel has always had data types. But they've been traditional ones like text, number, and so on. The Stocks and Geography data types are unique because they are considered linked data types. Linked data types contain a connection to an online data source. At this time, Stocks and Geography are the only linked data types. We plan to support more in the future, based on customer feedback.

Yes, but they need to use Excel 2016 for Office 365 subscribers.

Note: This feature is being made available to customers on a gradual basis over several days or weeks. It will first be available to Office Insider participants, and later to Office 365 subscribers. If you are an Office 365 subscriber, make sure you have the latest version of Office.

At this time, only Excel 2016 for Office 365 subscribers supports the Stocks and Geography data types. Other versions of Excel may eventually support these data types, as we are continually improving Excel. If you open these new data types in a version of Excel that doesn't support them, #VALUE! will appear for those cells. And #NAME? will appear in place of any formulas that reference these data types.

If you are using the Stocks or Geography data types in a table, you can type under the last row of the table. When you press Enter, Excel will automatically attempt to match your text with online data, and convert it into a data type. However, you cannot select a cell with a data type, and drag the fill handle down to link up text that's not already a data type. The same thing goes for the Format Painter.

The Stocks and Geography data types are very new, and there are some traditional Excel features that will not function well with them. For example, if you try to create a PivotTable based on information from these data types, you'll get a #VALUE! error. And, using these data types with Power Pivot, Power Query, or even some charts may not work as expected. 

At this time, these data types are only available if you have the English editing language added to your Office Language Preferences. We hope to support more languages in the future.

See also

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.

×