“Give me a formula to find duplicates” is the kind of prompt that produces a formula that is correct in general and wrong for your sheet. The assistant does not know your columns, your Excel version, whether blanks count, or whether “Delhi” and “delhi ” are the same customer. It fills in the gaps with guesses.

After a lot of trial and error, these are the habits that turn a first answer into a usable one.

1. Describe the layout, not just the goal

Compare:

• Weak: “Sum sales by region.”
• Better: “Sheet Data, headers in row 1. A = Date, B = Region, C = Product, D = Amount, rows 2 to about 5,000 and growing. On sheet Summary I have regions in A2:A6. I want the total in B2:B6 for the month in cell B1.”

The second one gets you a SUMIFS with the right ranges and a date condition, first time.

2. Say which Excel you have

“Excel 365”, “Excel 2016”, “Google Sheets” — each changes the answer. Without it you may get XLOOKUP or FILTER for a colleague still on 2016, where they show #NAME?.

3. Paste five sample rows, including a nasty one

Date       | Region | Customer     | Amount
02-01-2024 | North  | Sharma & Co  | 12,500
02-01-2024 | north  | Sharma & Co. | 3,000
03-01-2024 | South  |              | 8,000

Real data has inconsistent case, a trailing full stop, a blank. Showing them makes the assistant deal with them instead of assuming clean data.

4. Say what “correct” means

“Blank customers should be ignored.” “Treat ‘north’ and ‘North’ as the same.” “If nothing is found show 0, not an error.” One sentence each, and you avoid three follow-up rounds.

5. Ask for an explanation and a test

Add: “Explain each part in one line, and give me two rows where the formula would return something surprising.” The explanation lets you check the logic; the surprising cases show you what to test.

6. Test it — really

AI assistants produce confident answers that are sometimes wrong. Before using a formula on real numbers:

• Check it on a row you can calculate by hand.
• Try a blank, a duplicate and a text-number.
• Compare the grand total with a pivot table.

A prompt template

I use [Excel 365 / 2016 / Google Sheets].
Sheet [name], headers in row [n]: A = ..., B = ..., C = ...
About [N] rows. Sample:
[paste 5 rows incl. one messy row]
Goal: [what result, where it goes]
Rules: [blanks, case, duplicates, what to show if not found]
Give the formula, explain each part in one line,
and list two inputs where it might fail.

The same template works for VBA (“Excel 2019 desktop, macro should run on the active sheet”), SQL (“SQL Server 2019, table orders with columns…”) and pandas. Our free AI Helper has modes for each that already ask for this context.

When the answer is still wrong

Do not just say “that doesn’t work”. Say what it returned and what you expected: “For row 3 it returns 15,500 but should be 12,500 because row 4 is a different customer.” Specific feedback gets specific fixes.