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.

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.

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.

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.

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.

Comments

Make a Comment or View Existing Comments Using Disqus

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