My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / Excel / Excel Data Types
Excel Data Types
by

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.

excel data types

* 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.

click the ‘Extract’ icon

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’:

Table Country column

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:

adding 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:

charts

Or PivotTables:

pivot tables

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:

sort and filter data types

If I select ‘GDP’ from the field list drop down, you’ll notice the sort and filter options reflect the GDP field:

select GDP from the field list

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.

Messy Data

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:

messy data

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:

states in Australia

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.

data selector 1

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:

data selector 2

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 refresh settings

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:

data type field error

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:

convert data type to regular 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:

other data types

Opening the card gives you a list of the data you can retrieve and how long ago it was updated:

open the card for list of data

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.

Please Share

If you liked this please click the buttons below to share.

email icon twittericon fb icon LI icon
Excel Data Types

More Excel Posts

tips for working in multiple excel files
Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel
Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates
Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files
How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel
Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online
5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.
10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features
Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation
Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT
The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.


Category:

Reader Interactions

Comments

  1. Salil V Gangal

    Hello,

    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.

    Reply

  2. Kishan

    Hi Mynda,
    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 !!!

    Reply

    • Mynda Treacy

      Hi Kishan,

      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.

      Mynda

      Reply

  4. Michael Bernstein

    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?

    Reply

    • Mynda Treacy

      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.

      Reply

  5. Yvonne

    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!

    Yvonne

    Reply

  7. Alex

    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 🙁

    Reply

  12. Col Delane

    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?)

    Reply

    • Mynda Treacy

      Hi Col,

      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.

      Mynda

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *