Home Newsletter

Deze pagina in het NederlandsHome > Article index > Data Types

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:

Stocks and Geography behavior with additional Languages

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.


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: www.eileenslounge.com.

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