Why does my AI-generated Excel formula return an error?

Usually a version mismatch — the formula uses a function your Excel does not have — or a range that does not match your actual layout. Stating both in the prompt prevents nearly all of it.

Should I use one complex formula or helper columns?

Helper columns, in most cases. They are readable, debuggable, and someone else can maintain them. A nested one-liner is impressive and becomes unmaintainable the moment requirements change.

Which AI model is best for writing an Excel formula?

All of them handle this; what changes is the packaging. This page shows the same prompt written for Claude, ChatGPT, Gemini. Claude follows XML-delimited structure most reliably, ChatGPT works best with markdown headings, and Gemini prefers the task stated before the material. The content of the prompt is identical in each.

Can I change this prompt for my own situation?

Yes, and you should. Replace the placeholders with your own details, then open it in the builder to add context specific to you. The builder scores what you supply and tells you exactly which missing piece would improve it most.

Why does this prompt include rules I did not ask for?

Because writing tasks carry requirements that experienced practitioners apply automatically and rarely write down. The compiler adds them so the output does not fail on something obvious. Every added rule is listed on the how it works page.

AI prompt for writing an Excel formula

This is a ready-made AI prompt for writing an Excel formula, written for Claude and free to copy. Spreadsheet formulas fail silently. A wrong result looks like a number rather than an error, so the useful output is not just the formula but the list of conditions under which it quietly stops being right. The prompt below handles that: it is compiled as a writing task, so it carries the structure and the constraints that kind of work needs.

The prompt

Written for Claude. Compiled as a writing task — producing original prose: articles, essays, stories, letters, and long-form copy.

<role>
You are a senior writer and editor with a decade of experience in this exact form.
</role>

<context>
Application and version: {{application}}. Sheet layout: {{layout}}. Data quirks: {{quirks}}.
</context>

<task>
Write a spreadsheet formula that does: {{what_you_want}}.
</task>

<constraints>
- Use only functions available in the stated application and version.
- Explain each part of the formula in order.
- State what breaks it: empty cells, text where numbers are expected, added rows.
- If a helper column would make it substantially simpler, show both.
- Write in plain, direct prose. Do not restate the prompt, and do not open with a throat-clearing sentence.
- Vary sentence length. Do not begin consecutive sentences with the same word or structure.
- Do not invent facts, statistics, quotations, or attributed opinions.
</constraints>

<output_format>
Respond in Markdown. Use descriptive headings, and keep paragraphs to three sentences or fewer.
</output_format>

<before_you_start>
If anything above is unclear, or you are missing information you need, ask up to three specific clarifying questions before producing any output. Do not invent facts, names, numbers, sources, or quotations to fill a gap — if you do not know something, say so plainly.
</before_you_start>

Now, write a spreadsheet formula that does: {{what_you_want}}.

Open this in the prompt builder to add your own context and see what would improve it most.

What makes writing an Excel formula hard to prompt for

Spreadsheet formulas fail silently. A wrong result looks like a number rather than an error, so the useful output is not just the formula but the list of conditions under which it quietly stops being right.

Why this prompt works

Background comes before the instruction: The model reads the situation before it learns what to do with it, which stops the instruction being diluted by everything that follows. The task is then restated as the final line, where models weight it most heavily.
Rules a writing task assumes but nobody writes down: Write in plain, direct prose. Do not restate the prompt, and do not open with a throat-clearing sentence. Vary sentence length. Do not begin consecutive sentences with the same word or structure. These are added automatically because leaving them implicit is the most common reason this kind of output disappoints.
Permission to ask instead of guess: This brief leaves room for interpretation, so the prompt tells the model to ask before inventing details. That converts a confidently wrong answer into a question you can actually answer.

What to replace

Placeholder	What to put there
{{application}}	Your application.
{{layout}}	Your layout.
{{quirks}}	Your quirks.
{{what_you_want}}	Your what you want.

Check the output before you use it

Test with an empty cell and with a number stored as text — the two commonest breakages.
Confirm the functions exist in your version. XLOOKUP and LET are not in older Excel.
Check what happens when rows are added below the range.

The same prompt for other models

Identical content, packaged the way each model reads most reliably.

ChatGPT

## Role

You are a senior writer and editor with a decade of experience in this exact form.

## Context

Application and version: {{application}}. Sheet layout: {{layout}}. Data quirks: {{quirks}}.

## Task

Write a spreadsheet formula that does: {{what_you_want}}.

## Constraints

- Use only functions available in the stated application and version.
- Explain each part of the formula in order.
- State what breaks it: empty cells, text where numbers are expected, added rows.
- If a helper column would make it substantially simpler, show both.
- Write in plain, direct prose. Do not restate the prompt, and do not open with a throat-clearing sentence.
- Vary sentence length. Do not begin consecutive sentences with the same word or structure.
- Do not invent facts, statistics, quotations, or attributed opinions.

## Output format

Respond in Markdown. Use descriptive headings, and keep paragraphs to three sentences or fewer.

## Before you start

If anything above is unclear, or you are missing information you need, ask up to three specific clarifying questions before producing any output. Do not invent facts, names, numbers, sources, or quotations to fill a gap — if you do not know something, say so plainly.

Now, write a spreadsheet formula that does: {{what_you_want}}.

Gemini

**Task**

Write a spreadsheet formula that does: {{what_you_want}}.

**Role**

You are a senior writer and editor with a decade of experience in this exact form.

**Context**

Application and version: {{application}}. Sheet layout: {{layout}}. Data quirks: {{quirks}}.

**Constraints**

- Use only functions available in the stated application and version.
- Explain each part of the formula in order.
- State what breaks it: empty cells, text where numbers are expected, added rows.
- If a helper column would make it substantially simpler, show both.
- Write in plain, direct prose. Do not restate the prompt, and do not open with a throat-clearing sentence.
- Vary sentence length. Do not begin consecutive sentences with the same word or structure.
- Do not invent facts, statistics, quotations, or attributed opinions.

**Output format**

Respond in Markdown. Use descriptive headings, and keep paragraphs to three sentences or fewer.

**Before you start**

If anything above is unclear, or you are missing information you need, ask up to three specific clarifying questions before producing any output. Do not invent facts, names, numbers, sources, or quotations to fill a gap — if you do not know something, say so plainly.

Now, write a spreadsheet formula that does: {{what_you_want}}.

Common questions

Why does my AI-generated Excel formula return an error?: Usually a version mismatch — the formula uses a function your Excel does not have — or a range that does not match your actual layout. Stating both in the prompt prevents nearly all of it.
Should I use one complex formula or helper columns?: Helper columns, in most cases. They are readable, debuggable, and someone else can maintain them. A nested one-liner is impressive and becomes unmaintainable the moment requirements change.
Which AI model is best for writing an Excel formula?: All of them handle this; what changes is the packaging. This page shows the same prompt written for Claude, ChatGPT, Gemini. Claude follows XML-delimited structure most reliably, ChatGPT works best with markdown headings, and Gemini prefers the task stated before the material. The content of the prompt is identical in each.
Can I change this prompt for my own situation?: Yes, and you should. Replace the placeholders with your own details, then open it in the builder to add context specific to you. The builder scores what you supply and tells you exactly which missing piece would improve it most.
Why does this prompt include rules I did not ask for?: Because writing tasks carry requirements that experienced practitioners apply automatically and rarely write down. The compiler adds them so the output does not fail on something obvious. Every added rule is listed on the how it works page.

Browse all ready-made prompts, the full prompt library, or read the guides.