What Claude can and cannot do with spreadsheets
Before we get into prompts, set expectations, because half the frustration people have with Claude and spreadsheets comes from asking it to do something it was never going to do.
Claude does not live inside your Excel or Google Sheets file. It cannot see your workbook, click your cells, or run a macro for you on its own. What it does extremely well is three things: it writes and explains formulas, it transforms data you paste into it, and it generates scripts you can run yourself to automate repetitive work. Once you stop expecting it to reach into your file and start treating it as the person sitting next to you who is very good at formulas, it becomes genuinely useful.
Everything below is copy-paste. You describe what you have and what you want, Claude hands back the formula or the cleaned data, and you paste the result where it belongs.
Getting a working formula on the first try
The difference between a formula that works and one that returns #VALUE! is almost always in how you describe your data. Claude cannot see your columns, so you have to tell it.
Give it three things: what is in each column, what you want to calculate, and one sample row. Here is the pattern:
I have a Google Sheet. Column A is order date, column B is customer name, column C is amount in dollars. I want column D to show the amount only if the order was in the current month, otherwise blank. Row 2 is a real example: 2026-08-03, Priya Nair, 240. Give me the formula for D2 that I can drag down.
That prompt gets you a real, working answer instead of a generic one:
=IF(AND(YEAR(A2)=YEAR(TODAY()), MONTH(A2)=MONTH(TODAY())), C2, "")
The reason this works is the sample row. It tells Claude your dates are real dates and not text, which changes the formula it gives you. Without the sample, it guesses, and its guess is often wrong for your specific sheet.
This is where Claude earns its keep for people who are not formula experts. A nested lookup that would take you twenty minutes and three Stack Overflow tabs comes back in one paste, already matched to your column layout.
Cleaning and reformatting messy data
The second thing Claude is very good at is taking ugly data and handing it back clean. You paste the mess, you describe the shape you want, it does the transformation.
Say you have a column of phone numbers entered by hand, so some have dashes, some have spaces, some have country codes and some do not. Paste twenty of them and ask:
Here are phone numbers entered inconsistently. Standardize every one to the format +1 (XXX) XXX-XXXX. If a number has no country code, assume +1. Return them as a single column I can paste back into my sheet, same order.
Claude returns a clean column in the same order, ready to paste. The same approach works for splitting a full-name column into first and last, standardizing inconsistent addresses, deduplicating a list, or converting dates from one format to another. For anything under a few hundred rows, pasting the data straight into Claude is faster than writing the formula, and you can see the result before you commit it.
One honest limit: this is best for small-to-medium data. If you are cleaning fifty thousand rows, do not paste all of them. Instead, ask Claude for the formula or the script that does the cleaning, and run that against the full set yourself. Which brings us to the last two sections.
Explaining a formula someone else wrote
This is the use case operators love and rarely think to try. You inherited a spreadsheet. There is a formula in it that looks like a keyboard fell on the screen, and you are afraid to touch it. Paste it into Claude:
Explain this Google Sheets formula in plain English, step by step, and tell me what would break it: =ARRAYFORMULA(IFERROR(VLOOKUP(B2:B, Sheet2!A:C, 3, FALSE), "not found"))
You get a plain breakdown: it looks up every value in column B against the first column of Sheet2, pulls the third column when it finds a match, and writes "not found" instead of an error when it does not. Then it tells you the fragile part, which here is that inserting a column in Sheet2 would silently change which column gets pulled.
Claude is a spreadsheet tutor as much as a formula generator, and for a lot of people the tutor half is more valuable. You stop being scared of your own file.
Automating repetitive spreadsheet work with scripts
When a formula is not enough, because you want something to happen on a schedule or across many sheets, Claude writes the script. In Google Sheets that means Apps Script. In Excel it means Office Scripts or VBA.
Suppose you want a button that emails you a summary of any row where the amount is over 1000. Ask:
Write a Google Apps Script for my sheet. The data is on a tab named Orders, with amount in column C and email in column D. When I run it, for every row where column C is greater than 1000, send an email to the address in column D with the subject "Large order" and the amount in the body. Keep it beginner-safe and tell me exactly where to paste it.
Claude gives you the script and the steps: open Extensions, then Apps Script, paste, save, run, and approve the permission prompt the first time. The script itself is short and readable:
function emailLargeOrders() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Orders");
const rows = sheet.getDataRange().getValues();
for (let i = 1; i < rows.length; i++) {
const amount = rows[i][2];
const email = rows[i][3];
if (amount > 1000 && email) {
MailApp.sendEmail(email, "Large order", "Order amount: " + amount);
}
}
}
The key phrase in the prompt is "keep it beginner-safe and tell me where to paste it." Without it, Claude often assumes you already know your way around the script editor. With it, you get the hand-holding, and a task that used to be a manual half hour every week becomes one click.
The prompt pattern that makes the difference
Everything in this guide comes down to one habit. When you ask Claude to do anything with a spreadsheet, give it your column headers, one real sample row, and the exact output you want. That single move is the difference between a formula that works on the first paste and ten minutes of back and forth.
Compare the two:
Vague: "write me a formula to flag big orders." Claude has to guess your columns, your data types, and what "big" means.
Specific: "Column C is order amount as a number. Flag any row over 1000 with the word HIGH in column E. Sample: C2 is 1450. Give me the E2 formula." There is nothing left to guess, so the first answer is the right one.
Where to go from here
Spreadsheets are one job where the right prompt saves real time. Once you feel that, the obvious next question is which other everyday tasks have a proven prompt sitting behind them, so you are not reinventing the wording every time.
That is exactly why we built the Claude Cheat Sheet. It is a set of tested prompt codes for the jobs people actually do, each one with the before and after so you can see what it changes. If the spreadsheet examples above were useful, the Cheat Sheet is the same idea applied across the rest of your work. And if you would rather start free, the 75-page Claude guide covers the setup and the prompt patterns first, no card required.