My Online Training Hub

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

Excel Data Types

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

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

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel Posts

Excel Formatting Tricks
7 simple yet powerful Excel formatting tricks that will transform your spreadsheets into visually engaging and professional-looking reports.
Pareto Analysis in Excel
How to efficiently apply the Pareto Principle in Excel using sorting, filtering, PivotTables, and more to uncover key insights.
Dynamic Calendar
How to create a dynamic Excel calendar that updates automatically, highlights important dates, and adapts to any year.
Dynamic Dependent Data Validation Lists
Easily create dynamic multi-level dependent drop-down lists in Excel. Speed up data entry and reduce errors.
Spreadsheet Automation Tricks
5 essential Excel tricks to automate your spreadsheets and save hours of work each month. Learn how to update drop-down lists, consolidate data, and create dynamic text labels with ease.
Excel Tables Tricks
Excel Tables are a must have skill for all Excel users and their Structured References are a much simpler way to dynamically name ranges.
New Excel Formula Editor
The new Excel Formula Editor enables you to more easily write, debug, and manage formulas with a modern interface.
Microsoft Forms Update
Microsoft Forms simplify data collection with Excel integration and prefilled links accessible from any device. Create, edit, and sync forms.
Hidden Excel Tools Boost Productivity
Discover five hidden Excel tools that can transform your workflow and boost productivity, making you more efficient.
Make Money With Excel
How to make money with Excel turning beginner to advanced Excel skills into cash with these jobs and opportunities.


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 *