Asking AI to write a formula

How do I describe what I want?

Be specific, and name the columns. Microsoft says that naming the columns you want Copilot to analyse produces more accurate results.¹

"Work out the discount" leaves the AI guessing. "In column D, take the price in column B and take off the percentage in column C" doesn't.

A good request has three parts:

• Where the data is (sheet name, column letters or headers).
• What the answer should be (a number, a percentage, a yes or no, a piece of text).
• Where it should go (one cell, a new column, a total row).

What do the makers' own examples look like?

They're short and plain. Google's example is "Create a formula that divides goals by games."²

Microsoft's single-cell examples include working out the percentage increase from cell A1 to cell B1. Another extracts the first name from a full name in cell C1.¹

Notice they name cells and say what to do with them. That's the pattern to copy.

One formula or a whole column?

Ask for what you need and no more. Microsoft says that for a precise calculation in one cell, Copilot can suggest a formula and explain how it works.¹

For lookups, Copilot may recommend a formula such as XLOOKUP and add a new column for the results.¹ A lookup finds a value in one list by matching it against another, like finding a price by product code.

In Google Sheets, Gemini can generate formulas, and you click Insert to add one to a cell you've chosen.²

What if the formula shows an error?

In Google Sheets, you hover over the cell showing the error and click Fix. Google says Gemini then reviews the formula and analyses the issue.²

As general advice, not something Google says, you can also describe the error to the AI and ask again.

An error message is the kinder outcome, because it's obvious. The worse outcome is a formula that runs fine and gives a wrong number, because nothing flags it.

What do I do with the formula once I have it?

Don't paste it into your live sheet and move on. Microsoft tells readers to check whatever Copilot produces.¹

Try it first in a spare column or a copy of the file. Then work through the steps in Checking a formula the AI wrote. Some jobs need the exact same answer every time, and a formula you understand may suit those better than a prompt. The page on formulas versus pivot tables explains how to tell.

Keep real customer details out of the request. Describe the columns by name instead. The reasons are on the data and privacy page.

Can I step through a formula to see how it works?

In Excel, yes. The Evaluate Formula tool shows the parts of a nested formula in the order they're calculated. Only one cell can be evaluated at a time.³