Claude Prompts for Excel Formulas and Spreadsheets

The short version: Claude is good at writing Excel formulas when you give it the sheet structure, the exact column letters, and a plain English description of what you want, but it will confidently write formulas that look right and calculate wrong if you do not tell it which version of Excel you are using. Treat it as a fast first draft, not a finished formula, and always test it on a row where you already know the answer.

Why Claude is worth using for spreadsheets at all

I use Claude most days for client work and spreadsheets are one of the places it earns its keep without any fuss. You type what you want in plain English, it hands you a formula, you paste it in. No forum searching, no scrolling through someone's half-answered question from 2014 about VLOOKUP breaking when a column gets inserted.

But here's where it gets interesting: the quality of what you get out is almost entirely down to the quality of what you put in. Vague prompt, vague or wrong formula. Specific prompt with real column references, usually a formula that works first time.

If you want the install and setup side sorted first, I've covered that separately in Claude for Excel: how to install it and prompts to try. This post is about the prompts themselves and the mistakes people make with them.

The one habit that fixes most bad results

Before you ask Claude for anything, paste in a small sample of your actual data, including the header row, as plain text or a simple table. Not a description of your data. The actual data, or a close copy of it with the sensitive bits swapped out.

Say you run a small events business and you have a spreadsheet of 400 bookings with columns for Client Name, Event Date, Deposit Paid, Balance Due, and Status. If you ask Claude "write me a formula to find overdue balances" with no data attached, it will guess at your column layout and give you something generic that references column A, B, C in an order that is not yours. You will then spend ten minutes figuring out why it is pulling the wrong cell.

Instead, give it this:

• The exact column headers and their letters (A: Client Name, B: Event Date, C: Deposit Paid, D: Balance Due, E: Status)
• Three or four sample rows, including at least one row that should trigger the result you want
• The exact outcome you need, stated as a sentence, not a vibe

A prompt that works: "Here are my columns: A Client Name, B Event Date, C Deposit Paid, D Balance Due, E Status. Write a formula for column F that returns 'Chase payment' if Event Date is within 14 days and Balance Due is greater than 0, otherwise return blank. I'm using Excel for Microsoft 365."

That last sentence matters more than people think.

The version problem nobody mentions

This is the bit most guides skip over because it is slightly inconvenient to admit: Claude does not know which version of Excel you have open unless you tell it, and it will happily write you a formula using functions that do not exist in your version. XLOOKUP, dynamic arrays with spill ranges, LET, and TEXTSPLIT all work beautifully in Excel for Microsoft 365 and the current desktop builds, and all throw a #NAME? error in older perpetual licence versions like Excel 2016 or 2019.

I have seen people assume the formula is broken, or that Claude "doesn't work," when the formula was fine and the Excel version just could not run it. So the fix is simple and it costs you one extra sentence in every prompt: state your Excel version, every time. "I'm using Excel 2019" or "I'm using Excel for Microsoft 365" or "I'm using Google Sheets." That one line saves you the most common wasted hour in this whole process.

Google Sheets is its own separate issue too. Claude will sometimes blend Excel and Sheets syntax if you do not specify, which means you get ARRAYFORMULA mixed with IFERROR in a way that works in neither tool cleanly. Say which one you are in, every single time, even if it feels repetitive.

Worked example: a formula that goes wrong on purpose, then gets fixed

Say you run a small online shop and you want to flag which of your 200 products need reordering. You have columns A Product Name, B Stock Level, C Reorder Point, D Supplier Lead Time (days), and you want column E to say "Order now" if stock is below the reorder point, and "Order soon" if stock is within 20 percent above it.

A weak prompt: "write a formula to tell me what to reorder." You will get something that checks B against C with a basic IF statement and misses the "order soon" buffer zone entirely, because Claude had to guess what "soon" meant and guessed wrong.

A strong prompt: "Columns are A Product Name, B Stock Level, C Reorder Point, D Supplier Lead Time. In column E, write a formula that returns 'Order now' if B is less than or equal to C, 'Order soon' if B is between C and C times 1.2, and blank otherwise. I'm using Excel for Microsoft 365, formula needs to go in E2 and fill down to row 200."

That second version gives Claude everything it needs: the comparison logic, the exact threshold maths, the cell range, and the Excel version. The formula that comes back will typically look something like a nested IF using AND for the middle condition, and it will work on the first paste, not the third.

Prompts worth stealing for common spreadsheet jobs

These are the prompt structures I find do the most work with the least back and forth:

• "I have a list of dates in column A. Write a formula for column B that calculates how many working days have passed since that date, excluding weekends and UK public holidays. I'm using Excel for Microsoft 365."
• "My data is in A1:D500 with headers in row 1. Write a formula to pull the three highest values from column D along with their matching names from column A, without using a pivot table. State the Excel version this needs."
• "Explain what this formula does, step by step, as if I've never seen a nested IF before: [paste formula]."
• "I need to combine text from columns A and B with a comma between them, but only if column C says 'Active'. Write the formula and tell me if it will work in Google Sheets or only Excel."
• "Write a formula that checks column A against a list in column F and returns 'Duplicate' if it appears more than once, counting case-insensitively."

Notice the pattern: every one of those names the exact columns, states the condition in full sentences, and asks for or states the platform. That is the whole trick, repeated five different ways.

Where Claude is better than a Google search

The thing Claude does that a search engine cannot is explain its own formula back to you in plain English, which means you can check its logic before you trust it with 500 rows of real client data. Paste the formula back and ask "walk me through exactly what each part of this does" and you will usually spot straight away if it has misunderstood your conditions.

It is also quietly good at converting formulas between Excel and Google Sheets, and at converting an old-style formula someone else built years ago into something readable, which matters more than people admit because most small business spreadsheets are a graveyard of formulas nobody currently working there wrote or understands.

Where it still falls over

Claude cannot see your actual live spreadsheet unless you are using it through a connected tool, so if your sheet has merged cells, hidden columns, or named ranges it does not know about, it will write formulas that assume a clean, simple grid. Merged cells especially cause trouble, formulas written for a clean range often break or reference the wrong cell when they hit a merged block, so unmerge first if you can.

It also cannot test the formula for you. It will tell you it should work, with confidence, and sometimes it is wrong about edge cases, particularly around blank cells, text formatted as numbers, and dates stored as text rather than actual date values. Always run it against one row where you already know the right answer before you trust it on the rest.

If you are trying to get a whole team using Claude across spreadsheets and daily tasks, not just the odd formula, it is worth reading how to use Claude Cowork for first tasks, and if you are deciding whether to roll Claude out across the business at all, what to switch on first and the catches is the more honest starting point than most of the official marketing.

When it is worth paying someone instead

If your spreadsheets are the actual backbone of how your business runs, pricing, stock, forecasting, payroll inputs, and you are stitching together Claude prompts on your own at 11pm, there is a point where it is cheaper to pay for a few hours of real setup than to keep patching formulas that half work. That is what an AI consultant for small business is for: not replacing Claude, but setting the system up once so your team is using it correctly and consistently instead of everyone prompting differently and getting different answers from the same data.

Frequently asked questions

Can Claude write Excel formulas accurately?

Yes, often very accurately, but only when you give it the exact column letters, sample data, and your Excel version in the prompt. Vague prompts produce formulas that look plausible and reference the wrong cells.

Does Claude know which version of Excel I'm using?

No, it does not know unless you tell it, and this is the single biggest cause of formulas that throw errors. Always state whether you're using Excel for Microsoft 365, an older perpetual version, or Google Sheets.

Is Claude better than ChatGPT for Excel formulas?

Both can write similar formulas; the bigger difference is in how well each explains its own logic back to you. Test both on a prompt that includes your real column structure and see which explanation you understand.

Can Claude see my actual spreadsheet?

Only if you're using it through a connected tool or plugin designed for that; otherwise it only sees what you paste in, so merged cells, hidden columns, and named ranges it can't see will often break the formula it gives you.