Writing an Excel Formula with AI, Step by Step

Do you have an Excel spreadsheet but don’t know which formula to use to get the total you need? An AI can write the formula for you, as long as you explain your spreadsheet clearly. This guide shows you how, with a simple example: adding up the sales for one month.

Describing your spreadsheet to the AI

Start by explaining to the AI what each column contains and which row the data starts on. Do not paste personal or confidential data: it should not leave your computer unless necessary.

Example request I have an Excel spreadsheet. Column A contains the sale dates, column B the salesperson’s name, and column C the amount in euros. The data starts in row 2.

To protect your data, replace real names, addresses, or numbers with [brackets], for example [customer name]. You can fill them in yourself later.

Asking for the result in plain language

Say what you want to get, without jargon. Always mention that you use Excel in French, so the AI gives you the function names in the right language.

Example request I use Excel in French. I want to add up the amounts in column C only for the sales made by [salesperson name] in [month]. Give me the formula to type in a cell.

The main example in this guide is the SUMIFS function (called SOMME.SI.ENS in French Excel). Its official syntax is: SOMME.SI.ENS(somme_plage; plage_critères1; critères1; [plage_critère2; critères2]; …). You can use up to 127 pairs of criteria.

In French-language Excel, arguments are usually separated by semicolons. However, this depends on your computer’s regional settings. If the AI answers with commas or an English function name, ask it for the French version.

Copying the formula into the right cell

A formula always starts with the = sign. Copy it as it is, then paste it into the cell where you want the result to appear. Check that the column letters and row numbers match your spreadsheet.

If the formula gives you nothing useful, first check that you started it with =, and that the cell is formatted as expected.

Asking the AI to explain the formula step by step

Don’t just copy it. Ask the AI to explain each part, so you understand what you are doing and can adapt it later.

Example request Explain this formula step by step, part by part: what does each argument do, separated by a semicolon? Use simple words, I’m new to Excel.

Fixing an error

If Excel shows a message, copy it exactly and send it to the AI with a description of what is happening. For example, the #NAME? error (shown as #NOM? in French Excel) often appears when Excel does not recognize the function name, such as an English name in a French Excel.

Example request When I paste your formula into Excel, it shows #NAME? (or #NOM? in French). My Excel is in French. Here is the exact formula I pasted: [copy the formula]. What needs to be fixed?

Going further: conditional formatting and PivotTables

Once you are comfortable with the formula, you can ask the AI to guide you through other tools, such as conditional formatting (automatically coloring certain cells) or PivotTables (summarizing large lists).

Example request I want to create a PivotTable from my data in columns A to C, with the months in rows and the salespeople in columns. Give me the steps one by one, in simple words.

The steps may vary depending on your version of Excel. If a step does not match what you see on screen, describe to the AI what you actually see.

Good habits

• Always check the result: an AI can make mistakes or give a plausible but wrong answer. Test the formula on a few rows whose total you already know.
• Protect your data: do not enter personal or confidential information, and use [brackets] instead. The CNIL recommends avoiding such entries in generative AI as much as possible.
• Read each formula carefully before using it on real figures, and ask for an explanation if something is unclear.
• Stay in control: a formula that works is not necessarily the right one. Compare the result with a calculation done by hand.