Blog Post

Excel Blog
3 MIN READ

Announcing Data Types APIs: Data Types in Excel Add-ins

Chris_Gross's avatar
Chris_Gross
Icon for Microsoft rankMicrosoft
Nov 03, 2021

Excel is the most frequently used tool for storing and working with the world's data. We are amazed every day by the ways in which you, our customers, use Excel to make better decisions, leveraging the flexibility of the grid and formulas to capture, analyze, and collaborate on data.  

 

Until recently, Excel has only had a couple of base types of data you can work with, such as text and numbers. We have been on a multi-year journey to up-level the types of data you can work with natively in Excel, and you may have seen examples of this already. The first was the introduction of the Stocks and Geography “data types.” These linked data types meant for the first time, a single cell in the grid could contain a live, connected, and rich set of information about objects like stocks, currencies, cities, and countries.  

 

Last year, we expanded on this vision by allowing customers to work with their own data as a data type. For Power BI customers, Excel will automatically connect to Power BI, and any data you have published will be easily discoverable and flow into Excel as a structured data type from Power BI. If you do not have Power BI, you can still leverage the Power Query technology in Excel to manually connect to dozens of data sources, and specify that you want the data to be structured as a data type through Power Query.  

 

Now, we have taken linked data types to a whole new level again and are giving developers the ultimate freedom to build in Excel. Later this month, we are giving you a new JavaScript API in Excel, available in preview. This new API allows developers to create their own custom data types containing images, entities, arrays, formatted number values – backed by their own custom data sources, in addition to allowing for the creation of custom functions which can make use of these new Excel Data Types, both as inputs and outputs. 

 

Using one simple function that references the identifier you are looking for; you no longer need to decide what dimensions you want up front. 
 

These more flexible structures give you the ability to organize complex data as objects and expose this data to users in more natural ways, while still providing easy access to the full information users care about for their analysis or reporting.  

 

 

Additionally, customers will be able to create their own add-ins, or extend previously existing ones to utilize the power of data types, resulting in a more integrated and next-generation experience within Excel. You can share the power of data types across your entire organizations and create add-ins or solutions which can connect data types to your own service or data. 

with Data Types and custom functions, you can easily alter your query and see even more details than are shown in the immediate analysis. Additionally, rich images can be contained within the image and extracted at the click of a button.

 

Availability  

The new JavaScript API will be available in public preview at the next deployment of the Office.JS Preview APIs which we expect to take place near the end of the month. 

 

Once the feature is available, you will need: 

  1. Office.JS Preview API 
  1. An Insiders Beta build of Mac or Windows Excel. Click here to learn more about Office Insiders and join today

Learn More

To learn more you can check out

 

With this new API, we are giving the ultimate power to end users and developers, allowing them to better organize, access, and work with their data. We hope you will try it out and share with us the unique solutions and applications you create! 

Updated Nov 03, 2021
Version 2.0
  • govertvd's avatar
    govertvd
    Copper Contributor

    +1 to the suggestion of lori_m that we can also define and use the new data types through the C API from the Excel XLL SDK.

     

    There are many Excel extension languages like C#, VB.net and F# (through Excel-DNA), Python (PyXLL), Java (Jinx) and even C++ (xlw and XLL+) that would then be able to use these experiences too. That way many more Excel add-in developers will be able to integrate with these powerful new data type features.

  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Steel Contributor

    It's really a great news,,,, now tell me does this supports to all version of XL or to higher versions only ?

  • This is awesome, now people can create their own functions for their different types of data. thanks a million.