Using Data Types in Excel



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

Select the countries click the Geography button

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:

Some country names

Click the Insert, Table button:

Convert to table

Remember to set the name of the table:

Name the table

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

Select the countries click the Geography button

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

Excel adds a symbol in front of the country names

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

Excel displays a card with detailed information

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:

Adding new columns is easy

Select Abbreviation and press enter and you get:

A new column using a brand-new formula syntax

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:

A new column using point and type

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:


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:

The source of the information on 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:

A security warning

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:

An error shows if the cell has a formula

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:
    Refreshing the information of a data type
  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:

Ambiguous data leads to questions

Clicking the question mark icon opens the Data Selector pane:

Fixing ambiguous data

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.


All comments about this page:

Comment by: Will (2-8-2023 06:11:00) deeplink to this comment

Hi there. Any ideas for how to hide the geography icon that appears in each cell?

Comment by: Jan Karel Pieterse (2-8-2023 09:21:00) deeplink to this comment

Hi Will,

You can select all cells with the icon and right-click. CHoose "Data type", "Convert to text".

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].