Guide

ChatGPT prompts for Excel and Google Sheets: formulas, clean-up and reports without the guesswork

Published 12 October 2026

A tidy home-office desk with a laptop showing an abstract grid of coloured blocks, a highlighted paper table, a calculator, a pen and a glass of water

Short answerTo get a spreadsheet formula that works, tell the AI which app you use (Excel or Google Sheets), paste your column headers and two or three sample rows, say what the result should look like, and ask it to explain the formula. Then test it on a small sample before using it on the whole sheet.

Spreadsheet help is one of the best everyday uses of an AI assistant, because the answer is easy to check: the formula either returns the right number or it doesn't. The trouble is that most people ask too vaguely ("a formula to add up sales") and get one that almost works.

This guide gives you prompts that get a formula that works the first time, plus examples of the formulas you're most likely to be given, so you can tell whether the answer looks right.

The four things to include every time

The app and version. Excel 365, Excel 2019, Google Sheets. Some newer functions don't exist in older Excel.
Your column headers and 2-3 sample rows, with fake or anonymised data.
The cell ranges you want the formula to use, such as "dates in column A, region in column B, amounts in column C".
What the result should look like, with an example: "for North in January it should return 1,250."

Prompt 1: a formula from plain English

prompt — 2 blanks

I use [EXCEL 365 / GOOGLE SHEETS]. My columns are: A = Date, B = Region, C = Salesperson, D = Amount. Here are 3 sample rows:
[PASTE 3 ROWS]

Write a formula for cell G2 that returns the total Amount for the region in F2 between the dates in H2 and I2. Explain each part in one line, tell me which cells to change if my data starts on a different row, and give me one test I can do by hand to check it.

The answer is typically a SUMIFS, such as:

prompt

=SUMIFS(D:D, B:B, F2, A:A, ">="&H2, A:A, "<="&I2)

Read it back: add up column D where column B matches F2 and column A falls between the two dates. If you can follow that sentence, the formula is probably right. Our Excel and Google Sheets formula helper prompt is the full version of this.

Prompt 2: look something up

prompt — 1 blank

I use [APP]. Sheet 1 has product codes in column A and prices in column C. In Sheet 2, column A has product codes and I want the price in column B. Write the formula, make it show "Not found" instead of an error, and tell me if my version needs a different function.

A modern answer is XLOOKUP, which both Excel 365 and Google Sheets support:

prompt

=XLOOKUP(A2, Sheet1!A:A, Sheet1!C:C, "Not found")

Older versions of Excel use VLOOKUP or INDEX with MATCH, which is why you tell the AI your version.

Prompt 3: clean a messy column

prompt — 2 blanks

I use [APP]. Column A has names typed inconsistently (extra spaces, mixed capitals, some with a title like Mr/Mrs). Here are 8 examples:
[PASTE 8 EXAMPLES]

Give me a formula that trims spaces and converts to proper case, then a second formula that strips the title. Show the results for my 8 examples so I can check them.

The first part is usually =PROPER(TRIM(A2)). For a bigger job, the data cleaning plan prompt helps you plan the order of steps before you touch the data.

Prompt 4: highlight what needs attention

prompt — 1 blank

I use [APP]. In column D I have due dates. Write a conditional formatting rule that turns the whole row red if the date is before today and the status in column E is not "Done". Tell me exactly where to click and what to type in the formula box, and which range to apply it to.

A typical custom-formula answer is:

prompt

=AND($D2<TODAY(), $E2<>"Done")

Notice the $ before the column letters. It locks the column while letting the row change, which is the usual reason a rule highlights the wrong cells.

Prompt 5: summarise a table into a short report

prompt — 2 blanks

Here is a table of [WHAT, e.g. monthly expenses] (headers and 15 rows, anonymised):
[PASTE]

Tell me: the three biggest changes month to month, anything that looks unusual, and two questions I should check in the data. Use only the numbers above, and say "not enough data" if you can't tell.

For long reports and budgets, the monthly budget prompt is a good example of structured data in, short advice out.

Prompt 6: when you need a query or a script

prompt — 2 blanks

I have a sheet with [DESCRIPTION OF COLUMNS]. Write [a SQL query / a Python script / Google Apps Script] that [TASK, e.g. merges these two lists, removes duplicates and exports the result to a new tab]. Explain how to run it, and tell me what could go wrong with my data.

The SQL from plain English prompt and Python script helper prompt cover these in more depth.

Always check before you trust

Test on a few rows where you know the answer by hand.
Look for hidden errors: numbers stored as text, extra spaces, dates stored as words, blank rows.
Check the range. The formula should cover every row, but not the totals row.
Ask the AI to find the weak spot: "What kind of input would make this formula give a wrong answer?"
Keep a copy of the original sheet before you paste in anything new.

Protect your data

Don't paste customer names, bank details, employee salaries or anything confidential into a chatbot unless your organisation's rules allow it. Replace the real values with fake ones, and paste only the structure and a few made-up rows. The formula doesn't need your real data.

Where to go from here

If the AI keeps giving you answers that almost work, our guide on why ChatGPT answers are wrong explains the usual causes, and how to edit an AI prompt shows how to fix the prompt instead of starting over. More ready-made prompts are in the ChatGPT prompts collection, and the free prompt analyzer can check a prompt before you send it.