• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

AI Aided Excel Formula Editor

You are here: Home / Excel Formulas / AI Aided Excel Formula Editor
ai-aided excel formula editor
February 23, 2023 by Mynda Treacy

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


Subscribe YouTube

AI-aided Formula Editor

The editor is free to download from the add-ins store on the Insert tab:

get ai formula editor add in


Search for the add-in:

search for the ai formula editor add in


It adds a tab to the ribbon where you can launch the formula editor:


ai aided formula editor in ribbon


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.

ai aided formula editor window pane

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:


sample data in table


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:


describe the formula you want


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:


write the formula to 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:


explanation of formula created by ai formula editor


Alternatively, you can click the comment icon and open the console at the bottom of the pane to see the result and formula explanation:


click icon to see explanation of formula created by ai formula editor


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:


write your own formula in ai formula editor

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:


ai aided formula editor explains existing formula


Expand and collapse buttons enable you to focus on a specific part of a larger formula:


Expand and collapse buttons focus on part of 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.:


Pin a formula to the explain a formula window


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:


visual indication that formula can be improved


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:


old formula to split a text string


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):


tell ai aided formula editor to use excel textsplit function


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.

ai-aided excel formula editor

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.


Category: Excel Formulas
Previous Post:chatgpt for excelChatGPT for Excel
Next Post:Picture Fill Excel Chartspicture fill excel charts

Reader Interactions

Comments

  1. Stafford Johnson

    February 25, 2023 at 5:35 am

    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?

    Reply
    • Mynda Treacy

      February 25, 2023 at 10:13 am

      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

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.