Using Data Types in Excel
Content
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:
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.
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:
- Right-click on a cell containing a data type, click Data Type, Refresh:
- 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.
