Clueless about how to write Excel formulas? Use AI

  • Post author:
  • Post category:Uncategorized

Over the past six months, we’ve learned how AI can be used for many tasks: creating art, powering a sophisticated chatbot, and so on. But what if you could use it to actually assist you in your job?

If your job is working with Excel, you’re in luck. A free site (with registration) does just that: Excel Formulator.

Excel Formulator takes your instructions, in plain English, and turns them into an Excel formula. “Pick the highest value in column C and assign it to the cell to the right of the cell labeled “Big Spender” turns into =MAX(C:C) & “Big Spender”. Copy the latter into the formula field into an Excel cell, and you’ve saved yourself some time — or, for those who don’t have a sophisticated knowledge of Excel, you now have a quick-and-dirty solution to an irritating problem.

Last year, ExcelFormulabot.com promised a similar goal: take a plain-language prompt and turn it into a formula using AI. The site is still live, but it’s evolved: now, ExcelFormulabot only offers five free formula requests per month, while anything else costs $6.99 per month. (To be fair, the $6.99 plan offers unlimited requests, and saves previous requests as well.)

If you’ve ever used AI art, you know that, while it’s amusing, it might not totally offer the illustration you want on the first attempt. Excel Formulator does occasionally suffer from the same problem, in that you can never be quite sure what complexity it will easily parse, and what it will simply stumble over. Fortunately, Excel is set up so that false positives are (hopefully) rare: if Excel Formulator fails, you’ll simply see error messages like “#N/A” or “#REF” or “#NAME” instead within your spreadsheet. Phrasing occasionally matters, too: while I failed to get the site to look up and copy the contents of a cell whose row contained a specific word (“banana”) and whose column contained a second word, (“dog,”) I was able to make it work by using the Excel column name “D” instead.

ExcelFormulator can help you come up new formulas as well as explain what others do.

Both sites also work in reverse: if you have an existing formula in an Excel spreadsheet — that a coworker devised, for example — you can plug that formula into the site’s Explainer section and it will attempt to decode it into plain language.

My struggles highlight two lessons, however: first, sites like Excel Formulator and ExcelFormulaBot don’t necessarily highlight what AI models they’ve been trained on — and probably should, to indicate which is the most sophisticated Second, it’s entirely possible that more established sites like ExcelFormulabot will be superior, just because they’ve been used more, and presumably trained better as a result.

Incidentally, the site that solved my problem? The plain-jane and virally popular ChatGPT site, which can take text prompts and turn them into detailed solutions. The site’s so good that the founders are considering offering a professional version. ChatGPT not only solved the lookup problem above, but provided a detailed, helpful explanation along with the code:

=INDEX(A:Z,MATCH(“banana*”,A:A,0),MATCH(“dog”,1:1,0))

Mark Hachman / IDG

Professionals of all stripes may turn up their noses at AI, but for everyone else? In a pinch, it might be worth a try.

Windows