A busy day over at Ignite 2021 gives us an early look at a significant new feature in Excel that has the potential to have a huge impact in the coming years.
API access to custom data types
This combination will have a multiplier effect for what Excel developers and add-in authors will now be able to do.
Adding third party functionality to Excel – in an automated manner – just got a huge boost.
Custom data types
Custom data types have been around for a while now and give you the ability to define you own data objects, with various properties (and relationships between properties) that can give Excel a lot more power as a tool for custom data structures.
The first in-built examples included:
- Geographical data (places, cities, locations etc), containing rich information such as population statistics and place features: think “Atlas meets Encyclopaedia”
- Movies, containing rich information such as release dates, actors, genres, etc: think “IMDB meets LinkedIn”
- Stock history, containing rich information about companies and their stock price history: think “Bloomberg meets Yahoo Finance”
There are now many more examples. You can use custom data types for most things you might use a database for – anything that has a data structure. Ideas, vehicles, the entire animal kingdom – go for it!
Any record in a custom data set (such as a city, movie, company, idea, vehicle, animal, etc) can be represented in a single cell in Excel when using that data structure. Various properties or attributes of that item (think “database fields”) are accessible from within that cell, as part of the extended data structure.
Custom data types lets you define your own data structure and create your own custom objects.
Since 2016, programmatic API access to Excel has been offered for developers, with a gradually increasing functionality set.
New APIs are added in preview before being deployed into production software versions. The custom data types API has just commenced in preview, so will be evaluated further before being deployed.
Being able to perform CRUD functions on custom data types – using inputs created dynamically or programatically – helps unleash the power of custom data types for developers.
Access to information available on the web just got opened up, in particular the ability to specify how that information is stored and how it relates to other information. This is a big development!
The API for creating and manipulating custom data types is in preview, which means it is available for “insider” Excel developers to play with and evaluate.
Excel has long been a default database tool used by people who want to create a database, without actually creating a database.
Who knows what capabilities add-in developers will now be able to unleash? Using custom data types through the new API, the possibilities just got much more exciting.
To read the Ignite 2021 announcement about all this, see Ignite 2021: Excel add-ins and data types.