AI for Excel: how to get formulas, tables and analysis from ChatGPT, Copilot and Gemini

You don’t need to memorize formulas: AI writes, explains and fixes them. The secret is describing the spreadsheet properly — which column holds each piece of data, where it starts and what result you want.

Describe the spreadsheet

Always say:

1. The columns and what’s in each one (with the letter: A = Date, B = Customer…).
2. Where the data starts (row 2, for example, with the header in row 1).
3. The result you want and in which cell.
4. Your Excel version (Microsoft 365, 2019…) and that you use English function names with commas between arguments — the US English Excel standard.

I have a spreadsheet in Excel (Microsoft 365, US English). Columns: A = Date, B = Salesperson, C = Product, D = Amount. Data from A2 to D500.
In G2 I want the total sold by the salesperson typed in F2, only for September 2026.
Give me the formula with commas as separators and explain each part.

Ready-made prompts

Look up a value in another table:

On the Orders tab, column A has the product code. On the Products tab, A has the code and B has the price.
In Orders!C2 I want the price for each code. Use XLOOKUP if available, or VLOOKUP, and show what to do when the code doesn't exist.

Explain an existing formula:

Explain in simple language what this formula does, part by part, and whether there is any risk of error:
=IFERROR(INDEX(Products!B:B,MATCH(A2,Products!A:A,0)),"not found")

Find the error:

This formula returns #N/A on some rows: [paste the formula]. The codes come from a system and sometimes have spaces.
What are the likely causes and how do I fix it?

See also what each error means in Excel errors and in VLOOKUP returning #N/A.

Data analysis:

I've attached the sales spreadsheet. Show: total by month, the 5 customers who bought the most, the change compared to the previous year and 3 observations that stand out.
Tell me which columns you used in each calculation.

How to check the result

1. Test the formula on 2 or 3 rows whose result you can work out in your head.
2. Check that the ranges ($A$2:$A$500) cover all the data.
3. Make sure the AI used the right separator (commas in US English Excel) and the English function names (SUMIFS, VLOOKUP, IF).
4. For analyses, check the totals with a simple PivotTable.

Copilot inside Excel

With Copilot in Microsoft 365, you can ask for formulas and analysis right in the spreadsheet, without copying and pasting. See how to use Copilot in Word, Excel, Outlook and Teams.

FAQ