In this third article, in our series about using AI in Excel, we’re going to look at the best third party Artificial Intelligence Add-Ins for Excel that bring the power of AI technology to your spreadsheets. In the past year or so, there has been a great deal of interest in using AI more generally in the workplace. As one of the most commonly used workplace tools, it makes sense that there is a big focus on bringing AI into Excel. In our previous articles, we’ve looked at how Microsoft uses AI technology to varying degrees in Excel features and functionality, as well as a new experimental function that uses OpenAIs generative AI. In this article, we’re going to look at third party Artificial Intelligence add-ins for Excel which fill gaps in terms of AI technologies that Microsoft has not covered (yet).
Can you really use Artificial Intelligence in Excel?
We should state up front that the features we’ve mentioned in previous articles (such as data extraction and forecasting) use AI-type technologies such as machine learning. But they don’t use the more complex AI techniques such as expert systems. We’re therefore using the term Artificial Intelligence (AI) to refer to automated tasks that a machine can perform that mimic those that a human can do. A lot of these tasks, like data cleaning, data extraction and modelling help us be more efficient with our time. Thus, provide consistency in how tasks are performed.
A good example is the data extraction feature Insert Data from Picture in the Get & Transform Data menu, or the transformation functions in Power Query that help clean data. In a previous article, we discussed these built-in Excel AI Tools and others. In this article, we’re going to look at third party Artificial Intelligence Add-Ins for Excel that use AI-type technology or similar complex processes to help us do things quicker and better in Excel.
Features of third party Artificial Intelligence add-ins for Excel
There are a lot of third party AI add-ins for Excel out there. Many of them have similar features, so it’s a good idea to look at the features that are included in these tools to make comparisons easier.
Generate formulas
This is the most common way that AI technology is used in Excel add-ins. It allows you to generate a formula from a prompt that you provide. It basically means that you can make use of Excel’s powerful set of formulas without knowing what the formula is, or what syntax to use.
Here is an example that uses the Generate Formula feature from Formula Bot (formerly Excel Formula Bot). I typed the following instructions to generate a formula:
“Give me a formula that adds the number of work days in c2 to the date in b2”
Formula Bot then responded with the following suggestion, which I then copied into the cell in the worksheet:
=WORKDAY(B2,C2)
Explain formulas
Just as most of the Excel AI tools have a generate formula feature, so they also have a feature that does the reverse – explains in plain English how a formula that you provide to it works. This is useful when you are using a spreadsheet that contains formulas that you are not familiar with, or you have copied a formula from another spreadsheet but you’re not sure exactly how it works.
Here is an example where I asked for an explanation of the XLOOKUP formula in a table. Note that I have pasted the answer given by Formula Bot into a text box in the sheet so that the content of the answer is visible.
Generate VBA Code
With this feature you can enter your requirements in plain English and the VBA code will be generated. You can then copy and paste this code into the VBA editor in Excel and run it.
I ran a test using AI Excel Bot to create a loan repayment schedule and it worked pretty well. Here’s the requirement I entered in the AI Excel Bot interface:
“Please write VBA code to populate an excel file with a monthly loan repayment schedule using the following parameters: loan amount $50,000, annual interest rate 5%, repayment period 2 years. Write the parameters at the top of the sheet”
Here is the code that was generated:
And here are the results that were written into Excel when I ran the code:
Freeform ChatGPT queries
It’s useful to have the freeform nature of ChatGPT in a structured environment like Excel.
In the example below, I used the Freeform function in the Formula Bot add-in to generate thank-you messages for a list of people that made donations.
=FORMULABOT.FREEFORM(“Write short thank you message to “, A2, “for making a donation to our cause”, B2)
Search internet and return structured information/data
This feature provides the capability to perform a normal web search and return the results to your spreadsheet. A good use case for this is research-type tasks, where you need to populate information about a subject into a table.
I tried this out using the Browser function in the PromptLoop Excel Add-In. The function has two inputs: the target (i.e. the thing you want to get information about) and the header (the actual information that you want returned).
In this example, I used it to do some research on some venture capital companies. The formula takes a target (in this example it is the name of the company in column c) and a header, (in this example, it is the table headers, which are “Pitchbook Description”, Number of Employees, “Industry” “Website”)
=PROMPTLOOP.BROWSER(C7, E$6)
The screenshot below shows the results:
Categorization and labelling
This is a useful feature when you need to categorize or label text-based entries. The idea is to analyze some text input and match it to another text value that is semantically similar.
In the example below, I used the Label function in the PromptLoop Add-In to assign a Type value to a list of pizza descriptions. The results were a bit hit and miss, as you can see!
You could also use this function to perform sentiment analysis by analyzing tweets, for example, relating to the performance of a company and stating if they are positive, negative or neutral.
Extract specific information from text
Converting unstructured data into structured data is a common task when working in Excel. Often this means extracting some meaningful text from some messy source data.
In the example below, I used the Label function in the PromptLoop Add-In to work out which company a person works at based on their email address.
Generate dummy data
This is a feature that takes a set of example data and returns replicated content that can be used as dummy data.
In the example below, I used Replicate function in the PromptLoop Add-In to generate a list of random name. This function works by using some examples as guidance for the AI language model to provide random similar text strings. This can be helpful for making mock data or testing inputs to models or other workflows.
Data analysis using natural language
Performing data analysis by simply asking questions in plain language is an area where AI can really shine. Microsoft already has a built-in feature in Excel called Analyze Data, but when I have used it in the past it has been a bit hit and miss. There are some third-party tools that can perform similar analysis of Excel data using natural language. In the examples below, I used the Data Analysis tool from Superluminal.
With Superluminal you create a project and then upload your data (Excel file), or specify a location such as One Drive, Google Drive, DropBox, etc.
Here was my sample data:
Once you’ve uploaded your data to Superluminal, you can use the prompt to ask questions about the data, or ask for transformations to be performed, or charts to be generated.
In the first example, I asked Superluminal to add a column called Region to the right of the Country column and categorize each row using these values: Sub-Saharan Africa, Europe, Middle East and North Africa, Asia, Central America and the Caribbean, Australia and Oceania, North America.
The new column was added and populated with the correct data, but it was given a heading of ‘Categories’. So I asked to rename it, and also moved between Order ID and Country.
Next I asked for a summary table showing Total Revenue by Region. Note that the AI engine had to work out that revenue has to be calculated first from the ‘Units Sold’ and ‘Unit Price’ columns before summing and grouping.
Finally, I asked Superluminal to add 2 new columns; the first the Year and the second the Month, both extracted from the Order Date column. Anyone that’s worked with messy data knows how troublesome it can be working with dates in Excel. In the sample data I uploaded, for example, the ‘Order Date’ column was actually a text value, not a date.
When you have finished performing your data analysis and transformations, you can download the new dataset to CSV/Excel.
Conclusion
There are over a dozen or so third party Artificial Intelligence Add-Ins for Excel that use the power of AI in Excel. These add-ins extend the capabilities of Excel using AI-type technology. Many of these tools have similar features such as the following:
- Generate and explain Excel formulas in plain language.
- Generate VBA code based on natural language input.
- Use of ChatGPT in either freeform ways such as generating text, or more structured ways, such as classification and labeling of data.
- Extracting structured data from unstructured data.
- Data analysis using natural language prompts to interrogate data or perform transformations.
Al of the tools that I looked at, require you to sign up to a plan (those that offer a free tier have very limited caps in terms of number of queries you can run against the AI model). The cost effectiveness of using these add-ins therefore depends very much on your individual use case.
It’s likely that more features will be added as the AI models develop over time. It’s also probably the case that Microsoft will embed more AI in Excel to enhance some of the existing features, and also to add new ones.
