Two new data types in Excel
In the first of a three-part series, Liam Bastick introduces the latest data types that allow Excel to do even more: it can recognise real-world concepts, beginning with Stocks and Geography.
For most of us, the last thing we need in this world is more jargon. However, it’s important to understand what a data type is. If you use Excel on a regular basis, you will have already been taking data types for granted.
Some consider the data types in Excel to be separated into three divisions: labels, values and formulae. It’s not quite that simple. For example, just consider the values £28, 28% and 28 January 1900. These are three interpretations of “28” since they relate to currency, percentage and date respectively.
In computer science and computer programming, a data type or, simply, 'type' is an attribute of data which tells the compiler or user how the programmer intends to use the data. It is a classification that helps users understand the data.
If you have ever used Excel, you will probably have input text, integers, decimals, percentages, dates, and so on. These are all examples of data types prevalent in Excel (simply capitalise the first letter): you are more familiar with data types than you might think.
Meet the latest data types
Excel has been used for many years to convert data into information, helping users to interpret their numbers. There are now two new data types that allow Excel to do even more: it can recognise real-world concepts, beginning with Stocks and Geography.
This new Artificial Intelligence (AI) powered capability turns input data into an interactive property (known by Microsoft as an “entity”) containing layers of “data rich” information. Hence, these are colloquially known as rich data types.
For instance, by converting a list of countries in a workbook to “Geography” entities, customers can weave location data into an analysis of their own data. This is just the start; over time, Microsoft plans many more rich data types.
For example, here is an illustration of the new Stock rich data type:
In this graphic, the cells with company names in column A contain the Stocks data type. You may tell this because they have the icon for Stock next to the company name (featured above). The Stocks data type is connected to an online source that contains more information. Columns B and C are extracting that information. Specifically, the values for price and number of employees are getting extracted from the Stocks data type in column A.
You should note that Excel issues a warning:
This data is provided by third-party providers and Microsoft wants to make it clear that they will not be held responsible for any analysis performed in Excel in this manner!
As always (and quite rightly), the user should be held responsible for checking the veracity of any and all inputs used in the decision-making process using Excel.
Stocks is not the only new data type. Here is an example of a Geography data type:
In this example, column A contains cells that have the Geography data type. This time, the Geography icon next to the country name (above) indicates this. This data type is connected to an online source that contains more information. Columns B and C are again extracting that information. Specifically, the values for population and the dial (call) code are getting extracted from the Geography data type in column A.
Clearly, you can see how these data types might be useful. So how do you set it up? In part two of this series, Liam outlines a simple six-stage process to create these data types.
You might also be interested in
Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their...