Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
Home > English site > Articles > Data Types
Deze pagina in het Nederlands

Using Data Types in Excel

Introduction

If you have an Office 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.

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:

=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:

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.


Comments

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.

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

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

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.