Overview of data types in Excel add-ins

Data types organize complex data structures as objects. This includes formatted number values, web images, and entities as entity cards.

Prior to the data types addition, the Excel JavaScript API supported string, number, boolean, and error data types. The Excel UI formatting layer is capable of adding currency, date, and other types of formatting to cells that contain the four original data types, but this formatting layer only controls the display of the original data types in the Excel UI. The underlying number value is not changed, even when a cell in the Excel UI is formatted as currency or a date. This gap between an underlying value and the formatted display in the Excel UI can result in confusion and errors during add-in calculations. The data types APIs are a solution to this gap.

Data types expand Excel JavaScript API support beyond the four original data types (string, number, boolean, and error) to include web images, formatted number values, entities, arrays within entities, and improved error data types as flexible data structures. These types, which power many linked data types experiences, allow for precision and simplicity during add-in calculations and extend the potential of Excel add-ins beyond a 2-dimensional grid.

To learn how to use data types APIs, start with the Excel data types core concepts article.

Note

To start experimenting with data types right away, install Script Lab in Excel and check out the Data types section in our Samples library. You can also explore the Script Lab samples in our OfficeDev/office-js-snippets repository.

Data types and custom functions

Data types enhance the power of custom functions. Custom functions accept data types as both inputs to custom functions and outputs of custom functions, and custom functions use the same JSON schema for data types as the Excel JavaScript API. This data types JSON schema is maintained as custom functions calculate and evaluate. To learn more about integrating data types with your custom functions, see Custom functions and data types.

See also