Writing Excel formulas is one of the most important tasks to master in Excel, but it can be a mine field trying to get your head around all the functions available and knowing which one to use for the task at hand.
But now with this free AI-aided formula editor you can have it write the formulas for you from inside Excel.
It can also explain how existing formulas work and suggest improvements and tutorials to help you learn.
Watch the AI-Aided Formula Editor Video
AI-aided Formula Editor
The editor is free to download from the add-ins store on the Insert tab:
Search for the add-in:
It adds a tab to the ribbon where you can launch the formula editor:
The editor opens in a pane on the right-hand side of the window (see below).
Tip: you can make it wider or left click and drag the header area of the pane to bring it into a separate window.
Using the AI-aided Formula Generator
Using plain English, you can describe the formula you want, and Excel will return an AI-generated answer. For example, in Table1 I have some sales data:
Let’s say and I want to count the number of sales that are greater than $3000 and where they’re for the Components category. I can describe my formula in the editor, click the ‘Submit’ button (or press CTRL+ENTER), and AI writes the formula for me:
Notice how it formats it nicely so it’s easy to read.
If I want to use the formula, I can simply click the left arrow to write the formula to the selected cell:
If you want the formula explained, select the formula and wait a few seconds while the AI generates the result, which in this example is 3, shown in the box just above the explanation:
Alternatively, you can click the comment icon and open the console at the bottom of the pane to see the result and formula explanation:
Another option is to write your own formula in the ‘Write formula’ area and have the AI nicely format it for you. The downside of this is that there is no intellisense or autocomplete for the functions:
Understanding Existing Formulas
The AI-aided formula editor can also help you understand formulas. Simply select the cell containing the formula, click the ‘comment’ icon and open the Console at the bottom of the editor and it displays an explanation of what the formula is doing:
Expand and collapse buttons enable you to focus on a specific part of a larger formula:
You can pin a formula to the ‘Explain a formula’ window and it will remain there when you select a cell containing another formula on the same sheet or another sheet.
This is handy if you want to copy the formula to another cell or when troubleshooting other formulas that might be linked etc.:
Click the pin again to unpin the formula.
Improving Existing Formulas
If the formula can be simplified or improved, it will display a yellow wavy line under the relevant section. Hovering over this displays the current formula and the suggested alternative.
There are also links to tutorials, so you can learn more about the functions it suggests:
Note: Currently the suggested improvements feature is limited to a specific list of scenarios and is not available for all functions.
Limitations
The AI tool behind the formula editor is based on OpenAI Codex, which is a descendant of GPT-3 and the same AI that powers GitHub Copilot.
As such, it’s not familiar with information post 2021 and isn’t aware of some of the new Excel functions, like TEXTSPLIT etc. that were added in 2022.
For example, if you ask it to write a formula that splits text after each comma, it will return something like this:
And while this formula is correct, you have to absolute reference cell A1 and then copy it across the columns to extract each section.
However, if you have the TEXTSPLIT function in your version of Excel, then it’s far simpler:
That said, if you ask the formula editor to use TEXTSPLIT, then it can (it just won’t find it on its own):
Keep in mind that OpenAI Codex is still being developed, so currently it won’t get every answer right, and it’s not great at super advanced things like LAMBDA functions, but over time it will improve.
In the meantime, if it doesn’t give the desired answer, you can click the ‘Submit’ button again to try and get a different response.
If not, try rephrasing your question and providing more specific instructions. The clearer your question, the more likely you are to get the correct answer.
Hi,
FYI, this add in requires you to subscribe/login this gives you access to the basic subscription which is limited to 10 uses per week, for the full access you have to purchase a subscription which is currently $5.99 USD/month.
Thanks for the update, Ben. It’s a shame as it was 100% free at the time of writing.
I gave this a try. It is terrible. I set up a table with column A Fruit, Col B Quantity and column C Unit cost. I filled in the table with 4 different fruits (col A) with qty (col B) and unit cost (col C). I added a Total Cost (Col D), made it a table and set the active cell to D2.
Fruit Qty Unit Cost Total cost
Apple 10 0.5
Pear 20 0.75
Kiwi 5 1.5
Orange 25 0.55
I asked it for the “total cost of the apples and if gave me this”:
SUM(Apples!A2:A5)* Apples!B2
I submitted again and if gave me
SUM(A1:A10) * B1
So then I said “total cost of apples by multiplying unit cost times Qty” and it gave me this: = Unit Cost * Qty
This is a really simple example and if does not work at all. What’s up?
Hi Safford,
Great to see you giving it a try. The AI cannot see your spreadsheet. You have to tell it the cell references that you want it to include in the formula. The correct way to ask your question is this:
“formula to calculate cell B2 times cell C2”
However, that’s probably more simple than you’d ever ask AI to write for you. Another option would be to ask it:
“formula to calculate the product of the quantity in cells B2:B5 and the sales prices in cells C2:C5”
It should return:
=SUMPRODUCT(B2:B5,C2:C5)
I hope that clarifies things, but shout if you have any questions.
Mynda