To date, Excel and similar apps deal primarily with text and numbers as data types. However, that tradition is about to be a thing of the past as Microsoft is adding two new data types to Excel. These data types allow cells to contain rich, intelligent data that can better represent more real-world data types.
Suppose you are putting together a spreadsheet that will plot the relationship between a company’s sales and population in South America. The sales data is easy enough to find, but tracking down the latest population for each South American country might be a bit time consuming and error-prone. At the last minute, someone asks for data that shows sales related to the size of the country, which means another session of hunting down the right information.
What if you could have all that information for a country — population, square miles, map, gross national product, average minimum wage and more – all contained in a single cell within your worksheet? Believe it or not, those days are not too far away.
There are two new intelligent data types available in Excel: Geography and Stocks. That means that cells in your Excel workbook are no longer limited to holding flat information like text, numbers, or dates. Cells can now house an incredible amount of information related to geography and stocks. Not only can you access this information easily, you can even work with it when you are offline. Both of these data types can be found under the Data tab in Excel, and converting existing data to either of these types is very simple.
Let’s suppose we have a worksheet that contains a single-column table. The table contains strings that represent countries. To convert this data to the new Geography data type, highlight the country names, then go to the Data tab and click on Geography. This takes care of the conversion.
You’ll notice that an icon appears in the cells next to each country name. It resembles a map that has been unfolded. If you click on that icon, you’ll see a data card that contains tons of information about that country. Now that cell is no longer just a string of characters, but a rich data type with much deeper meaning. All of the data from the data card is actually contained in that cell, and you don’t need an internet connection to access that data.
You will notice that a widget appears to the right at the top of the table. If you click on it, it offers to add another column. You can select from a list of available fields based on the data contained in the card you just looked at.
The Stock Data type works in a similar manner to the Geography data type but provides access to data involving stocks. Let’s say you have a table with a single column that contains some company names and some ticker names. You highlight that data, then go to the Data tab and select Stocks. That converts the string data into the new Stock data type, and all the names are switched to company names. You’ll notice that an icon appears by each company name, allowing you to access the data card for that company.
Stock data changes quickly, unlike the Geography data. Because of the dynamic nature of Stock data, the data is refreshable. Some of it is available in almost real-time, while other data will be delayed. If you want to do calculations with cells that contain either the Geography or Stock data type, type in a formula referencing the cell number and then use the . (dot operator) to select the correct member of that geography object. Anything you can do with normal data, you can do with these new data types.
The Microsoft Knowledge Graph, the intelligent service that also powers Bing, is what provides the data. When someone points out that the Stock and Geography data types are intelligent, that means far more than fixing typos or spelling errors. For example, these intelligent data types can interpret data requests in context. It may ask for more specifics if you enter a city name and convert it to the Geography data type because it wants to make sure what city you mean. However, if a city is listed with other city names in a particular geographical region, then Excel will select a city in that particular region (context).
Not all Excel 365 users can access these new AI data types just yet. According to Microsoft,
“The new data types are being released as preview to Office 365 subscribers enrolled in the Office Insiders program, in the English language only, starting in April 2018. “
However, it will eventually be rolled out for all Office 365 users. And other AI data types will also be added to Microsoft Excel’s repertoire. These developments mean that in Excel you can do even more, even faster.