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


NetMarketShare Bows Out
04/11/2020

This news item was supposed to be about the latest desktop browser statistics and the fact that during October Edge had experienced an increase in its share. However, more newsworthy is the fact the O [ ... ]



Python Overtakes Java In TIOBE Index
06/11/2020

This month's TIOBE brings dramatic news. Python has replaced Java as the second most popular language. Although this was bound to happen sometime, we had not imagined it would be this soon.


More News

square

 



 

Comments




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