Love it or hate it, Excel is used the world over for everything from quickly adding a couple of numbers together to accidentally losing tens of thousands of COVID-19 cases in the UK. But if you’ve ever had to use Excel for anything beyond INDEX MATCH (or *shudder* VLOOKUP), you’ve probably found yourself nonstop Googling only to find out Excel isn’t really built for what you’re doing.

But what if you’re ready to go beyond the basics? What if you want to do actual “data science”? Is it time to abandon Excel and get up to your elbows in lists, loops and package managers?

Not necessarily! In this post, we’ll look at how you can get Excel performing all your data science tasks with just one free add-on for Windows—Wolfram CloudConnector for Excel.

What Is Wolfram CloudConnector for Excel?

Wolfram CloudConnector for Excel can transform Excel into a data science powerhouse by injecting Wolfram Language functions straight into your spreadsheets. It does this by providing a connection between Excel and Wolfram Language, extending the computational power of your spreadsheets.

With CloudConnector, there’s no end to what you can achieve with your spreadsheets, including executing high-level computations (machine learning, algorithms, …), importing hundreds of data types, analyzing data with natural language (harnessing the power of Wolfram|Alpha), creating rich visualizations, linking across several areas of research (chemistry, geography, cryptography, …) and much more.

What Can You Do with CloudConnector?

Pretty much anything you can think of! Want to use Wolfram’s machine learning capabilities in your spreadsheet? You can do that with a single formula. Want to create advanced visualizations like geo histograms or contour plots with extensive formatting options? That’s easy too!

Whether you’re calling a single Wolfram function or want to easily add complex computations to your Excel workbooks, CloudConnector has you covered. But rather than read about it, you can see just how easy it is to create geo visualizations that are simply not possible in Excel alone with help of CloudConnector.

Want to give it a go yourself? Just follow these simple steps to get started. All you need is Excel and a free Wolfram Cloud account!

Getting Started with CloudConnector

Step 1: Installing the Plugin

Getting CloudConnector is easy: just download the Excel plugin, restart Excel and you’re good to go. To confirm it has worked, you should see a Wolfram tab in Excel:

Engage with the code in this post by downloading the Wolfram Notebook

&#10005

Step 2: Sign In to the Wolfram Cloud from Excel

From Excel, navigate to the Wolfram tab and select the Signed Out button to bring up the Wolfram Cloud dialog:

&#10005

If you do not have a Wolfram ID, you can obtain one for free by creating an account—all you need to do is provide a valid email address.

Step 3: Call Your First Function

The quickest and easiest way to get started with Wolfram Language in Excel is to call a single expression.

In the formula bar, use the Wolfram function in Excel to call the Wolfram Language function RandomInteger:

= Wolfram (“RandomInteger”, B2, B3)

&#10005

Let’s take a quick look at what’s happening here.

The first argument in the Wolfram function is the name of the Wolfram Language function you want to use, in this case RandomInteger.

The following arguments pass parameters to the Wolfram Language function, in this case the contents of cells B2 and B3 (100 and 10, respectively).

&#10005

When we press Enter on the formula, CloudConnector will then create a list of 10 random integers between 0 and 100, each with its own cell.

That already gives you access to over six thousand functions! You can stop here and just use singular functions while still being able to cover everything from machine learning–based sentiment analysis with the Classify function to creating charts and graphs not normally available in Excel with functions like GeoHistogram.

Step 4: Call a Wolfram API

Although Wolfram Language has uniquely capable functions and superfunctions that produce powerful outputs by themselves, by combining them, you can create even more impressive, customized workflows. We’ll go over how to make these functions in a future post; for now, we’ll look at calling Wolfram APIs from within Excel.

For this article, we’ll use a very basic Wolfram API called myAPI, which takes a single parameter (a number) and squares it. Here’s how you use it in Excel.

First, select the cell where you want the output of the API to appear (in this case, the cell B2):

&#10005

Go to the Wolfram tab and click Insert API Call:

&#10005

This will bring up an Insert API Call dialog. Fill in the following fields:

API Name — This is the location and name of the API you would like to connect to. You will be provided this URL when you create a Wolfram API. To use the one in this example, just insert /events-europe/myAPI in this field.

Output Location — This field was filled in automatically by Excel; it’s just the full name of the cell chosen earlier (B2).

Parameters — These are similar to the second and third arguments we used earlier, except you specify them when creating the Wolfram API.

This API has only one parameter (arg). You can provide a cell reference (in this case B1) or manually type in a value:

&#10005

Click Insert, and that’s it! The API will run and you will get a result in the output location. If you used a cell reference in your parameter, the API will rerun every time you change that cell—just like a normal Excel function:

&#10005

OK, but What about the Data Science?

So far, we’ve created a random number generator and a number squarer. If that seems like something you could already do in Excel, you would be exactly right—that’s what the RANDARRAY and POWER functions are for!

But does Excel have functions for intelligently interpreting dates, no matter the format? Or for semantic text analysis? Or for time series forecasts with machine learning?

Well let’s take a look at creating our own Wolfram Language function that can intelligently clean and interpret dates far more powerfully than Excel’s built-in tools.

APIs: Unleash the Power of CloudConnector for Excel

Now, we’re going to start combining functions in the Wolfram Cloud to start building our own APIs and unlock a whole range of capabilities not normally available in Excel.

Getting Started with Wolfram Cloud Notebooks

Wolfram Notebooks are the ideal environment for exploring ideas and building workflows across any domain—from data science and modeling to research and education—with interactive coding, natural language queries and expansive documentation. If you can write an Excel formula, you’ll be able to get started with notebooks in no time at all!

The first step to building your own API is to head to www.wolframcloud.com, set up an account (if you haven’t already) and create a new cloud notebook:

&#10005

Give your new file a sensible name by clicking the “(unnamed)” text:

&#10005

If you try clicking somewhere in the body of the notebook (the large blank area), the first thing you might notice is that your mouse and text cursor are both horizontal. This is because notebooks are made up of rectangular “cells,” and you haven’t yet made a cell in which to type code. You can create a new cell by clicking the “+” (or by typing something):

&#10005

Now that we’re all set up, we can start building our first API!

Building Our First API

Building and deploying an API in a cloud notebook is as easy as filling in a simple template:

&#10005

Let’s have a look at what’s happening here, from the inside out:

Function — This is where we’ll define what we want our API to do. This can be as simple as a single function or an entire program comprised of multiple functions.

APIFunction — This is where we define the parameters we’ll pass from Excel into the the function we’ve created.

CloudDeploy — This is where we define our API’s name. This also puts (or “deploys”) the created function and turns it into an API stored in my cloud files, where it can be called by Excel.

There are also a couple of extra bits in there that make sure the API we’ve created is accessible by CloudConnector for Excel. Now let’s add in our functions and parameters to create an intelligent date interpreter.

Creating an Intelligent Date Interpreter

If you’ve ever had to Google “Why wont Excel understand my dates?!”, you’re definitely not alone! So, in our first API, we’re going to use Wolfram’s intelligent interpretation functionality to convert whatever type of date we have into a format Excel will immediately understand.

Creating the interpreter is incredibly easy: all we have to do is call the Interpreter function, tell it to generate a date and give it the text we want it to interpret, like so:

&#10005

You’ll notice that instead of passing actual text for it to interpret, we instead have [#date], which tells our function that there will be a parameter called date that it will get its data from—more information on that in a bit!

Next, we want to add some code around that to covert it into an Excel date serial number, which just involves some simple arithmetic, then put it into the Function part of our API template:

&#10005

Next, we need to define some parameters. We’re going to use just the one parameter date that we added to our function earlier. This parameter is of the "String" type, as it will be passed text from Excel. Since the parameter will refer to a column of data (as opposed to a single cell), we need to indicate by wrapping the "String" type with RepeatingElement[]. Then we can pass that into our template:

&#10005

Finally, we just need to name our API. In this case, we’ll call it "interpretDate" and deploy it to the cloud, which is as simple as pressing Shift + Enter:

&#10005

This produces a link to where our API has been stored. We’ll need the latter part of this link that has the account ID and API name, so “/events-europe/interpretData” in this instance, when we call it from Excel. You can find all of your personal APIs here:

&#10005

&#10005

Calling Our API in Excel

We covered how to call a Wolfram API in Excel using CloudConnector’s interface earlier in this post. But if you have CloudConnector installed, you can also call your API directly from Excel’s formula field like so:

=WolframAPI(“/events-europe/interpretDate”, Parameter(“date”, ))

Our API then takes the date text in column A and converts it into Excel format in column B:

&#10005

Now we have a function that can take a date in pretty much any format, including natural language, and reliably convert that into something that Excel can understand and use consistently.

What Else Can You Do?

Well, that’s limited only by your imagination! But if you’re in need of a little inspiration, you can see a range of examples you never thought you could achieve in Excel by watching this webinar.