Get a spreadsheet formula from a plain-language request

Data analyst · Time: 10 min · Spreadsheets

Tools that fit this task

• ChatGPT $20/mo
• Gemini in Google Sheets $14/mo
• Copilot in Excel $20/mo
• Numerous.ai Free
• Formula Bot Free
• Qwen Chat Free

Prompt

Role: you are an expert in {{Google Sheets or Excel}}.
Context: in the sheet, column A {{contains what}}, B {{contains what}}, C {{contains what}}, data starts on row {{number}}. Dates are in {{format}}, the argument separator in my locale is {{comma or semicolon}}.
Task: give me a formula that {{what it should return}}. Explain how it works part by part and name two typical mistakes that would make it return a wrong result.
Format: the formula on a separate line, ready to paste into cell {{address}}, then an explanation of up to five sentences, then how to check the result on a control sample.

Steps

1. Describe the table structure: what each column contains, which row the data starts on, the format of dates and numbers
2. State the result in one sentence, for example "total costs of a division for the current month"
3. Give the assistant the description and the task with a prompt for a formula with an explanation
4. Paste the formula into the sheet and check it on five rows by hand or with a control total
5. If the result differs, tell the model what the formula returns and what it should return, and ask it to find the missing condition
6. Save the formula with its explanation in a note on the sheet for colleagues

How to check the result

The formula matches a manual calculation on five rows and a control total for the column, and a colleague understands the explanation without you

Pitfalls

• The model mixes up argument separators (comma versus semicolon) and absolute references: check them in your locale
• Dates stored as text and numbers with spaces break any formula, so bring the column to one format first

Data that stays out of public AI tools

• CRM and ERP exports with client and employee names: anonymize them before uploading to a public service
• Company financials before publication: into a service only inside a corporate environment or with placeholder names
• API keys to systems and databases: keep them in secrets, never in cells or prompts

• Get a Google Sheets formula from plain English with Gemini

Other tasks for data analysts

• Design a KPI dashboard for the business owner
• Analyze budget variances, actual versus plan
• Analyze customer reviews by topic
• Build a competitor price review
• Build a 12-week cash flow forecast
• Explain the numbers to the business owner
• Summarize a long PDF
• Build a quarterly results presentation
• Run a quick counterparty check before a deal

AI tools for data analysts · Prompt: Get a spreadsheet formula from a plain-language request