How to use ChatGPT for Excel: prompts for formulas, errors and messy data

ChatGPT can write most Excel formulas in seconds, as long as you tell it exactly how your sheet is laid out: which columns hold what, where the answer should go, and which version of Excel you use. Describe the sheet first, paste one of the prompts below, then test the formula on a few rows you can check by hand before filling it down. The same prompts work in Claude, Gemini and Microsoft Copilot.

Replace the details in [square brackets] before you paste a prompt.

Which AI tool to use

Any of the big chat tools can write and explain formulas. The differences that matter for Excel work:

• ChatGPT, Claude and Gemini work in a browser or app next to your spreadsheet. You describe the sheet or upload a copy of it as an .xlsx or .csv file, then paste the result back into Excel.
• Copilot in Excel works inside the workbook itself, so it can see your columns without you describing them. It needs a Microsoft 365 plan that includes Copilot.
• Gemini in Google Sheets is the equivalent if your spreadsheets live in Google Sheets.

How to describe your sheet so the AI gets it right

Most wrong formulas come from the AI guessing your layout. It does not know that your dates are in column C or that "Amount" includes VAT. Give it these details:

1. Sheet and column names with letters. "Sheet Orders: A = Order ID, B = Customer, C = Date, D = Region, E = Amount."
2. Where the data starts and ends. "Headers in row 1, data in rows 2 to 5000." Say if it is an Excel table (created with Ctrl + T) and what the table is called, because the formula will then use column names instead of cell ranges.
3. A few sample rows. Three to five rows with made-up values are enough. They show the AI what your dates, codes and numbers actually look like.
4. Where the answer goes. "Result in column F, one per row" or "a single total in cell H2."
5. Your Excel version. XLOOKUP, FILTER, UNIQUE and TEXTSPLIT do not exist in Excel 2019 or older. If the AI gives you a function your version lacks, you get a #NAME? error.
6. Your Excel language and separator. In many European language versions, function names are translated and arguments are separated with semicolons instead of commas. Say which you use.

You can save this as a reusable starting prompt:

I am working in [Excel for Microsoft 365 / Excel 2019 / Excel for Mac / Google Sheets], in [English, with commas between arguments / another language, with semicolons]. My sheet is called [name]. Columns: [A = ..., B = ..., C = ...]. Headers are in row 1 and data runs from row 2 to about row [number]. Here are 3 sample rows with fake values: [paste]. Keep this layout in mind for everything I ask in this chat. Give me the formula first, then a short explanation of how it works.

Prompts for writing formulas

Look up a value from another sheet (XLOOKUP)

In sheet Orders, column B has product codes. In sheet Prices, column A has product codes and column C has the price. Write a formula for Orders column F that finds the price for each product code. If the code is not found, show "Not found". Also give me a version that works in Excel 2019, without XLOOKUP.

A typical answer looks like =XLOOKUP(B2, Prices!A:A, Prices!C:C, "Not found"), with =IFERROR(INDEX(Prices!C:C, MATCH(B2, Prices!A:A, 0)), "Not found") as the older version. Check that the columns in the answer match your sheet before using it.

Add up with conditions (SUMIFS and COUNTIFS)

Write a formula that adds up column E (Amount) only for rows where column D (Region) equals the region in cell H1 and column C (Date) is in the month in cell H2. H2 contains a date such as 1 March 2026. Then give me a COUNTIFS formula that counts the same rows.

I want a summary table with regions in H5:H10 and months across I4:T4. Write one formula I can put in I5 and copy across and down to fill the whole table. Explain which references need $ signs and why.

Decide with IF, IFS and nested conditions

Write a formula for column G that shows "Overdue" if the due date in column C is before today and column F (Paid) is empty, "Paid" if column F has a date, and "Open" otherwise.

Clean up text

Column B has customer names with extra spaces, random capital letters and sometimes a line break inside the cell. Write a formula for column C that removes the extra spaces and line breaks and capitalises each name properly.

Column A has full names like "Smith, John" and "Novak, Ana". Write formulas that put the first name in column B and the last name in column C. Give me a version for Microsoft 365 using TEXTBEFORE and TEXTAFTER, and one for older Excel.

Work with dates

Column C has invoice dates. Write formulas for: the month name, the last day of that month, the date 30 days later, and the number of working days between column C and today, excluding the holidays listed in sheet Holidays, column A.

Column B has dates imported from another system that Excel treats as text, like "2026.03.14". Write a formula that turns them into real Excel dates, and tell me how to check that the result is a date and not text.

For more speed while you test formulas, the Excel shortcuts cheat sheet covers editing, filling down and jumping around large sheets.

Prompts for explaining a formula

AI tools are good at taking apart formulas in inherited spreadsheets.

Explain this Excel formula step by step, in plain language, as if I have never used these functions: [paste formula]. Then tell me what it returns for this example row: [paste the values from that row].

This formula is in cell [cell] of a sheet I inherited: [paste formula]. What is it trying to do, are there any parts that look like mistakes, and is there a simpler way to write it?

To check the explanation in Excel for Windows, select the cell and use Formulas > Evaluate Formula, which steps through the calculation one part at a time.

Prompts for fixing errors

Error messages tell you the type of problem. A few common causes:

• #N/A in a lookup usually means the value was not found. Common reasons are extra spaces, or a number stored as text on one side and as a real number on the other.
• #VALUE! means a function got the wrong kind of data, such as text where it expected a number or a date stored as text.
• #NAME? means Excel does not recognise a name. Often it is a typo, or a function your Excel version does not have.
• #REF! means the formula points to cells that were deleted.
• #SPILL! means a formula that returns several values is blocked by cells that are not empty.

Give the AI the formula, the error and an example of the data, and it can usually find the cause.

This formula returns #N/A for some rows but works for others: [paste formula]. Here is a row where it fails: [paste values from both sheets]. List the likely causes in order, and tell me how to check each one in Excel.

My formula [paste formula] shows #VALUE!. The cells it uses contain: [paste values exactly as they appear]. What is causing it and how do I fix it?

This formula gives a result, but the result is wrong. Expected: [value]. Got: [value]. Formula: [paste]. Data for that row: [paste]. Find the mistake.

Use the last prompt often. A formula with no error can still return the wrong number.

Prompts for cleaning data

AI can either write formulas to clean data, or tell you which built-in Excel tool fits the job. Often the built-in tool is faster.

I exported this data from [system]: [paste 10 rows with fake values]. List every cleaning problem you see (spaces, mixed date formats, duplicates, merged values, inconsistent spelling) and tell me how to fix each one in Excel, with formulas or with tools like Remove Duplicates, Text to Columns, Flash Fill or Power Query.

I receive this same messy export every week. Walk me through building a Power Query that cleans it, step by step, so I can refresh it next week instead of repeating the work.

If you paste data, use fake or masked values. The AI only needs to see the pattern.

Prompts for planning a pivot table

Pivot tables are built with clicks under Insert > PivotTable, so the AI cannot build one for you in a chat window. It can tell you exactly what to drag where.

I have a table with these columns: [list columns]. I want to see [question, e.g. total sales by region per month, and which product sold most in each region]. Tell me what to put in Rows, Columns, Values and Filters, which summary to use for each value, and any grouping I need for the dates.

Before I build a pivot table from this data, check the structure: [paste headers and 5 sample rows]. Tell me anything that will cause problems, such as blank rows, merged cells, totals inside the data or text in number columns.

Prompts for VBA macros and Office Scripts

AI can write macros for tasks you repeat every week. Two options:

• VBA runs in desktop Excel for Windows and Mac. Turn on the Developer tab under File > Options > Customize Ribbon, press Alt + F11 to open the editor on Windows, and save the file as an Excel Macro-Enabled Workbook (.xlsm).
• Office Scripts run from the Automate tab in Excel on the web, Windows and Mac. They need a Microsoft 365 business or school plan that includes them.

Write a VBA macro that [task, e.g. copies every row where column D is "Closed" from sheet Orders to sheet Archive, then deletes those rows from Orders]. Add comments explaining each step. Ask me any questions about my layout before you write it.

Write an Office Script for Excel on the web that [task]. Explain where to paste it and how to run it.

Explain what this macro does, line by line, and tell me if anything in it deletes data, overwrites cells, sends email or touches other files: [paste code].

A safety note before you run any macro. Changes made by a macro usually cannot be reversed with Ctrl + Z. Always:

1. Run it on a copy of the file first.
2. Read the AI's explanation and make sure it matches what you asked for, using the last prompt above.
3. Never enable macros in a file someone else sent you unless you trust the source and know what the code does.

The limits: check results, protect data

AI tools are fast at formulas and confident even when they are wrong. A few habits keep that from costing you.

Test on rows you can check by hand. Before filling a formula down 5,000 rows, check three or four results with a calculator or by eye, including at least one unusual case such as a blank cell or a missing code.

Do not paste confidential data. Salaries, client lists, health information and anything covered by a confidentiality agreement should stay out of public chat tools. Describe the layout, use fake sample rows, and follow your company's AI policy. Check your tool's data settings too: in ChatGPT, Settings > Data controls lets you choose whether your chats are used to improve the model.

Keep the logic understandable. If you cannot follow a formula after reading the explanation, ask for a simpler version that someone else could maintain.

For more ways to fit tools like this into a normal working week, see our guide to using AI to save time at work. If you run a business, the ChatGPT prompts for small business owners cover marketing, customer replies and planning.

Common questions

Can ChatGPT read my Excel file directly?

Yes. ChatGPT, Claude and Gemini can read an uploaded .xlsx or .csv file and answer questions about it. Upload a copy with sensitive data removed, and still paste any formula it suggests into your real file and test it there.

Which version of Excel do the formulas work in?

That depends on the functions. XLOOKUP, FILTER and other dynamic array functions need Excel for Microsoft 365 or a recent standalone version. Tell the AI your version at the start, and ask for an older alternative when you share the file with others.

Is Copilot in Excel better than ChatGPT for formulas?

Copilot in Excel can see your workbook, so you spend less time describing the layout. ChatGPT, Claude and Gemini work with any version of Excel and any plan. Both need the same checking.

Can AI build a pivot table or chart for me?

A chat tool can tell you exactly how to set one up, and it can create charts from an uploaded file inside the chat. To get a pivot table in your own workbook, you build it in Excel with its instructions, or use Copilot in Excel if your plan includes it.