There's a lot you can do with linked data types. Whether you want to reference connected data types in a formula or simply learn about some of the limitations, check out the content below for more details on how to make the most of data types in Excel.
Frequently asked questions
Note: Linked data types are only available to Worldwide Multi-Tenant clients (standard Microsoft 365 accounts).
In order to access data types that link to external sources of data such as Bing, you must meet the requirements below. To see what data types are currently available, see What linked data types are available in Excel?
For Stocks and Geography data types
-
Have an active Microsoft 365 subscription or a free Microsoft Account.
-
Have the English, French, German, Italian, Spanish, or Portuguese editing language added to Office Language Preferences.
-
Use the up-to-date Excel app or Excel for the web.
Note: If you don't have a Microsoft 365 subscription, you can only use these data types if you're signed in to Excel for the web with a Microsoft Account.
For Organization data types (from Power BI)
-
Have an active Microsoft 365 subscription through an organization or employer.
-
Have access to Power BI featured tables and they have to be enabled in Power BI for data types in Excel.
Note: For more info, see Power BI considerations and limitations. If don't see the data types you need from Power BI, please contact your IT admin for help.
If you've opened a workbook with data types or are trying to change or refresh a data type, and you get an error message about being unable to access these data types, try the steps below:
-
Make sure you're signed in to Excel with the account associated with your Microsoft 365 subscription.
-
Go to File > Account > and make sure Excel has the latest updates.
-
Check to make sure you meet the requirements to get data types.
-
If you're still having trouble, try opening the workbook in Excel for the web.
Note: You may have originally opened a workbook with data types in a version of Excel that only supports viewing linked data. For example, the workbook contains the Stocks data type so you can still view the card or access fields. However, you won't be able to refresh or change the data type. To use the data types, make sure you're signed in to Excel for the web with the account associated with your subscription.
There are many data types available on different subjects, but in Excel, they're organized into topic groups so it's easier to find the right button to convert with. To see all the data types available and what ribbon button they are under, see What linked data types are available in Excel?
Linked data types pull in reliable data from online sources such as Bing. If you want to view the type or source of a specific data type, you can select the data type icon in the cell to open the card and scroll to the bottom to find information about the data provider and the data type.
We’re continuously working to improve Excel data types. If you can't find a result, the data is incorrect or missing, or you can't achieve your task… we want to know! We’ll use your valuable feedback to make the experience and the feature better.
What feedback are we looking for?
-
Data quality (wrong, inappropriate, incomplete etc. data)
-
Data coverage (missing data)
-
Your experience (using data types and the data)
-
Suggestions and ideas
On data quality for Stocks, Geography
-
Once you convert to a data type, select the data type icon in the cell to open the card.
-
Scroll to the bottom of the card and select the flag icon.
-
Fill out the short survey and submit it. The more specific you are, the easier it is for us to understand the issue and fix it.
On data quality for Organization data types
-
Once you convert to a data type, select the data type icon in the cell to open the card.
-
Scroll to the bottom of the card to see the Contact field.
-
Reach out to the Power BI dataset contact in your organization.
For feedback on data coverage
-
Once you convert to a data type, you may get a blue question mark in the cell. This means we had trouble finding or matching a result. Select this icon if the Data Selector hasn't already opened.
-
the Data Selector pane, select Give feedback.
-
Fill out the short survey and submit it. The more specific you are, the easier it is for us to understand the issue and fix it.
For general data type feedback or suggestions
You can also send general feedback from directly in the Excel app about your overall experience using data types.
-
Go to the Help tab > Feedback. See How do I give feedback on Microsoft Office for more details.
-
Tell us what you like or dislike, or have suggestions about. Submit the form with as much detail as possible.
Yes. Unlinking cells is a way for you to remove a connection to a formula that is referencing data types. Select a cell with a linked data type, then press Ctrl+C or Cmd+C on a Mac to copy it. Press CTRL+ALT+V or Cmd+Control+V on a Mac, and then choose Values. This will paste the data type into the new cell and you won't lose connection to the data provider.
Yes, but they will need to meet the requirements to be able to use certain data types. If you sent the workbook to someone who doesn't meet the requirements, they will still be able to see the data from data types, however will not be able to change or refresh them.
At this time, only Excel for Microsoft 365 and Excel for the web support linked data types. 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.
Yes. Most Excel features work with data types, and we are continuously working to improve integration. The Fill handle works when rows or columns have the same data type and you're populating cells with the same field. If that field has no data or is missing, you'll get the #FIELD error.
You can also enter text to convert in the last cell of a list of data types and Excel will automatically attempt to match your text to convert it.
Some traditional Excel features may not function well with linked data types. For example, if you create a PivotTable based on information from data types, you can only use the information explicitly added to the sheet. And, using these data types with Power Pivot, Power Query, or even some charts may not work as expected.
Certain data types are only available in specific languages at this time. In order to access and use them, you can add an editing language to your Office Language Preferences.
-
Stocks and Geography data types: English, Spanish, French, German, Italian, and Portuguese only.
We are working on adding more languages in the future.
Tips and tricks
Open a card:
Press Ctrl+Shift+F5 for Windows or Cmd+Shift+F5 for Mac.
Add a column:
Press Alt+Shift+F10 to open the Insert Data menu. You can also press the Ctrl or Cmd key. Then use your arrow keys to move to a field, and press Enter to choose a field.
Linked data types connect 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 a cell with the linked data type and click Data Type > Refresh. That will refresh the cell you selected, plus any other cells that have that same 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.
With data type cards, you can extract more than just your typical values. Some data type cards, such as those for the Geography data type, includes images that you can insert into your workbook. These images are contained inside the cell, like any text or number.
-
Convert text to a data type by selecting the cell and going to Data tab > Data Types group > Automatic.
-
Once converted, an icon will appear in the left of the cell value. Select it to view the card.
-
In the card, hover over the image and you'll see the Insert Data
-
The image will scale to fit within the cell. To resize it, simply adjust the row or column to increase the size of the cell.
Tip: To view the attribution of the inserted image, right-click the image and select Show Card.
If you don't want a cell to be a linked 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.
Important: 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.
Data types and the data you insert from them can be used in formulas and calculations, just like other data. It is also possible to write formulas that reference the values from the linked data types, even if your data is not in a table. For more information, see How to write formulas that reference data types.
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 check boxes 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.
More about linked data types
Excel data types: Stocks and geography
