Data wrangling underpins most jobs today, and Excel is the basis for pretty much all data work. Wrongly claiming proficiency in Excel will now get you fired. You’ll likely be exposed. Fortunately, there are some of the best AI tools on the market available to you.
Analysts and data scientists are mission-critical, but I’m largely a exporter of data from our various tools – putting that into Excel to report on multiple channels of marketing performance.
I can do more complicated things in Excel now because I use the program so much, but it can still be time-consuming and tricky.
With these developments all coming to roost in the warm atmosphere of our relentless digital economy, I wanted to see if all the recent advances in AI could make shorter work of those hour upon hour spent in spreadsheets, and just how much time they could save.
So I looked around for some of the best Robo-waterproofing tools for Excel, did the tests, and share my results below.
How Tested the Best AI Tools and Plugins for Excel
Below is an instruction that describes a task, paired with an input that provides further context. Write a response that appropriately completes the request.
God knows you can do a lot in Excel. I used everything from simple formulas to the more advanced macros. I played around with the tools and plugins for:
Accuracy: I’m working with pretty big data sets, as I’m sure you are. Those [formulas] can get extremely complex, especially when you’re working with complex data sets, so it better be pretty darn good with numbers.
1. Ease of Use: Nothing kills a useful tool faster than confusion. And Excel can, let’s be honest, be fairly confusing in itself, so clear UX and UI is a given.
Speed and reliability: we have to go to AI because it makes us faster. If I am going to make the investment of time and money into a tool, then that tool has to be quick and it has to work.
Richness of Features: AI can do a lot in Excel. The richer the set of features that a single tool has, the better.
To see how the tools would perform, I downloaded a sample spreadsheet of office supplies sales data that would be fed to every tool so that any differences in the answers were not a result of different input.
The Best AI Tools and Plugins for Excel
1. Ajelix
Pricing: Free plans are available. Plans start at $9 per month. Business plans cost $40 per month, and business plus cost $200 per month.
But here I spend most of my time, working in spreadsheets in Excel and Google Sheets, but without having to memorise arcane formulae and functions; Ajelix remembers.
After less than two minutes in Ajelix, I could begin to type what I was hoping to do with my data – and the tool returned to me multiple formula options to try.
The first thing I did was open the tool and get a really nice user-friendly dashboard. Since I usually mess up formulas, I hit the Generate Formula button as the first action.
In this sample sheet, it would be handy to know how many pencils were sold, so for my simple request line, I wrote the Ajelix tool the essence of what I wanted to do: ‘Sum the number of units in Column E when the value of Column D is pencils:’’
At the top of the screen, Ajelix returned a formula almost instantaneously and included a convenient little button that let me copy it. At the bottom, just in case the first formula didn’t work but the information was still being produced, Ajelix gave me an alternative.
Then, I spotted the “Chat with AI” button and decided to try it out.
The button took me to a chat window with all the details of my formula already loaded. I asked the AI assistant to expand the formula I already had to include the number of desks sold, and the formula was immediately delivered.
The formula was spot on. But to push the AI just a little further, I told the tool that the formula did not work. Immediately, I got a second formula.
What I liked: Ajelix is simple enough for the average business user, not a data expert. You can then use the business intelligence (BI) functionality for more advanced data visualisation, if you need to go further than the AI assisted guidance.
Bear in mind, of course, that extended use of Ajelix comes at a price (for the AI functionality) – but pricing is reasonable at $6 a month for the Standard plan, or $20 a month for a plan geared towards business.
2. GPTExcel
Pricing: Free plans are available. Paid plans cost $6 per month.
GPTExcel is another ‘AI DIY’ tool suitable for anyone who wishes to improve their Excel proficiency in the most cost-effective way possible.
It includes a formula generator, but I also wanted to try out Scripts. With the Script facility, you can tell GPTExcel what you want your script to do, or you can reverse this process by giving GPTExcel a script and having the tool explain it to you.
So let’s imagine that I wanted to create automation in my sample spreadsheet where, whenever the Sales value in Column G exceeds $200, the row would be highlighted.
Here’s what I gave GPTExcel and what it provided for me:
I tried it against my Excel sheet with the script, and it worked.
After this, I opened the VBA (Visual Basic for Applications — a Microsoft programming language) editor in Excel and ran the macro:
The first time I ran it, it gave an error but it was my fault.
I just gave GPTExcel the wrong name for the sheet that I wanted to refresh by calling it SalesOrders but it thought it was still called the default name of Sheet 1. So I had to tweak the script for it to run.
However, once it did, the script was no more than basic and had also put some of the diagram headers in the sheet to the highlight category.
If you can use Excel, you’ll quickly figure out all the problems with the output, but if you don’t know it, you probably won’t know what to do. Another thing shared with most generative AI: you have to say exactly what you want, the first time, or you won’t get it.
Likes: The system is ridiculously straightforward to use. I like having the pop-up checklist with dos and don’ts to avoid common mistakes; this is a nice feature I haven’t seen offered elsewhere. The price is ridiculously low: $6.99 a month (compared with four queries per day for free, 100 for $6.99 per month).
This is an excellent tool for pounding out macros very rapidly, and presumably SQL whizzes will find that useful too.
3. Formularizer
Pricing:
Free plans are available, offering 100 usage credits per month.
At one point in my career, I had an entire word-processing Macro document consisting of thousands of template formulas to copy and paste into Excel – each time you had to drill down and customise the cell ranges, values, etc so that you would enter correct equations into your spreadsheet.
Now I’d see if I could perhaps work faster with a text prompt too, by using Formularizer.
(And to be perfectly frank, I sometimes come across a formula in my own worksheet, don’t know what it does or why I put it there, and I am too lazy to go back to find out so I leave it be.)
Formulariser is a program that takes in a formula and produces a human-sounding explanation of what it means.
I keyed in the prompt to get the formula I needed and used vernacular like ‘Column E’ because I couldn’t upload a spreadsheet to allow the tool to analyse. This feature is in Beta.
Here’s what I provided:
Make a formula that tallies up everything in Column E based on the numbers in Column B.
I wanted to estimate units sold in the “East” region from the information in my sample. Here is what Formularizer provided:
This is what I pasted into Excel, which worked perfectly: =SUMIF(B:B, “East”, E:E).
Now, I wanted to see if Formularizer could explain a formula to me. I gave it this formula:
“=INDEX(C:C, MATCH(MAX(SUMIF(C:C, C:C, G:G)), SUMIF(C:C, C:C, G:G), 0)).” The formula shows me the rep with the highest number of sales and returns that person’s name. I pasted it into the explainer tool in Formularizer.
It did a great job. It had no context to go on. I was really happy with the abstract.
The good: Formularizer works uncommonly quickly and is easily the most usable of the three options I tried, thanks to its chat-like interface. If you know your data well, the free version will see you through. For basic formulas, like the one above, it’s all you need. Really. (No, seriously.) But only the paid version will open a spreadsheet to analyse it.
4. Promptloop
Pricing:
There is a free plan. An individual plan is $49 per month. For a company plan pricing, please contact us.
Behind Promotloop are AI models that can handle a few of Excel’s more common drudge tasks. The tool will read your text data to draw conclusions about which category it best fits, answer questions about specific information from the data you provide, and generate dummy data so you can test your sheet.
The first thing I was asked when I logged in was what I would be doing there.
I said I needed help cleaning some sales data, although I could have omitted that step and gone straight to the dashboard. (Promptloop’s key functionality at the time was data cleaning.)
My data was capped at 25 rows because I was on the free plan, and I had used that by attaching my Excel workbook.
For the follow-up question, I described how to clean the data: “I want to put them into product types in column D. I want to put Pencil, Pen Set, and Binder as Small. And I want to put Desk as large.
Promptloop then gave me an opportunity to clarify the columns and the expected output.
It then did the work in a matter of seconds, and gave me an output file to download.
Everything worked perfectly: hit ‘Download’, then select the data and the sheet. After the first few steps, another click was needed (sometimes two).
What I like: Promptloop if you have a lot of text you need to sort through. Survey responses are an example where Promptloop would be really helpful, especially if a survey had an open-ended free-form text field instead of selection options.
Having said that, I didn’t entirely like using this tool over others on this list: it had some gratuitous steps and clicks along the way.
5. ChatGPT
Then come GPT.4, ChatGPT can be faster than ever. I’d been frustrated by ChatGPT’s earlier incarnations; its rigid rules around training a spreadsheet to pose a prompt.
First, I asked ChatGPT what it could do if I gave it a spreadsheet to work with. It came back with answers, including data analysis, cleaning, visualization, transformation, formulas, and automation. So, I put it to the test.
First, I had it create a pivot table, providing no other instruction.
Here is what it provided to me, which I was able to download as a CSV file:
Next, I asked it to visualize the data in the pivot table. It did a pretty great job delivering a color-coded bar graph:
Finally, I asked it to help me with a macro using the following prompt:
Subject: Re: SALES BY REPRESENTATIVE: June 2009 Can you give me a macro I could run from this data in Excel for Mac that keeps track of the total units sold by each rep for me?
Its response was a script that promised to ‘Generate [sic] a summary of the total units sold by each representative and output the result to a new sheet named “Summary”.If you run this macro once every day, week or month, it will update the totals accordingly.’
I gave it a try and encountered a glitch, but then I took it back to Chat-GPT and notified it which significant line is lined up in the Debug Machine. Instantly, it gave me a new output that worked like a charm.
What I like: Since ChatGPT has such fluent language capabilities, it would now be possible, even for a person without any experience in using Excel, to quickly complete tasks for the tool.
With this level of strength, though, you will need the full paid version of the software, available in the UK for £20 a month – one of the simplest tasks on the list.
6. Formula Bot
It crunches massive amounts of data and does simple work in Excel. For most of its features, Formula Bot needs to be installed as an add-on to either Excel or Google Sheets in order to use it, but some of its simpler, generative AI features are available via the browser app.
First, I let the ‘Free Analysis’ flag have a go with no instruction whatsoever.
The first thing I needed to do is enter data. I could import an Excel file or select an interesting connector such as Google Sheets or Facebook Ads:
Once I’d loaded my spreadsheet, the only module that made sense was ‘Start Chat’.
I asked the chat to give me “Which region has the highest sales?” from my sample sheet. Disappointingly, I was given a CSV file to download with the answer, and it had just grouped the unit data into regions without giving me a direct answer.
I decided to try one of the other AI features to see if I fared any better.
This time, I wanted to upload a PDF and convert it to an Excel spreadsheet.
I uploaded a PDF containing data to see how well it would translate to an Excel sheet.
(Note: Formula Bot also allows you to paste text to be turned into a spreadsheet, which is handy.)
Formula Bot asks you to select a couple of options after uploading your PDF. It also recommends that you don’t try to turn non-table data from the file into Excel data or try to combine multiple tables into one sheet in your workbook.
Converting the PDF file only took a couple of seconds and the spreadsheet was ready for download right from the same screen.
When I downloaded and opened the Excel file, the data was perfect and ready for use.
What I like: The interface of Formula Bot is and the tool is relatively straightforward to use. You don’t need to sift through dozens of tutorials to figure out how the platform works. However, it did feel a little glitchy here and there.
The PDF to Excel feature was impressive, and with the paid add-on ($9-13 per month) right within your Excel workbooks, some of the advanced features like sentiment analysis or text classification would be interesting to see.
Making the Most of Excel
And my absolute favourites were: For data visualisation, go with ChatGPT (I’m still amazed by this, but I also know that I’m way behind the curve, and that almost any marketer can say the same. If you don’t want to use another system, just use the one you already know.)
But if you need a rather serious program to teach excel, your choice must be Ajelix – the only program I tried that was good at creating formulas I don’t remember by heart. More impressively, the AI chatbot was quite proficient in replying to my questions.
And if Excel forms even a small part of your working life, it’s high time you checked them out: they’ll save you time, and might even fill in some of the gaps in your knowledge.
