AI prompt to write spreadsheet formulas

Email, data entry, reports and planning take up a large share of an agent's week. AI can draft, summarize and organize that work, and specialized tools can pull data from documents and keep client records tidy.

Below is a ready-to-copy prompt to write spreadsheet formulas, a detailed version that asks you clarifying questions first, and tips for using it. It works in any AI assistant: ChatGPT, Claude, Gemini or Copilot.

Try it in the search box ›

The prompt

Write Excel/Google Sheets formulas for: count of policies by line, sum of premium by month, and a lookup of producer names by ID, with a short explanation of each. Use sample column letters.

Detailed version (asks clarifying questions first)

Act as an experienced assistant to a licensed insurance agent. Before answering, ask me up to 3 clarifying questions (your goal, the format you want and the tools you already use). Then complete this task, and finish with a short checklist of statements I should verify with my compliance team.

Task: Write Excel/Google Sheets formulas for: count of policies by line, sum of premium by month, and a lookup of producer names by ID, with a short explanation of each. Use sample column letters.

What a good result looks like

A good result gives you working formulas for each request (COUNTIF for policies by line, SUMIFS for premium by month, XLOOKUP or VLOOKUP for producer names) with sample column letters and a one-line explanation of what each formula does.

Common mistakes and watch-outs

Check that the formulas match your column layout and that they work in your tool; some functions differ between Excel and Google Sheets. Test on a few rows first. Use de-identified sample data when asking the AI for help.

How to adapt the prompt

Describe your real column headers and ask for formulas for other reports, like renewals due next month or retention by producer. Ask for a step-by-step guide to build a pivot table, too.

EXAMPLE output (placeholder data, not a real client)

Assuming: A = Policy line, B = Effective date, C = Premium, D = Producer ID

Policies by line (Auto):
=COUNTIF(A:A,"Auto")

Premium by month (January [Year]):
=SUMIFS(C:C,B:B,">="&DATE([Year],1,1),B:B,"<"&DATE([Year],2,1))

Producer name from ID (lookup table in Sheet2 A:B):
=XLOOKUP(D2,Sheet2!A:A,Sheet2!B:B,"Not found")

How to use it

Copy the prompt. Copy the short prompt above, or the detailed version if you want the AI to ask clarifying questions first.
Add your details. Add the details for your situation (line of business, audience, tone). Keep it de-identified; never paste client personal information.
Run it in the right tool. Paste it into any AI assistant: ChatGPT, Claude, Gemini or Copilot.
Review before you use it. Double-check numbers and extracted data against the source document. Check every fact, name and number yourself.
Take the next step. Next, see: Clean up and standardize client data.

Which AI tool to use

Any AI assistant

ChatGPT Claude Google Gemini Microsoft Copilot

Paste the prompt above (short or detailed) into any of these.

Before you use it

Double-check numbers and extracted data against the source document.
Never paste client personal information (names, policy numbers, health or financial details) into AI tools your agency hasn't approved.
Review every AI output for accuracy and compliance before you use it.

Related tasks

Clean up and standardize client data

More: All operations and productivity prompts · All AI prompts

Was this page helpful?