We’re excited to announce new capabilities that let you programmatically create your own custom data types within Excel. This helps solve a problem that developers have today with metadata requirements associated with cells. With this new set of capabilities, you can leverage the technology behind data types that power the experiences of Wolfram, Stocks, Geography, and other linked data types. Onboard your add-in to the next generation of Excel experiences with data types and reduce your time to market by reaping the benefits of our deeply integrated feature set.

Excel data types

More than just text and numbers

Countless solutions have been built with Excel. Many of these solutions are powered by cells that often contain (or evaluate to) just text or numbers. With the addition of data types, Excel has evolved to a world where cells can contain richer content. Linked data types (Stocks, Geography, Wolfram, and Power BI), entity values, dynamic arrays, improved errors, images in cells, lambdas, and formatted number values are all new types that have delivered on this promise and back many of the experiences you see introduced in the product today.

Increase developer velocity

With the addition of the Excel data types APIs, we want to empower developers to take advantage of many of the experiences we’ve built that power data types. This reduces the complexity of your add-in and eliminates the need for custom solutions that power many add-ins today. We’ve built out data types, inspired by many of the patterns we have seen across all the solutions built in Excel.

For example, if you have a solution that creates another dimension on top of your data, you can use a card view. If you want to assign a given format to a value and have it travel through the calculation engine, you can use formatted values. Ultimately, if you need another dimension added to your data, you can use our composable types to deliver a truly custom experience for your users.

New Range.valuesAsJson API

The first API we’re introducing, Range.valuesAsJson, allows you to both read and write data types. If you’re familiar with the Range.values API, this API works similarly. The major difference is that this new API can return augmented information about basic types (text, numbers, errors) as well as information about the new data types we have introduced. One other notable difference is the introduction of a schema that conforms to the types that are available to Excel.

Our focus has been on exposing the structures so that you can bring your data to Excel using our schema and leverage many of the built-in experiences today. Additionally, we are allowing you to make use of these types within custom functions. This opens the door to create new experiences and innovate on what is possible with the product today.

Flexible containers

The following is the complete list of types we’re exposing in this first release.

• Entity values
• Formatted number values
• Web images
• Improved errors
• Arrays*

*Only supported as a property of an entity and cannot be a standalone value currently.

With these value types, you’re free to use them as both inputs and outputs for your experiences, and you can also arbitrarily combine them.

For example, you can create an entity value that contains properties of all the types, such as a web image for richer context about the entity, formatted values for things like currencies or dates, arrays of types for lists of data, and even additional entities through nesting.

This allows you to take your flat data and package it up into a logical value, as shown in the following example.

Schema

As noted, the structures are all driven by our schema. Here are some quick examples of JSON that adheres to the schema and creates a few types. Be sure to take note of the type field, which tells Excel what type a given value is.

We are building this API with the world of services in mind. We want to make it easy for developers to extend any service and easily expose data to Excel through custom add-ins. It’s as simple as adopting our schema and passing the schematized values for display and re-use within Excel.

Custom functions support in preview

In addition to supporting the read and write of data types, we’ve also added support for data types within custom functions now in public preview. This unlocks a whole new class of functions that customize analysis or generate reporting, and takes advantage of the powerful Excel calculation engine.

You can either generate data types through custom functions or take created data types as the function arguments into calculation. The same schemas are used when interacting with data types using custom functions. So, after you finalize the structure of data types, we’ll keep the consistent experience between APIs and custom functions. For more information, see Get more information about Custom Functions.

Availability of Excel data types

We’re in the process of rolling out support for data types. The next release of the preview APIs will add support which we expect to happen soon.

Upon release, to access these new APIs, you’ll need:

1. A valid Insiders: Beta build of Excel
   1. Windows: greater than or equal to 16.0.14626.10000
   2. Mac: greater than or equal to 16.55.21102600
2. The latest preview version of the Excel JavaScript APIs

We’ll continue to make this available to a wider audience over the coming months, so watch for updates.

Learn more about Excel data types

To learn more about data types and access the documentation, see our developer overview and Ignite announcements.

• Overview documentation
• Announcement video
• Deep dive with demos

Connect with us

We continue to deliver new capabilities based on your needs. The best way to stay up to date is to follow this blog. And, join our monthly Office Add-ins community call, the second Wednesday of each month at 8:00AM PST/PDT, to connect with us and our community. See you there!

Happy coding!