Excel Tip of the Week #384 - Data types

What are data types?

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:

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:

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:

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

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

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:

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:

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

You may also like

• Previous post - Revisiting ranking functions
• TOTW index
• Next time – Power Query – Creating transaction references!