Get a spreadsheet formula from a plain-language request

Procurement manager · Time: 10 min · Spreadsheets

Tools that fit this task

• ChatGPT $20/mo
• Copilot in Excel $20/mo
• Gemini in Google Sheets $14/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

• Supplier prices and terms from sealed bids: a trade secret of both sides, into a service with codes
• Annual procurement plans and budgets: before a tender is announced, only inside a corporate environment
• Contracts with real parties and amounts: into a public service as [Party A], [Party B], [amount]

• Get a Google Sheets formula from plain English with Gemini

Other tasks for procurement managers

• Run a quick counterparty check before a deal
• Build a competitor price review
• Run a risk review of a contract for your side
• Compare two versions of a contract
• Write a formal claim letter to a counterparty
• Keep a contract register and track deadlines
• Find discrepancies in a counterparty reconciliation statement
• Write an email to a foreign partner in their language
• Translate a contract and keep the terminology consistent
• Write a business letter

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