Excel Data Types Overview

Excel Data Types are a feature to query external data sources without importing entire datasets and help simplify complex datasets. The feature, introduced in 2018, converts specific cells to allow those cells to contain more than a single, simple data element, such as a string or number. Data Types cells can contain a complete data structure, such as an array, matrix, or a row of data from a table with multiple columns.

Excel Data Types are available for the following:

• Predefined data, such as Geography and Stock data
• Organization data, such as Power BI hosted tables
• Custom data structures built by individual users in Power Query.

The illustration shows a workbook using the Geography Data Types structure. In the top screen shot, Column A contains a list of manually entered city names, over which the Geography Data Types was applied. By applying the Data Type, the cells are converted to the Geography type and Excel performs an automatic lookup, searching for each city’s name.