Get help writing an Excel or Google Sheets formula
The problem
You know what the new column should calculate, but not how to write the formula, and a wrong one fails silently.
Recommended approach
Use the AI in your spreadsheet if you have it (Copilot in Excel or Gemini in Sheets); otherwise describe your columns to a general assistant. State the rule in plain words with one worked case and its right answer, ask for the formula plus an explanation, then test it on rows you can check by hand, including blanks.
Try this now
- Describe your columns. Column letters, what each holds, and the units.
- State the rule. In plain words, with one worked case and its right answer.
- Ask for the formula. Use the example request below.
- Test the edge cases. Try blanks, zeros, text in number columns, and the first and last rows.
- Keep the explanation. Add a note next to the formula saying what it does.
What you should get: A formula, a plain-English explanation of each part, and test cases to confirm it works.
I use [Excel / Google Sheets]. Columns: [A = order date, B = amount, C = region]. Rule: [in column D, show the amount if the region is North and the date is in 2026, otherwise 0]. Worked case: [row 2 should give 150]. Give me the formula for D2, explain each part in plain English, and tell me how it handles blank or text cells. Then list 3 test cases I should check by hand.
Check the result
- The worked case gives the right answer
- Blank and zero cells behave the way you want
- The formula still works when copied down the column
- You can explain what it does
Which tool to use
Use what you already have first. These options are not ranked.
If you already have it
Use the AI built into your spreadsheet
Best when: You have Microsoft 365 Copilot or Google Workspace with Gemini at work
Tools: Microsoft 365 Copilot Business, Google Workspace with Gemini
More options: plans, costs and subscriptions
Moderate: documented workflow fit; no independent head-to-head testing of these tools for this job. Prices come from each tool's record, with the date checked; always confirm on the vendor's page.
If you already have it
Use the AI built into your spreadsheet
- Best when
- You have Microsoft 365 Copilot or Google Workspace with Gemini at work
- Tools
- Microsoft 365 Copilot Business, Google Workspace with Gemini
- Why it fits
- Formula suggestions and explanations appear in the workbook, where you can test them straight away.
- Subscription
- Only if your organization already licenses it. Don't buy it for this job alone.
- Cost (from tool records)
- Microsoft 365 Copilot Business: Promotional $18/user/month annual commitment; regular $21 shown at verification. (reported, not re-checked 2026-10-21 · source)
Google Workspace with Gemini: Workspace business pricing varies by plan; official page lists AI integrated across Workspace. (reported, not re-checked 2026-10-21 · source)
Free / already-owned option
Use one general assistant you already have (free tiers often enough)
- Best when
- You have no AI at work, or only a personal account
- Tools
- ChatGPT, Claude, Gemini
- Why it fits
- Describe the columns and the rule; paste the formula into your sheet and test it there.
- Subscription
- Not required: free tiers can be sufficient; usage limits vary by plan.
- Cost (from tool records)
- ChatGPT: Free; Go $8/month U.S.; Plus $20/month. (reported, not re-checked 2026-10-21 · source)
Claude: Free; Pro $20/month billed monthly ($17/month with annual billing); Max from $100/month (U.S.). (verified 2026-10-21 · source)
Gemini: Free access plus Google AI Plus $9.99 and Pro $19.99/month U.S. (reported, not re-checked 2026-10-21 · source)
Assumptions
- You can use at least one general AI assistant (many have free tiers), or your employer already provides one.
- You are allowed to put this material into that tool. Check your workplace policy first.
Why this approach
Why it fits
- Microsoft and Google both document formula suggestions and explanations inside the spreadsheet.
- A formula is easy to test: the sheet does the arithmetic, not the AI.
- Once it works, the formula repeats the rule every time without asking AI again.
When not to use it
- Figures for financial statements, payroll or tax that need a qualified reviewer.
- You can't state the rule in words yet; agree the business rule first.
How we know
| Claim | Evidence | Source | Checked |
|---|---|---|---|
| Promptivity's own research synthesis (Research 1) lists this in its next 20 use cases to publish, drawing on published survey, behavioural and experimental studies of workplace AI use. Internal prioritization by Promptivity, not an independent evaluation and not a performance result. | Promptivity research synthesis | Source | 2026-10-26 |
| Which tools fit, and on which plans, comes from Research 2: vendor documentation plus Promptivity editorial judgement. Its rankings are editorial, not measured. No product was hands-on tested. | Editorial | Source | 2026-10-26 |
| Microsoft Support documents Copilot in Excel generating formula columns and rows, suggesting single-cell formulas and lookups, and explaining existing formulas. Vendor documentation, published by the vendor. Page fetched and read on 2026-10-26. Not hands-on tested by Promptivity. | Vendor-documented | Source | 2026-10-26 |
| Microsoft's Copilot in Excel FAQ says generated insights and formulas can be inaccurate and must be reviewed, and that Copilot needs files saved to OneDrive or SharePoint with AutoSave on. Vendor documentation, published by the vendor. Page fetched and read on 2026-10-26. Not hands-on tested by Promptivity. | Vendor-documented | Source | 2026-10-26 |
| Google Docs Editors Help documents creating formulas with Gemini in Sheets and a Fix option that reviews a formula showing an error. Vendor documentation, published by the vendor. Page fetched and read on 2026-10-26. Not hands-on tested by Promptivity. | Vendor-documented | Source | 2026-10-26 |
What we don't know yet
- No independent test compares formula correctness across Copilot, Gemini and general assistants.
- Function differences between Excel and Sheets can make a formula work in one and fail in the other.
Evidence types: Vendor-documented (the vendor's own help pages) · Outside evaluation (independent published study) · Promptivity research synthesis (our own compilation of published studies) · Editorial (our judgement) · Unknown. Nothing on this page was hands-on tested by Promptivity. Revisit if credible independent testing shows one tool writes correct formulas materially more often, or if a listed tool changes access or price.
How this guide was prepared
This guide is based on published sources. We have not independently tested this workflow hands-on.
Related jobs
Understand a spreadsheet without being an Excel expert
Explain what the data means and find a few checkable findings.
Decision support & problem solvingCompare options with a decision matrix
You set the criteria and weights; AI organizes the evidence and shows what could change the answer.
One AI Use Case a Day
Get one useful AI use case in your inbox every day. Free.
Double opt-in: we email you a confirmation link first. Sent by Promptivity <newsletter@promptivity.ai>, once a day. Unsubscribe in one click. We never sell your address or share it with sponsors. Brevo stores and sends it for us as our data processor. When you subscribe, we may save a short label from a link used in this tab, such as X or LinkedIn, to understand how our signup links are used. Open and click tracking is optional and off unless you tick the box. How we handle your email.