ICAEW.com works better with JavaScript enabled.

Excel Tip of the Week

Excel Tip of the Week #384 - Data types

Author: David Lyford-Smith

Published: 09 Mar 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Hello all and welcome back to the Excel Tip of the Week! This week, we have a Creator level post which will function as a brief overview of Excel’s Data Types feature. The basic Data Types (Geography and Stocks) are available in Excel 365, and if you’re on the Insider program or a faster update pathway you might have access to more.

What are data types?

Excel screenshot

Data types can be added to an Excel Table to mark a particular column as being a certain type of data. Excel will then do its best to identify each item in that column and associate it with an online database of information about items of that type.

Let’s take an example of some geographic data – the names of some cities. We type out our names and then try the Geography button to convert these to the City data type. Most are recognised automatically:

Excel screenshot

The “?” names are ambiguous or otherwise not recognised by Excel. By highlighting each, we are able to select which city we mean from a sidebar:

Excel screenshot

Once this is done, we have now set up our linked data type.

What do data types let you do?

Once a field is recognised as a linked data type, we can add new columns to our Excel Table automatically by pressing the special Add Column button that appears when we highlight any cell in the column:

Excel screenshot

We can then pick from a very large selection of preset information about that data type provided by Microsoft:

Excel screenshot

And we then see the resulting data added to the Table:

Excel screenshot

Like everything with Excel Tables, this connection is live and automatically updates, so we can add more cities on the bottom of the Table and Excel will automatically identify them and fill in any connected fields. This whole thing makes for an easily updated and flexible way of filling in live data on a variety of common fields. For example, you can get information about stocks and exchange rates using the Stocks or Currency types:

Excel screenshot

If your Excel version has it, you can also use the related STOCKHISTORY function to get the historic information about a particular stock or currency pair:

Excel screenshot

Try downloading the example file (if you have Office 365) and exploring for yourself!

You may also like

Excel community

This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.

Excel polaroid
Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.