Microsoft wants to take Excel beyond numbers and text

Microsoft has updated Excel to allow developers to programmatically create their own custom data types in the popular spreadsheet program. 

This update isn’t aimed at Excel-using business execs but developers who need to wrangle metadata in Excel cells. Excel historically been about organizing text and numbers but Microsoft has, for several years, been working to boost the number of data types the application supports.  

At last year’s Ignite 2020 conference, it introduced both dynamic arrays and array formulas and support for Stocks and Geography data types via Bing. Ignite’s on again this week, and Microsoft has furthered this goal with more data types that also power Wolfram search. Microsoft in August announced additional Wolfram data types for Excel. 

The gist of it is that linked data types connect users in Excel to other data from Bing, Wolfram, Power BI and other sources, covering currencies, geography, organizations, locations, cities, universities, space, people, space, animals and so on. Many of them are powered by Wolfram, the science-focused search engine. 

Microsoft for its part wants to show the world that Excel is far more than text and numbers and comes as several software vendors push the idea of low-code or no-code app development, which allow non-developers to create mobile and desktop applications for businesses. Excel is really important for Microsoft’s no-code story. 

“Countless solutions have been built with Excel. Many of these solutions are powered by cells which most often contain (or evaluate to) just text or numbers,” explains Chris Gross, a program manager at Microsoft. 

“With the addition of data types, Excel has evolved to a world where cells can contain something much richer. 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 have seen introduced in the product today,” says Gross. 

Microsoft has introduced several new standard JavaScript interfaces (APIs) that allow developers to use more data types. This should make life easier for developers. 

It should reduce the need for developers to build custom fixes for add-ins they need to today.

The first API is called Range.valuesAsJson, which allows developers to read and write data types and should be familiar to those who use the Range.values API, 

The new API “can return augmented information about basic types (text, numbers, errors) as well as information about the new data types we have introduced,” says Gross.  

It also supports entity values, formatted number values, web images, and arrays. They can be used as inputs and outputs. 

“We are building this API with the world of services in mind. We want to make it easy for you as a developer to extend any service and easily expose your data to Excel through your add-in. It should be as simple as adopting our schema and passing the schematized values for display and re-use within Excel,” notes Gross. 

It’s currently available in the latest version of the insiders build of Excel for Windows and macOS with the latest JavaScript APIs.