Director SumProduct
Share this content

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.

19th Aug 2020
Director SumProduct
Share this content
Budget planning
istock_simpson33_aw

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:

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:

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:

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.

Replies (2)

Please login or register to join the discussion.

avatar
By ian339
20th Aug 2020 12:55

You might mention, at the start of the article, which versions of Excel this applies to!

Thanks (0)
avatar
By C.Y.Nical
22nd Aug 2020 15:22

The only way I can imagine this could work would be by the spreadsheet interrogating an off-site data source "in the cloud". If that's right, does it mean a spreadsheet which might contain data subject to GDPR, or sensitive financial information, is in some way communicating off-site, in the background? If so, how do I go about satisfying myself that the communication is one-way only (from external data to spreadsheet) and not two-way with my data "leaking" outside my control? I'm actually finding it hard to see how it could be one-way cloud to spreadsheet because the spreadsheet has to communicate with the cloud, find the intended data source, and tell that source what it is looking for. You've got two-way communication right there. And it's all happening in the background using code I can't check. You're asking me to place a hell of a lot of trust in this, aren't you? Trust which goes way beyond trusting the data the spreadsheet fetches is accurate.
It seems to me a bit like installing an app on your phone which gets access to all sorts of stuff you don't really want it to have, and which it shouldn't really need to do its job, but which the app developer would like to get their hands on.
I'm not sure I like the sound of this one bit.
But that's just me. I expect the world at large will just lap this up.

Thanks (0)