Claude Prompts for Excel Formulas That Actually Work

The fastest way to get a working spreadsheet formula from Claude is to describe your data in plain English, say exactly what result you want, and paste a few sample rows — then Claude returns the formula and explains it. Below are 13 copy-ready prompts for lookups (VLOOKUP, XLOOKUP, INDEX/MATCH), nested IFs, debugging broken formulas, and converting between Excel and Google Sheets. Replace the bracketed parts, and always test the result on a small sample before running it on your whole file.

The core pattern: describe, don't guess

You don't need to know whether the answer is SUMIFS or a pivot table. Describe the goal and let Claude pick the function. The template never changes: here's my data → here's what I want → here's the format I need it back in.

I have a spreadsheet in Excel. Column A is Order Date, B is Customer, C is Region, D is Amount. Row 1 is headers, data runs to row 500. I want a formula in F2 that adds up every Amount where Region is "West". Give me the formula, tell me exactly which cell to put it in, and explain each part in one line.

Here are 5 sample rows from my sheet (tab-separated, first row is headers): [PASTE ROWS]. I want to [DESCRIBE THE RESULT IN PLAIN ENGLISH]. Don't assume any columns I didn't list. If more than one approach works, give me the simplest one first and say when I'd want the other.

Lookups: VLOOKUP, XLOOKUP, and INDEX/MATCH

"Pull the price for this SKU from my other tab" is the most common spreadsheet task. Name your columns and tabs, say which value you're matching on, and ask for XLOOKUP if you're on a modern version — it's cleaner and doesn't break when columns move.

I have two tabs. On "Orders", column A is SKU. On "Catalog", column A is SKU and column D is Price. In "Orders" column B, I want to look up each SKU's price from Catalog. I'm on Microsoft 365, so use XLOOKUP. Show me the formula for B2 and tell me what it returns when the SKU isn't found.

Rewrite this XLOOKUP as an INDEX/MATCH so it works in my older Excel 2016: [PASTE FORMULA]. Keep the "not found" fallback, use absolute references so I can fill it down, and explain why INDEX/MATCH is more resilient than VLOOKUP here.

I need a lookup on TWO conditions: match both Region = value in A2 AND Rep = value in B2, and return the Sales figure from my "Data" tab. Give me one version with XLOOKUP and one with INDEX/MATCH, and tell me which is safer if a combination appears more than once.

A correct answer to prompt 3 looks like =XLOOKUP(A2, Catalog!$A:$A, Catalog!$D:$D, "Not found"), and the INDEX/MATCH from prompt 4 like =INDEX(Catalog!$D:$D, MATCH(A2, Catalog!$A:$A, 0)). The absolute ranges let the formula survive a fill-down — a detail worth asking for explicitly.

Nested IFs and conditional logic

Grading bands, commission tiers, status labels — these turn into unreadable nested IFs fast. Ask Claude for the modern IFS form and give it the exact thresholds.

In column C I have a score 0–100. In D I want a letter grade: 90+ = A, 80–89 = B, 70–79 = C, 60–69 = D, below 60 = F. Give me an IFS formula (I'm on Microsoft 365), and also show the classic nested IF version in case I share the file with someone on an older Excel.

Turn this word problem into one formula: if Status is "Paid" show the Amount, if Status is "Refunded" show 0, and if Status is anything else show "Review". Status is in column B, Amount in column C. Explain the order of the conditions so I understand why it matters.

A clean answer to prompt 6 is =IFS(C2>=90,"A", C2>=80,"B", C2>=70,"C", C2>=60,"D", TRUE,"F"). The TRUE at the end is the catch-all — a good prompt gets Claude to point that out, not leave you to find it when a blank cell errors.

Debug a formula that's broken

When a formula throws #N/A, #REF!, or the wrong number, paste the formula and one row of the real data — most errors are data problems (a trailing space, text that looks like a number), not formula problems.

This formula returns #N/A and I don't know why: [PASTE FORMULA]. Here's the lookup value in A2 and the first few rows of the range it's searching: [PASTE]. Tell me the most likely cause, how to confirm it in 10 seconds, and the corrected formula.

Explain this formula to me in plain English, one argument at a time, then tell me what would make it break: [PASTE FORMULA]. I inherited this workbook and I'm afraid to touch it.

My VLOOKUP worked until someone inserted a column, and now it returns the wrong values. Here's the formula: [PASTE]. Explain why inserting a column broke it, then rewrite it so a future column insert can't break it again.

The honest answer to prompt 10 is that VLOOKUP's hard-coded column number (the 4 in =VLOOKUP(A2, Catalog!$A:$D, 4, FALSE)) doesn't move when a column is inserted — which is exactly why Claude will steer you to XLOOKUP or INDEX/MATCH, both of which reference the price column by name or position, not by count.

Convert between Excel and Google Sheets

Most functions are identical across the two apps, but a handful aren't — structured table references, some dynamic-array quirks, and ARRAYFORMULA, which Sheets needs and Excel doesn't. Always name the target app.

Convert this Excel formula to work in Google Sheets exactly: [PASTE FORMULA]. If Sheets needs a different function or an ARRAYFORMULA wrapper, use it, and flag anything that behaves even slightly differently between the two so I know what to spot-check.

I'm moving a workbook from Google Sheets to Excel. Here are the formulas I'm worried about: [PASTE]. For each, tell me: works as-is, needs a tweak (show it), or has no Excel equivalent (suggest the closest approach).

Always verify on a small sample

This is the step that separates people who trust AI formulas blindly from people who ship correct numbers. Before you fill a formula down 10,000 rows, prove it on rows you can check by hand.

Give me a 5-row test to prove this formula is correct before I apply it to my whole sheet: [PASTE FORMULA]. Include one normal row, one edge case (blank or zero), and one row that should trigger the "not found" or error path, with the exact result I should see for each.

Compare those five results against what you calculated by hand, and only then fill down. If Claude's expected result and your sheet ever disagree, that gap is the bug — send both back and ask which side is wrong.

How to get better results

• Lead with the layout. "Column A is Date, B is Amount, row 1 is headers, data to row 900" removes all guessing. This one sentence is worth more than any clever phrasing.
• Paste real sample rows. Three to five actual rows (with any private data swapped for fakes) let Claude see whether your "numbers" are really text, whether dates are dates, and how your values are formatted.
• Name your app and version. "Microsoft 365," "Excel 2016," or "Google Sheets" decides whether you get XLOOKUP, a nested IF, or an ARRAYFORMULA.
• Ask for the explanation, not just the formula. "Explain each argument in one line" turns a black box into something you can edit yourself next time.
• Say where it goes. Asking "which cell do I put this in and can I fill it down?" catches absolute-vs-relative reference mistakes before they multiply.

Mistakes to avoid

• Pasting formulas without data. Claude can guess at a broken formula, but a debug is only reliable when it can see one row of the values the formula is actually chewing on.
• Trusting it on the full sheet before the sample. A formula can be 99% right and still be wrong on the one row shaped differently from the rest. Verify small, then scale.
• Uploading confidential data you don't need to. For a single formula, a described layout plus fake sample rows is enough. Don't paste customer PII or a client's private metrics into any AI tool.
• Accepting the first formula when a simpler one exists. If Claude hands you a 200-character nested monster, ask "is there a simpler, more readable way?" — often there is.
• Ignoring which app you're in. An Excel formula pasted into Sheets that looks like it worked may be silently mis-calculating. When you switch apps, run the convert prompt.

That's the whole method: load your real layout, scope one clear task, ask for the formula plus an explanation, then prove it on a five-row sample. Browse the full Claude AI Class learn hub for more, start with the ten-minute install guide if you're brand new, or apply the same pattern to your resume and job search.