• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

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

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Data Types

You are here: Home / Excel / Excel Data Types
Excel Data Types
October 2, 2018 by Mynda Treacy

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

Hacks 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

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

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

Easily Remove Excel Password Protection

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

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

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

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

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

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.


Category: Excel
Previous Post:Excel Threaded CommentExcel Threaded Comments
Next Post:Hide Blanks in Excel PivotTables

Reader Interactions

Comments

  1. Salil V Gangal

    December 1, 2018 at 7:33 am

    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
    • Mynda Treacy

      December 1, 2018 at 9:41 am

      Hi Salil,

      Under the heading ‘Other Data Types’ I cover briefly demonstrate Stocks.

      Mynda

      Reply
  2. Kishan

    November 14, 2018 at 1:28 am

    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

      November 14, 2018 at 8:51 am

      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
  3. Alex

    October 5, 2018 at 12:30 pm

    Thanks for good features review as always!
    from which O365 version it will come?

    Reply
    • Mynda Treacy

      October 5, 2018 at 5:56 pm

      Hi Alex,

      All Office 365 versions will get Data Types.

      Mynda

      Reply
  4. Michael Bernstein

    October 5, 2018 at 5:17 am

    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

      October 5, 2018 at 10:52 am

      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

    October 5, 2018 at 3:05 am

    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
    • Mynda Treacy

      October 5, 2018 at 10:53 am

      🙂 I know what you mean, Yvonne.

      Reply
  6. Muneeb Saeed

    October 4, 2018 at 6:07 pm

    This is amazing! Looking forward to using this

    Reply
    • Mynda Treacy

      October 5, 2018 at 10:53 am

      Amazing, indeed 🙂

      Reply
  7. Alex

    October 4, 2018 at 4:54 pm

    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
    • Mynda Treacy

      October 5, 2018 at 10:54 am

      Hi Alex,

      You might want to try rebooting. I’m sure it’ll be there soon.

      Mynda

      Reply
  8. Marc

    October 3, 2018 at 11:59 pm

    Sounds exciting!

    Reply
    • Mynda Treacy

      October 5, 2018 at 10:54 am

      It sure is, Marc 🙂

      Reply
  9. Colin Coles

    October 3, 2018 at 5:27 pm

    Phenomenal! Can’t wait to get started with it!

    Reply
    • Mynda Treacy

      October 3, 2018 at 6:26 pm

      Glad you like them, Colin 🙂

      Reply
  10. Rudra

    October 3, 2018 at 4:56 pm

    Thanks for the update, Mynda! You are the best!!!

    Reply
    • Philip Treacy

      October 3, 2018 at 5:02 pm

      🙂 Thanks, Rudra. Glad you like the new data types.

      Reply
  11. Stewart

    October 3, 2018 at 11:26 am

    This is so cool

    Reply
  12. Col Delane

    October 3, 2018 at 11:02 am

    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

      October 3, 2018 at 6:39 pm

      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
      • mickael

        October 30, 2018 at 11:41 pm

        Thanks for all your work.

        What is the source for the stock data ?

        Best,

        Mickael

        Reply
        • Mynda Treacy

          October 31, 2018 at 1:01 pm

          Hi Mickael,

          All we know at this stage is that Excel uses Microsoft Knowledge Graph to provide the data.

          Mynda

          Reply
  13. Tina

    October 3, 2018 at 10:31 am

    does it come in excel 2016?

    cheers,

    Reply
    • Mynda Treacy

      October 3, 2018 at 6:28 pm

      Hi Tina,

      Only if you have an Office 365 license.

      Mynda

      Reply
  14. Terry

    October 3, 2018 at 10:03 am

    This is amazing. I can remember when Excel was a spreadsheet. I don’t know what to call it now.

    Reply
    • Mynda Treacy

      October 3, 2018 at 6:29 pm

      🙂 me too, Terry.

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.