Microsoft Adds Custom Data Types To Excel
Written by Kay Ewbank   
Tuesday, 03 November 2020

Microsoft is adding support for custom business data types to Excel. The addition will be made to Excel for Windows for Office 365 subscribers. The new facility seems powerful, but likely to cause confusion as people don't understand it, and equally likely to provide work for developers called in to sort out the resulting chaos.

The addition is being described by Microsoft as a progression from the introduction of dynamic arrays and array formulas, where a single formula could return an array of values, and the Stocks and Geography AI-powered data types.

excel

These connected data types meant that a single cell could contain a live, connected, set of information about objects like stocks, currencies, cities, and countries. Excel users can access the underlying data in the cell via formulas, filters, and charts.

When you've set a cell to contain geographic information, for example, it displays with an icon next to it showing that the cell holds a data type, and clicking on the icon displays a 'card' - a mini record showing all the data in that cell.

bingai2

If you want to get at the underlying data, you can choose from the defined fields for that data type and pull them into another column. Excel deals with this by dereferencing it into a formula. The connected data types, as the name suggests, are connected and can point to live services providing data that can be refreshed at any time.

 

bingai

The latest update adds more data types and the ability to create custom data types.  The additions start with around 100 predefined data types based on Wolfram Alpha data types, and the Power BI service will act as the connector to bring the data into Excel. Users will be able to discover Power BI tables through a data types gallery. You can then convert your cells into data types.

Microsoft says Power BI provides some of the most advanced governance controls over datasets, including over 140+ data sources on-premises and in the cloud, permissions-based access, row-level security, full application life cycle management, and the ability for any user to apply Microsoft Information Protection labels.

You can also create your own data types connecting directly to your data source, then using Power Query to create selection queries and transform the data to a set of connected data fields for use within Excel.

The new ideas sound like a tamed version of the old Excel and Office DDE (dynamic data exchange) feature that caused chaos and confusion a few years ago. Whether the problems have been tamed as well remains to be seen.

The facilities are currently available to try for members of Microsoft's Insider’s Beta program.

excel

More Information

Insider’s Beta program

Microsoft Custom Data Video

Related Articles

End Manual Data Entry in Excel - Thanks AI!  

Excel Adds New Data Types 

Microsoft Cognitive Services APIs Released 

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Banner


Firefox 1.0 Released 20 Years Ago
10/11/2024

A news item with the headline "Firefox browser takes on Microsoft" from 20 years ago has attracted renewed attention. It was originally published on the BBC News website on November 9th, 2004 rec [ ... ]



Azure Container Apps Dynamic Sessions Generally Available
02/12/2024

Dynamic Session support has been added to Azure Container Apps. Azure Container Apps is a serverless platform for running containerized applications, and dynamic sessions is designed to provide fast a [ ... ]


More News

espbook

 

Comments




or email your comment to: comments@i-programmer.info