Microsoft's spreadsheet finally gets an easy way to add custom code, in the shape of code that can run in the cloud.
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)
Building and using custom functions
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.
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.
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
- How to control spacing and alignment in a numbered list in Microsoft Word (TechRepublic)
- How to use Excel's Data Model to turn related data into meaningful information (TechRepublic)
- 5 ways to modify and customize styles in Microsoft Word (TechRepublic)
- How to use COUNTIFS() to compare multiple data sets (TechRepublic)
- Office Q&A: An easy Word Replace trick for a big problem and exposing duplicate records in Excel (TechRepublic)