Microsoft Excel: How these new features point the way to the future

Microsoft's spreadsheet finally gets an easy way to add custom code, in the shape of code that can run in the cloud.

3 steps to take before creating a timesheet in Microsoft Excel

Part of the promise of Microsoft 365 is that it's a platform that extends from device to cloud and back again, bringing together Windows, its development tooling, Office 365, and Azure, as well as management and security tools. That's a big promise, as it requires Microsoft to deliver on several fronts at the same time, using technologies like the Microsoft Graph to bridge the divide between the various facets of this new, expanded platform -- as well as extending existing desktop tools to support cloud integrations.

One key piece of the new puzzle is an old favourite, Excel. It's long been a popular tool for business analysis, with support for complex queries on large data sources, along with advanced visualisation tooling. It's also on most PCs, ready for anyone to pick up and use. While Excel has been able to connect to data sources as part of a client-server application, it's never really had the ability to take advantage of any remote processing capabilities beyond using Visual Studio Tools for Office to add COM endpoints, falling back to its analytic and calculation roots.

SEE: 10 Excel time-savers you might not know about (TechRepublic)

That's all changed with the addition of custom functions, which extend Excel's built-in programming model. Announced at Build 2018, they're a new way of extending Excel using JavaScript. Once installed, a custom function is called the same way you'd call any Excel function, simply adding it to a cell. Using Excel content as inputs, you can use the function to perform complex mathematical or statistical operations. Because custom functions are JavaScript, unlike older Excel extension options, they can run on Windows, on Mac and in Excel Online. You'll find support in the current release of the Office ProPlus; it's unlikely we'll see support in the non-subscription boxed client until Office 2019 ships.

Building and using custom functions

Creating a custom function is easy enough. JavaScript function code can be built in any editor, with a JSON description to handle Excel integration. You can have multiple functions in a single file, making it easier to deploy a library of business-specific functions across an organisation. One important note: custom functions need to be part of a namespace, to ensure there's no clash between similarly-named functions from different sources. This is used when you call the function, so for example, ISPRIME, a function that's in the TECHREPUBLIC namespace that looks for prime numbers would be called by putting =TECHREPUBLIC.ISPRIME into a cell and then adding a reference to the cell you're analysing.

The JSON description is best thought of as metadata for a custom function. It links Excel to your code, indicating where to find help, how to autocomplete the function, as well as offering custom options. Most importantly it also defines the parameters that are sent to the function, and the type of results returned -- a string, a number, a Boolean, or even an array. It's accompanied by an XML manifest that indicates where all the files that are associated with a custom function, including a hidden HTML page that hosts the function JavaScript.


Custom functions: This easy-to-use approach extends Excel's built-in programming model

Source: Microsoft

Functions can run locally or remotely, with local functions running synchronously and remote requiring JavaScript promises and call-backs to handle asynchronous operations. You can use them to call out to Azure, or even to non-Microsoft web services. Cells will display a GETTING_DATA message while the function waits for a call-back from your remote code, which allows you to continue working if the response takes seconds or even minutes to be delivered.

One useful option is the ability to stream results to a cell automatically, allowing contents to update and trigger recalculations. You'll need to make any streaming function cancellable, but while its running it'll give you a way of monitoring IoT data or displaying dynamic information from a pricing service or an e-commerce platform. Instead of being the place you put data to be analysed, an Excel spreadsheet hooked up to a streaming custom function is a window into a business process, ready to be used as a customisable business dashboard; especially in conjunction with built-in and custom visualisations.

It's even possible to use custom functions to add machine learning to an Excel spreadsheet.

Excel goes AI

Much of what's needed to build and deploy machine learning services is built into the Azure Machine Learning platform. It's where you build and train machine learning models, using either a cloud-hosted workbench or the AI tooling available for Visual Studio. There's also the option of working with Microsoft's implementation of the R statistical programming environment or taking advantage of the recently inked partnership with the Anaconda scientific computing distribution of Python.

Once implemented, Azure ML models are simple enough to use, exposed through familiar REST APIs that can be called from JavaScript applications. Hosting an Excel custom function in an Azure web application gives you quick access to a ML model, connecting your on-premises spreadsheet directly to Azure services.

Integrating Excel with a ML service lets you go beyond its built-in analytic features. Models can be used for predictive analysis, for data classification, for text analysis, or for error detection. Combined with tools like Flow, you can use Excel as the hub for a basic workflow that takes data for, say, a specific Twitter hashtag, stores it in Excel, and then applies a ML model to run a sentiment analysis, writing the results into the Excel workbook ready for later analysis in PowerBI. If you don't want to spend the time building your own ML models, Microsoft's Cognitive Services offer pre-trained ML models that can quickly extend a custom function, including tools for custom image recognition.


Taking advantage of Flow: Users can create, manage, and most importantly run automated workflows on Excel tabular data


With Microsoft now starting to offer support for FPGA-based machine learning via its Azure Brainwave accelerators, Excel analytics will soon be able to take advantage of much faster ML models. It's an approach that should give you access to more complex models that otherwise would take time to run, even using Azure. It's easy to imagine using techniques like this to add fraud detection to an accounting dashboard, or advanced predictive maintenance models to an IoT service.

The shape of Excel to come

Excel's custom functions go a long way towards giving you the ability to deeply integrate your spreadsheets into your business processes, either as a way of consuming data or adding advanced analytics capabilities. With the ability to reach out into the cloud for services, including machine learning, this new hybrid Excel is starting to show the shape of what Microsoft is planning for its productivity tools over the next few years.

More on Microsoft