ChatGPT Prompts for Excel and Google Sheets Formulas (With Examples)

The anatomy of a good spreadsheet prompt

Vague prompts such as 'make a formula to sum sales' produce vague answers. A good prompt contains four things: the program and version, the layout of your data, a couple of example rows and the exact output you want.

The more precisely you describe the structure, the less the AI has to guess. Column letters, header names and sample values matter.

• Program: Excel or Google Sheets.
• Layout: which column holds what.
• Examples: two or three realistic rows.
• Goal: the result you expect for those rows.

Prompt template: write a formula

Use this as your default starting point.

• Prompt: I use [Excel / Google Sheets]. Column A has dates, column B has product names and column C has sales amounts. Example rows: [paste 3 rows]. Write a formula that returns the total sales for product 'X' in November 2026. Explain how it works in plain language and give one alternative approach.

Prompt template: explain a formula

Inherited a complicated spreadsheet? AI is good at translating formulas into plain English.

• Prompt: Explain this formula step by step as if I am new to spreadsheets: [paste]. Then tell me what could go wrong, for example blank cells or text instead of numbers.
• Prompt: Rewrite this formula so it is easier to read, and explain what you changed.

Prompt template: debug an error

Paste the formula, the error message and a few example cells.

• Prompt: This formula returns [error]: [paste]. Cell A2 contains [value], B2 contains [value]. Explain the likely cause and give a corrected formula.
• Prompt: My lookup returns the wrong row. Here is the data layout and the formula: [paste]. What am I missing?

Cleaning and reshaping data

Common data jobs are a natural fit for AI: splitting full names into first and last, standardising dates, removing duplicates or combining text from several columns.

• Prompt: Column A contains names like 'Smith, John'. Give me a formula to output 'John Smith' in column B.
• Prompt: Dates in column C are text like '03/11/2026'. How do I convert them to real dates in [program]?
• Prompt: Give me a step-by-step way to find duplicates in column A and highlight them.

Always verify

A formula that looks right can still be wrong, particularly with edge cases such as blanks, text stored as numbers and regional settings for separators and date formats. Test on a copy of your file and compare the AI's result with a manual calculation for a few rows.

For financial or legal spreadsheets, have a person review the logic before you rely on it.

Examples of tasks AI handles well

Some spreadsheet tasks are especially suited to AI help. Conditional totals, such as summing values that meet several criteria, are a common one. So are lookups across sheets, text cleaning, date calculations and building a dropdown that drives a dependent summary table.

Describe the result you want as a sentence first, for example 'show the average order value per month for customers in Germany'. Then add the layout. If the first formula fails, paste back exactly what you see, including any error text, rather than describing it loosely.

For bigger jobs, ask the AI to propose a plan before writing formulas: what helper columns you might need, what the final table should look like and how to check the totals. Planning in steps produces cleaner spreadsheets than one enormous formula nobody can read later. Add notes in a spare cell explaining the logic so your future self, or a colleague, can follow it.

Learn as you go

Treat each answer as a mini lesson. Ask 'why this function and not the alternative?' and 'what is the simplest way to do this?' Over time, you will recognise patterns and need the AI less for routine tasks. Keep a note of formulas that work so you can reuse them in future sheets.

If you export data to share, you can format raw data for other tools with the JSON formatter when needed.