Introduction
If you run a small business, your spreadsheets already hold the answers: which products make money, which clients pay late, and where costs are creeping up. The problem is rarely the data. It is the formula standing between you and the answer.
Prompt engineering for spreadsheets changes that job. Instead of memorising function syntax, you describe the result you want in plain English, and an AI assistant in Excel or Google Sheets writes the formula for you. Done well, it turns a 20-minute lookup into a 20-second copy-and-paste. Done badly, it produces a confident-looking formula that quietly adds the wrong column.
This tutorial shows the workflow that works: how to prepare your sheet, how to write a prompt the AI can actually follow, how to generate formulas in Microsoft Excel with Copilot and in Google Sheets with Gemini, and how to check the result before you trust it. No coding experience is needed — only a clear description of your data.
Prerequisites
- A spreadsheet with real data, in Excel (Microsoft 365) or Google Sheets. A sheet with headers in row 1 and no blank header cells works best.
- Access to Copilot in Excel or Gemini in Google Sheets, depending on which app you use. Availability depends on your subscription and, for work accounts, your admin settings — if you do not see the AI button, check your plan before assuming the feature is missing.
- One small test case you can check by hand, such as three rows whose total you already know. Never test a new formula on your full client list first.
- About 30 minutes for your first run. Later formulas take minutes once you reuse the prompt patterns below.
Step 1: Prepare the sheet before you prompt
AI writes better formulas from clean sheets. Before you open any AI panel, do this quick tidy-up:
- Put one header per column in row 1, with no merged cells in the header row. Use names like OrderDate, Product, Quantity and UnitPrice instead of vague labels like Data or Value.
- Format your data as a table (in Excel: Insert > Table; in Google Sheets, keep one continuous block with no blank rows inside it). Tables give the AI stable column names to reference.
- Keep dates, numbers and text in consistent formats. A column mixing “12 Oct”, “Oct 12” and “12/10/2026” will confuse any formula, AI-written or not.
- Remove totals rows from inside the data block. A grand-total row sitting in the middle of your data gets counted twice more often than you would expect.
This five-minute tidy-up is the single biggest accuracy upgrade in this tutorial.
Step 2: Write a prompt with five parts
Vague prompts produce vague formulas. Every good spreadsheet prompt contains five parts, in this order:
- Goal: what result you want in this column.
- Columns: the exact header names and what each one holds.
- Rules: conditions, edge cases and what to do with blanks.
- Format: the output type — a number, a date, a category label, or currency.
- Example: one row of sample input and the answer you expect.
Copy, adapt and reuse this template:
“Create a formula for column F. Columns are: A OrderDate, B Product, C Quantity, D UnitPrice. For each row, calculate total revenue (Quantity times UnitPrice), but return blank if Quantity or UnitPrice is empty. Return the result as currency with two decimals. Example: Quantity 3, UnitPrice 25 gives 75.00.”
Notice what the prompt does not do: it does not say “calculate stuff” or “fix my sheet”. It names columns, states the rule for blanks, and gives a checkable example. When the AI gets the example wrong, you know immediately — and so does the AI, when you paste the mismatch back in your next prompt.
Step 3: Generate the formula in Excel or Google Sheets
In Excel with Copilot: format your data as a table, select the Copilot button in the ribbon, and choose Add formula columns or describe the column you want in your own words. Copilot suggests a formula with an explanation of how it works; preview it, then insert the column. To understand an existing formula instead, select the cell with the formula, choose the Copilot sparkle icon next to the cell, and choose Explain this formula. See Microsoft Support for the official guidance.
In Google Sheets with Gemini: open the sheet, click Ask Gemini at the top right, and describe the formula using your sheet references or generic cell names. You can also start from any cell, type =, then use Ctrl + Alt + G on Windows and Chrome OS or Cmd + Ctrl + G on macOS to open the formula prompt. Gemini returns a formula you can insert into the selected cell. See Google Support for the official guidance.
In both apps, do not accept the first suggestion blindly. Read the explanation first: if it references the wrong column or misunderstands a condition, fix the prompt and regenerate rather than patching the formula by hand.
Step 4: Test on a few rows before you fill the column
An AI formula that looks right can still be wrong in exactly one case — the blank row, the refund, the duplicate order. Test like this:
- Insert the formula into three to five rows only, including one edge case (a blank, a zero, a returned order).
- Check each result by hand or with a calculator. For totals, compare against a manual SUM of the same rows.
- Only then fill the formula down the full column. In Excel tables, the calculated column fills automatically; in Sheets, drag or double-click the fill handle.
- Add a spot-check row at the bottom: a SUM, a COUNT and, where it matters, an average. If a later edit breaks the column, the spot-check numbers move and warn you.
This step takes two minutes and catches most errors while they are still cheap.
Step 5: Fix errors by asking the AI to explain, not just rewrite
When a formula returns an error or a wrong value, resist the urge to ask for a completely new formula. Instead, narrow the problem:
- Paste the error and ask: “This formula returns #VALUE! in row 7. Row 7 has a blank in column C. Explain why, then fix only that case.”
- In Google Sheets, when a cell shows an error, hover over it and click Fix: Gemini reviews the formula and analyses the issue, and you can stop it from the side panel if the suggestion looks wrong. See Google Support.
- In Excel, use Explain this formula on the problem cell, then ask Copilot for a version that handles the specific case you found. See Microsoft Support.
- If the AI keeps repeating the same mistake, simplify: ask for the formula for one condition first, verify it, then add the second condition in a follow-up prompt.
Debugging in small steps is faster than regenerating from scratch, and it teaches you which of your prompt phrases the AI misunderstood.
Step 6: Save your best prompts as a reusable library
The real time saving is not one formula — it is the tenth time you reuse the same prompt. Keep a second sheet or a document called Spreadsheet Prompts with one entry per formula: the prompt, the working formula, and a one-line note about what it does. Good starter entries are total revenue by row, days overdue from a due date, category from a score band, and a lookup that pulls a price from a price list.
The next time a similar sheet arrives, you paste the prompt, swap the column names, and test — the whole job drops from half an hour to five minutes. Teams that do this also stop depending on the one person who “knows Excel”: the prompt library is the knowledge, and anyone can run it.
Tips and common mistakes
- Name your columns in the prompt, every time. “Column C” breaks the moment someone inserts a column; “UnitPrice” survives.
- One result per prompt. Asking for a total, a category and a chart in one prompt gives you three half-finished answers. Get the formula right, then ask for the next thing.
- Never paste sensitive data into a public chatbot to get a formula. Use the AI built into your spreadsheet, or anonymise the sheet first — replace real client names and account numbers with placeholders before pasting anything externally.
- Watch for the AI inventing a function that does not exist in your app. If a formula uses a function you have never seen, check it in the app’s function list before trusting it. A plausible name is not proof.
- Keep the human check for money. AI formulas are fine for drafts and analysis, but invoices, payroll and tax figures deserve a manual spot-check every time, no exceptions.
- Pair this skill with good research prompts: our guide to using Perplexity for competitor and market research uses the same discipline — dated, sourced, checkable answers — and our n8n invoice-reminder workflow shows what a clean Google Sheet can automate once its formulas are reliable.
FAQ
Do I need Copilot or Gemini to use AI for spreadsheet formulas?
No. The prompt patterns in this tutorial also work with a general chatbot such as ChatGPT, Claude or Gemini on the web: describe your columns, rules and example, paste the formula it returns into your sheet, and test it. The built-in assistants are simply more convenient because they can see your sheet and insert the result directly.
Why does the AI keep giving me the wrong formula?
Almost always because the prompt left something out: a blank case, a column name, or the expected output format. Add the missing rule and one example row with its expected answer, then regenerate. If it still fails, split the job — one condition per prompt — and combine the working parts afterwards.
Is it safe to let AI touch my business spreadsheets?
Use the AI built into Excel or Google Sheets for real business data, and follow your organisation’s rules for AI tools. Do not paste sheets containing client names, bank details or salaries into a public chatbot. For sensitive files, anonymise the data first or work on a copy with placeholders.
Can AI formulas replace learning Excel?
No, and that is not the goal. AI removes the syntax barrier, but you still need to know what result you want and how to check it. The most productive users are not the ones who memorise functions — they are the ones who can describe a calculation clearly and spot a wrong total in seconds.
Which is better for AI formulas, Excel or Google Sheets?
For most small businesses, use the app your team already lives in. Excel with Copilot and Google Sheets with Gemini both generate and explain formulas, and both need the same clean-sheet preparation and testing. Switching apps to chase one AI feature costs more in retraining than it saves in formulas.
