Watch on YouTube
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 the 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:
=TEXTSPLIT(A1,",")
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.
Check Out Our Courses
