Get a spreadsheet formula from a plain-language request

Accountant · 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

• Employee names, tax IDs, dates of birth and salaries: into a public service only as [Employee 1] and [amount]
• Account numbers, tax numbers and counterparty names from bank statements and the trial balance: banking and trade secrets, replace them with codes C1, C2
• Digital signature keys and passwords to online banking or the taxpayer portal: never, anywhere

Other tasks for accountants

• Reconcile the trial balance with the bank and source documents
• Categorize a bank statement by cash flow line
• Find discrepancies in a counterparty reconciliation statement
• Check a source document for required fields
• Prepare a reply to a tax authority request
• Write an overdue payment reminder
• Summarize changes in legislation for colleagues
• Analyze budget variances, actual versus plan
• Explain the numbers to the business owner
• Draft an internal company order
• Write a business letter
• Summarize a long PDF
• Keep a contract register and track deadlines

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