Ask for the test up front
Use a three-part prompt so the AI gives you the formula, tells you what it decided without being asked, and suggests how to check it.
Free course. Your Maestro, an AI, .
Often the AI does add the FALSE, or gives you an XLOOKUP. You don't know until you check, so ask it to help you check from the start. This is the prompt I use for the bakery:
I use Excel in [English or Spanish] and I have two sheets. Ventas has the product code in column A and the quantity in column B. Precios has code, product, and price in columns A, B, and C. Give me a formula for column C of Ventas that brings in the price. Explain each part, tell me what you decided without my asking, what happens if a code isn't in Precios, and what test I can run to check it.Look at the last three requests. "Tell me what you decided without my asking" brings out decisions like leaving off VLOOKUP's fourth part. "What happens if a code isn't there" is step 2, trying to make it fail, asked for in advance. "What test I can run" gives you a test ready to go.
You run the test, because you know what should come out.
Tell it which language your Excel is in. In Spanish-language Excel, functions have different names and in some countries their parts are separated with semicolons, so a formula written for English Excel won't work if you paste it as is.
You don't need to share your data
You don't need to upload your file or share your data. The AI doesn't care what products you sell or what they cost. It cares how your sheets are laid out. The prompt has three parts:
What you're working with
Excel, in which language, and how many sheets you have.
What each sheet has
What it's called and what's in each column. If the price is in pesos or in cents, or if the code is text, say that too.
What you want
The formula and where it goes, and an explanation of how to check it.
The hard part is the second one. What you know about your sheets, you know so well it doesn't occur to you to say it. That prices are in pesos, that codes start with P, that there are new products every month. The AI doesn't know it until you write it down. And if it doesn't know, it decides for you, the way the bakery formula decided what to do with a code it couldn't find.
Try it
Pick a spreadsheet at work where a formula would help.
- Write your version of the prompt with the three parts. Don't include real data, only how the sheet is laid out.
- Reread it and look for one thing you know about that sheet and didn't write: units, formats, missing values, things that change every month. Add it.
- If you like, send it to the AI you use. When it answers, check: did it tell you what it decided without being asked? What test did it suggest, and do you know what should come out?
A good answer has all three parts, names at least one thing you added in step 2, and has a test with its expected answer.
Ask your Maestro to review your prompt.