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.
* The new data types are starting to roll out to Office 365 subscribers over the coming weeks and months.
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.
Salil V Gangal
Towards the beginning you wrote:
>> 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.
I thought with the verbiage “Let’s start with the Geography Data” you are going to start with it, and then end the article with datatype “Stocks “.
But there is no indication of datatype “Stocks” anywhere afterwards. Did you now write anything further, or I am not seeing the details? Please clarify.
Under the heading ‘Other Data Types’ I cover briefly demonstrate Stocks.
Thank You so much for this lovely article. I am wondering if you could help me to convert ” ’12-NOV-2018″ to date format. The character ” ‘ ” is not letting me to convert to date format. Is there any better way to convert this to date format other than by deleting the special character ” ‘ ” of individual cell which is very tedious in thousands of cells. Thank You !!!
Have you tried the DATEVALUE function?
It’s difficult to tell whether that apostrophe is as entered in the comment here, or one entered in Excel. They are different and behave differently. If DATEVALUE doesn’t work then we’ll need to see the file. You can post it and your question on our Excel forum, where we can help you further.
Thanks for good features review as always!
from which O365 version it will come?
All Office 365 versions will get Data Types.
This is so fun and very exiting. Thank you for update. I do have office 365 license but not seeing Data Types. Is there something I need to activate or is it being rolled out to general public at a later date?
Great to know you like them, Michael. You don’t need to do anything to get the Data Types, they’ll be rolled out automatically. If you want to be one of the first to get them then you can join the Monthly update channel if you have a ProPlus license.
Thanks Mynda!!! Very excited about this. Very ready to explore creating my own data types. Hopefully it’ll come soon!
Thanks for always keeping me informed and ready to tackle new features. I may never leave my desk!
🙂 I know what you mean, Yvonne.
This is amazing! Looking forward to using this
Amazing, indeed 🙂
Great news, great features! Thanks for review!
just yesterday got new O365 updates version 1809 (Build 10827.20138) the interface design of Outlook is new, but Excel is still old 🙁
You might want to try rebooting. I’m sure it’ll be there soon.
It sure is, Marc 🙂
Phenomenal! Can’t wait to get started with it!
Glad you like them, Colin 🙂
Thanks for the update, Mynda! You are the best!!!
🙂 Thanks, Rudra. Glad you like the new data types.
This is so cool
1. What’s the source of the variable/statistical data (e.g. population, GDP) dragged in by these new data types, and what’s its integrity?
2. Is this data a snapshot at the time or automatically refreshed (when?)
1. Excel uses Microsoft Knowledge Graph, the same intelligent service that powers Bing, to provide the data.
2. A snapshot in time that can be refreshed on demand by right-clicking > Refresh.
Thanks for all your work.
What is the source for the stock data ?
All we know at this stage is that Excel uses Microsoft Knowledge Graph to provide the data.
does it come in excel 2016?
Only if you have an Office 365 license.
This is amazing. I can remember when Excel was a spreadsheet. I don’t know what to call it now.
🙂 me too, Terry.