Using Data Types in Excel

Content

• Introduction
• Who has access to Data types, internationalization
• Setting a data type to a series of cells
• Using the information of a cell with a data type
• What's in those cells?
• Where does the information come from?
• Which cells can be converted to a Data Type?
• When is my content refreshed?
• What if Excel get's it wrong?

Introduction

If you have an Microsoft 365 subscription perhaps you have noted the Data Types group in the Data ribbon:

Let's see how you might use this feature.

Who has access to Data types, internationalization

Apart from needing a Microsoft 365 license, there are some more things to consider with Data types:

Stocks and Geography behavior today:

• The data sources are currently English only.
• The data is derived from the Editing language.
• If English is included in the Editing Language list, you will get Data Types in the gallery. Even if another language is set as preferred.
• The property names (like "Population") and text data will all be in English. The property names are NOT like function names and do not change when you change your settings.
• The display Language does not impact the data types data, it only impacts the tooltips/Ribbon button text etc. Because we use a cached service connection layer, unfortunately you must restart Excel twice to see the Data Types Ribbon buttons change language. We support the UI localization in all supported office languages.

Stocks and Geography behavior with additional Languages

• If you have a 'supported language' set as your default/preferred proofing/editing language we will use data for data types from that language.
• If you have a default editing language that is currently unsupported (e.g. Dutch, Russian sorry) but have a supported language installed in your editing list, the data for data types will be in the supported language. If you have multiple supported languages with none set as preferred in the list, we fallback in a set order. English, Spanish, French, German, Italian, Portuguese.
• If you change your preferred editing language (or remove a fallback language), any data types that you have created using English data will remain in English (even on refresh). After restarting Excel (twice ), any new data types you create will remain in the language set when they were created. Refresh will not break your formulas that are already written.
• When your preferred language does not match what the data from data types will be, we will include a hint in the ribbon buttons/tooltip to show what language is being used.

Some Notes: The data provider controls what properties and values are available in additional languages, the data may not be the same across English data types and another language. The 'conversion' is supposed to respect the language targeted, so using native spellings of the locations to the language should work.  You can see from this screenshot we are quite production ready (like Population isn't translated). But this is French data Data Type.

Setting a data type to a series of cells

Data types work best when your data is in a table (but it isn't mandatory). Suppose you have a small table with country names like shown here:

Click the Insert, Table button:

Remember to set the name of the table:

Now select the first column of the table and click the Geography button:

It takes a bit of time and then Excel adds a small icon in front of each cell:

If you click on any of the icon's denoted with '1', you're presented with a card displaying detailed information about that cell:

Using the information of a cell with a data type

Excel makes it easy to add columns containing pieces of information from a cell which contains a data type. Just click the little add column icon:

Select Abbreviation and press enter and you get:

This reveals the underlying new syntax. To get a property from a cell with a data type, just type the address of that cell (or click the cell) and hit the period key to get a drop-down with the appropriate properties:

What's in those cells?

You may have the impression that not much has changed by converting a cell to a data type cell. All that appears to have been added is that small icon. But you should be aware that all data you are able to view by clicking that icon is in fact already stored in that same cell. With data types, Excel cells can now contain far more than just one value, they can contain an entire object with all of its properties.

This also means that the dot syntax to extract properties from the data typed cell really does that: It fetches the property information from the content of the cell. In other words, this formula:

=A2.Capital

actually gets the Capital from the country that is shown in cell A2 from that cell. The information about the capital of the country shown in A2, is also in cell A2.

Where does the information come from?

If you display the card of a country like I showed above and scroll down to the bottom of the card you can see what sources were used to fill the card:

Because Excel now includes a connection to the outside world, you'll get the "business bar" with a security warning when you open the file:

Click Enable Content to make sure Excel can fetch the most up-to-data content for you.

Which cells can be converted to a Data Type?

Only cells with a hard-coded value can be converted to a data type. If a cell contains a formula, you get:

When is my content refreshed?

In short: When you ask Excel to do so. There are two ways:

1. Right-click on a cell containing a data type, click Data Type, Refresh:
   
2. Click the Refresh All button on the Data tab of the ribbon

What if Excel get's it wrong?

Any of the cells you are trying to convert to a data type may contain ambiguous information. For instance, if you typed 'America' into a cell and try to convert that to a Geography data type, Excel displays a question mark icon next to that cell:

Clicking the question mark icon opens the Data Selector pane:

Click "Select" below the one you want the data type cell to use. Note that Excel will update the cell with the descriptive text of the item you clicked.

If you change your mind, right-click the same cell, choose Data Type >, Change and you'll be presented with the Data Selector again.