AI powered Excel Data Types will transform the way we work with Excel by enabling a cell to contain much more than text, numbers or formulas.
There are currently two Excel data types available to Office 365 users: Stocks and Geography.
Let’s start with the Geography Data Type that can take a table of countries and return rich data that can be referenced in Excel formulas and expand into further columns.
Note: Excel Data Types require an internet connection.
Taking the example above, we can also view the underlying data for a single country by left-clicking on the geography icon in the cell to the left of the country name.
This brings up the card with the data available for that country, as shown below. The scroll bar reveals the different information available and if I want to add it to my table I simply click the ‘Extract’ icon in the card.
When we add fields to our table using the methods shown above, we aren’t adding hard coded text.
If you look at the formula bar (below), you’ll see it’s a formula that references the Table Country column [@Country] followed by the dot “.” operator and then the field name ‘Capital’:
Note: the formula in cell B2 above uses the Excel Table Structured reference, [@Country]. The [@Country] could be replaced with the cell reference, A2. i.e. the formula could also be written: =A2.Capital
We can therefore add fields by referencing one of the geography cells followed by the dot operator to bring up a list of available fields:
You can use these references in any function as they are full, calculation enabled, first class data types in cells.
They can also support charts:
Note: You cannot load Data Types into Power Query (yet) as it will generate an error on the column containing the Data Type icon.
Sort and Filter Excel Data Types
When you filter a column containing data types you have a new option that allows you to choose which field you want to sort or filter by, as you can see in the orange box in the image below:
If I select ‘GDP’ from the field list drop down, you’ll notice the sort and filter options reflect the GDP field:
Effectively sorting or filtering on a field not even present in the table itself. Wow!
That said, you can’t tell what field the data is sorted on once it has been applied. However, if you Filter on another field it will be retained in the filter drop down when you revisit it.
I've reported this to Microsoft in the hope that they retain the selected sort field information in a future update.
Excel is good at correcting spelling errors and incorrect capitalisation with intelligent conversions.
For example, the before (left) and after (right) images below show New Zealand and Papua New Guinea are corrected after the Data Type is applied:
Resolving Ambiguity in Excel Data Types
Sometimes there can be ambiguity when Excel tries to identify locations or stock codes that are present in multiple locations or multiple exchanges.
For example, the abbreviations for states in Australia are often found elsewhere. If I try to convert the table below to geography data types:
I get the result shown below where it finds New South Wales, but it’s not sure about WA and NT. For these locations Excel opens the ‘Data Selector’ pane and offers some suggested results. However, I want Western Australia for WA and it’s not listed.
In this case I can type in the full name of the state in the search box and then click the ‘Select’ button to insert it to my table:
And repeat for Northern Territory (NT).
Refresh Settings - New June 2021
Via the right-click menu you can set the data types to automatically refresh every 5 minutes, on file open, or manually:
Data Type #FIELD! Error
If the data doesn’t exist for a card Excel will return the #FIELD! error, as you can see below for Vanuatu’s armed forces size:
Convert to Text
Copying and pasting data type cells as values will retain the cards. To convert the data types to regular text, right-click > Data Type > Convert to Text:
Tip: Notice you can also refresh the cards here.
Other Data Types
So far, we’ve looked at countries, but Excel also supports zip codes/postcodes, cities, stocks, index funds and other financial data.
The example below are stocks on the Australian Stock Exchange:
Opening the card gives you a list of the data you can retrieve and how long ago it was updated:
More to Come
The Excel team have big plans for Data Types with more coming, including the ability to create your own data types unique to your organisation.
Imagine data types for Employees, Products, Stores, Regions… the list is endless.
If you liked this please click the buttons below to share.