How to Write Better AI Prompts for Excel
Vague prompts get vague answers. "Help me with this data" produces generic filler. "Give me a SUMIFS for revenue by North region in March 2026 using data in columns A-E, row 2-5000, no dynamic arrays" produces a ready-to-paste formula. This guide is the practical playbook: the CRAFT framework, before-and-after examples that show exactly what changes, and prompt templates you can adapt in seconds.
⚡ Quick Answer
Great Excel prompts have five ingredients — the CRAFT framework: Context (where your data lives), Request (what you specifically want), Arguments/constraints (Excel version, function preferences), Format (output shape), and Tests (edge cases to handle). Include all five and AI produces reliable, ready-to-use output. Skip any and you're inviting guesses. Full framework with templates below.
📋 What we'll cover
The CRAFT framework
Every effective Excel AI prompt contains five elements. Missing any of them makes the AI fill in the gap by guessing, which produces wrong or generic output.
| Letter | Element | What it does |
|---|---|---|
| C | Context | Tells AI where your data is: sheet name, columns, headers, range |
| R | Request | States specifically what you want: formula, analysis, cleanup, chart |
| A | Arguments/constraints | Sets bounds: Excel version, function preferences, "no helper columns" |
| F | Format | Specifies output shape: single formula, list, table, code, narrative |
| T | Tests/edge cases | Names special cases: blanks, errors, "not found" scenarios |
Step 1: State the Context
Tell AI where your data lives
AI doesn't see your workbook (unless you're using Copilot with a workbook open). Every prompt starts by describing the data. Column letters, header row, range, and data types. This is the foundation — skip it and every subsequent instruction becomes ambiguous.
Effective context phrases
- "Sales sheet has columns A-F: A=Date, B=Rep, C=Region, D=Product, E=Amount, F=Customer"
- "Headers in row 1; data from row 2 to row 5000"
- "Column E contains USD amounts stored as numbers"
- "Column A contains dates in MM/DD/YYYY format"
- "The Products sheet is a lookup table: A=SKU, B=Name, C=Category, D=Price, rows 2-500"
"Column D contains phone numbers" is more useful than "Column D contains contact info" because AI can infer format (potentially with area codes, potentially with dashes). Naming the data type helps AI pick the right cleanup or extraction approach.
Step 2: State the Request
Say exactly what you want
Vague requests get vague answers. "Help me with this data" produces generic filler. "Give me a formula that sums Amount for North region in March 2026" produces a specific answer. Make requests as narrow and unambiguous as possible.
Good vs weak requests
Weak
"Analyze this sales data"
AI has to guess what analysis you need.
Specific
"Give me a summary of which
region grew the most in Q3
2026 vs Q3 2025, and identify
the top 3 products in each."
AI knows exactly what to produce.
Request verbs that work well
- Give me a formula that... (single-formula answers)
- List the top N... (ranked lists)
- Compare A vs B on... (side-by-side analysis)
- Identify anomalies where... (outlier detection)
- Explain what this formula does... (formula walkthrough)
- Clean up the data by... (cleanup instructions)
- Extract [X] from column [Y]... (targeted extraction)
Step 3: Add Arguments/constraints
Set the bounds AI should work within
Constraints prevent AI from picking wrong tools. If you're on Excel 2019, don't want XLOOKUP. If you want simplicity, don't want nested INDEX/MATCH. If you don't want helper columns, say so. Explicit constraints eliminate whole classes of wrong-tool answers.
Common constraint categories
| Constraint type | Examples |
|---|---|
| Excel version | "Must work in Excel 2019+" / "M365 features OK" |
| Function preferences | "Use SUMIFS not SUMPRODUCT" / "Prefer XLOOKUP over VLOOKUP" |
| Excluded functions | "No dynamic arrays" / "No LET or LAMBDA" |
| Structural | "Single formula, no helper columns" / "Fillable down the column" |
| Style | "Simplest possible formula" / "Prioritize readability over cleverness" |
| Length | "Under 3 paragraphs" / "One table only, no narrative" |
Step 4: Frame the Format
Tell AI what shape the output should take
Format specification is often what turns a good response into a great one. "Give me the formula" leaves AI room to also explain the formula, add caveats, discuss alternatives. "Give me only the formula, no explanation" produces exactly the paste-ready output you wanted.
Format-specification phrases
- "Just the formula. No explanation."
- "Formula + one-sentence explanation of what it does."
- "A markdown table with columns: X, Y, Z."
- "A bulleted list of findings, one line each."
- "3 short paragraphs: what, why, what to do next."
- "JSON output with keys: category, value, growth_rate."
Step 5: Handle Tests/edge cases
Tell AI what to do with the tricky cases
Real data has blanks, errors, unusual values. Naming what to do with them in the prompt prevents AI from making unstated assumptions that produce silent failures.
Common edge-case phrases
- "If the value is not found, return 'Not Found' instead of #N/A"
- "Ignore blank cells in the source range"
- "Treat blank cells as 0 for the sum"
- "If multiple matches exist, return the first one"
- "Skip rows where column D contains errors"
- "Wrap in IFERROR to hide any error output"
Before & after prompt examples
Same task, weak prompt vs strong prompt, seeing the difference in what you get back.
Example 1: Generate a lookup formula
Weak prompt
How do I look up
a price for a product?
AI produces generic VLOOKUP explanation. You still have to adapt it.
Strong prompt (CRAFT)
Products sheet: A=SKU,
B=Name, C=Category, D=Price
(rows 2-500).
Give me a formula for cell F2
that looks up the Price for the
SKU in E2. Return "Not Found"
if the SKU doesn't exist.
Excel 2019+. Just the formula.
AI returns exactly the formula you need. Paste and done.
Example 2: Data cleanup
Weak prompt
Clean up my names column.
AI doesn't know what "clean" means. Casing? Spacing? Duplicates?
Strong prompt
Column A has 800 person names in
mixed formats (some UPPERCASE,
some "last, first", extra spaces).
Standardize to "Firstname Lastname"
in Title Case, no extra spaces.
Output: give me a cell-level
formula I can fill down column B.
AI returns the exact formula for the cleanup pattern.
Example 3: Data analysis
Weak prompt
What does my data say?
AI produces vague high-level observations.
Strong prompt
Sales data in Sheet1, cols A-F:
A=Date, B=Rep, C=Region,
D=Product, E=Amount, F=Customer
Rows 2-5000, Jan-Aug 2026.
Identify: (1) fastest-growing region
by revenue, (2) any month that
looks anomalous, (3) top 5 reps
by close rate.
Format: bulleted list under each
of the 3 headers above.
AI produces structured, actionable analysis.
Ready-to-use prompt templates
Template 1: Formula generation
Template 2: Data analysis
Template 3: Data cleanup
Template 4: Formula explanation
Advanced prompt techniques
Trick 1: Chain of thought
For complex analysis, ask AI to "think step by step" or "walk through your reasoning before giving the answer." This forces AI to work through problems more carefully and often improves output quality.
Trick 2: Role priming
Starting with "You are a senior financial analyst" or "You are an Excel expert helping a beginner" primes AI to respond in that voice with appropriate depth. Useful for tuning explanations to your audience.
Trick 3: Few-shot examples
Instead of describing what you want abstractly, show 2-3 examples of the input-output pattern. AI extrapolates from examples remarkably well.
Trick 4: Iterative refinement
First prompt gets you a starting point. Second prompt refines: "Same thing but exclude weekends." Third refines further. AI keeps context within a conversation, so iterative refinement is fast and effective.
Trick 5: Ask for alternatives
"Give me two different approaches to this — one using XLOOKUP and one using INDEX-MATCH — with pros and cons of each." Useful when you're not sure which approach fits your workbook.
Excel Wizard — prompts inside Excel
Every step of the CRAFT framework works with any AI tool. Excel Wizard's ribbon interface adds one advantage: the tool knows it's talking to Excel, so you skip some of the context. "Give me a formula for sum by region" already assumes Excel and current workbook, streamlining prompts.
Includes a prompt library of pre-tested templates for common tasks. Works on Excel 2019+.
Try Excel Wizard →Frequently asked questions
What makes a good AI prompt for Excel?
Five elements: data context (where your data lives), request specificity (what you want), constraints (Excel version, function preferences), output format (shape of the response), and error handling instructions (how to deal with edge cases). Missing any of these forces AI to guess, which produces generic or wrong results. Adding all five turns a vague ask into a reliable, reproducible operation.
Should I use bullet points or paragraphs in prompts?
Both work. Bullets tend to produce cleaner responses for structured requests (formulas, tables, checklists). Paragraphs work better for open-ended analysis and exploration where you want the AI to think through the problem. Match the prompt structure to the expected output structure — a bullet-list prompt tends to get a bullet-list answer.
Do I need to be polite to AI?
Politeness doesn't affect output quality, but it costs nothing and some users find it changes their own mindset — treating AI as a collaborator rather than a vending machine tends to produce more thoughtful follow-ups. What actually matters for output quality is specificity, structure, and clarity — not politeness.
Should I include examples in my prompts?
Yes, whenever your data has unusual patterns. Providing two or three example rows dramatically improves AI's understanding of what your data looks like — especially for text formats, mixed data, or non-standard structures. For simple, clean data with descriptive column names, examples are less necessary.
How long should an AI prompt be?
Long enough to be specific, short enough to be readable. Most effective Excel prompts run 3-8 sentences. Too short and you leave AI guessing; too long and important details get buried. The sweet spot: describe data structure, state the request, add key constraints, specify output format. Anything else is often filler.