AI Spreadsheet Prompts: 10 Copy-Paste Templates

Most spreadsheet problems aren't really spreadsheet problems. They're "I stared at this VLOOKUP error for 45 minutes and now I hate Excel personally" problems. AI spreadsheet prompts fix that. Not by being magic, but by being faster than a Google search and more specific than Stack Overflow.

This article is a copy-paste kit. Ten prompts you can use today in ChatGPT or Claude, a formula for writing your own, and a few reminders about what not to hand over to a chatbot.

The prompt formula that makes AI spreadsheet help actually useful

AI is not psychic. If you type "my VLOOKUP is broken" you'll get a generic explanation of how VLOOKUPs work, which you didn't ask for. What you need is context.

The formula is simple: role + context + task + constraints + format.

Concretely: "You are a spreadsheet expert. I have a Google Sheets file with sales data in columns A through F. Column A has order dates, column D has salesperson names, column F has revenue. Write a SUMIF formula that totals revenue for one specific salesperson. Use column D as the criteria range and F as the sum range. Show me the formula and explain each argument."

That's it. Five pieces of information. You get a formula you can actually paste.

This is Rule #13 from Don't Replace Me: garbage in, garbage out. Treat AI like a smart intern who's fast, tireless, and occasionally overconfident. Give them the context they need to do the job right.

10 copy-paste AI spreadsheet prompts

Prompt 1: Explain a formula you didn't write

Inherited a spreadsheet from a colleague who left the company? Classic. Paste this:

You are a spreadsheet expert. Explain this formula in plain English, step by step. Tell me what it does, what each argument means, and what it would return if any referenced cells were empty or contained errors.

Formula: [paste formula here]

Replace the bracket with your actual formula. Ask it to use plain language, not jargon. If the explanation doesn't make sense, reply with "simplify that" and try again.

Prompt 2: Write a lookup formula from scratch

You are a spreadsheet expert. I have an Excel file. Sheet1 has a list of employee IDs in column A and their department names in column B. Sheet2 has a column of employee IDs in column A, and I need column B to show the matching department name from Sheet1. Write a formula for Sheet2 cell B2 that will work when copied down. Use XLOOKUP if available, otherwise VLOOKUP. Explain what each part does.

Swap in your actual column letters and sheet names. The explanation matters because you need to understand what you're pasting.

Prompt 3: Clean messy exported data

Data exports from CRMs, accounting tools, and booking systems are almost always filthy. Phone numbers with inconsistent formatting, names in all caps, extra spaces nobody asked for.

You are a data cleaning expert in Google Sheets. I have a column of phone numbers in column C, rows 2 through 500. The numbers are inconsistently formatted: some have parentheses, some have dashes, some have country codes, some have spaces. I want all of them formatted as plain 10-digit numbers with no punctuation or spaces, like 5551234567. Write a formula I can put in a helper column that will clean each number. If a cell is empty, return empty.

Prompt 4: Categorize rows using logic

You are a spreadsheet expert. I have an Excel file with a column of invoice amounts in column D (numbers only, no currency symbols). I want a formula in column E that labels each row as "Small" if the amount is under 500, "Medium" if it's 500 to 4999, and "Large" if it's 5000 or more. Write a nested IF formula or IFS formula for cell E2 that I can copy down.

Simple, but writing nested IFs from memory at 4pm on a Friday is how typos happen. Just ask.

Prompt 5: Find duplicate values

You are a spreadsheet expert. I have a Google Sheets file with a list of email addresses in column B, starting at row 2. I want to flag any email address that appears more than once. Write a formula for column C that returns "Duplicate" if the email in that row appears more than once in column B, and returns blank if it's unique. Show me the formula and explain how COUNTIF is being used.

Good for client lists, inventory codes, order numbers. Any situation where duplicates are a problem.

Prompt 6: Summarize a table in plain English

This one is different. You're not asking for a formula. You're asking AI to read your data description and tell you what's worth knowing.

You are a business analyst. I have a spreadsheet summary: [paste a small, anonymized table or describe the structure and totals here]. In plain English, tell me what patterns or observations stand out. Don't make up numbers. Only comment on what I've shared. List your observations as bullet points.

Important: Only paste data that has no personally identifiable information, no client names, no financial data that's confidential. Anonymize before you paste. The rule is: would you be comfortable if this appeared on a shared screen in a meeting? If no, clean it first.

Prompt 7: Build a pivot table plan

Before you click into the pivot table builder, figure out what you actually want.

You are a spreadsheet expert. I have a sales data table in Excel with these columns: Order Date, Region, Product Category, Salesperson, Units Sold, Revenue. I want to analyze monthly revenue by region and identify which salesperson has the highest revenue per region. Describe the pivot table I should build: what goes in rows, columns, values, and filters. Then tell me if there's a simpler formula-based approach that would give me the same answer.

You can then build it yourself with a clear plan, or paste the pivot setup instructions and follow them.

Prompt 8: Debug a #N/A or #VALUE! error

Every spreadsheet user has stared at a red error cell with the energy of someone who wants to close the laptop and go outside.

You are a spreadsheet expert. My VLOOKUP formula is returning #N/A but I'm sure the lookup value exists in the source table. Here is my formula: [paste formula]. Here is a description of my data: [column letters, data types, whether there are any leading/trailing spaces, whether the ID is numeric or text]. Walk me through the most common reasons this error appears and what to check first. Then suggest a modified formula that handles the error gracefully using IFERROR.

Nine times out of ten it's a trailing space or a number stored as text. But now you'll know which one.

Prompt 9: Turn a business question into spreadsheet steps

Sometimes the problem isn't the formula. It's that you don't know what formula to use, or whether a formula is even the right tool.

You are a data analyst. I have a question I want to answer using a spreadsheet: [describe your business question in plain English, e.g., "which product sold the most units in Q1, and how does that compare to Q4 of last year?"] My spreadsheet has these columns: [list them]. Tell me step by step how to answer this question using formulas, a pivot table, or both. Don't assume I know advanced functions. Explain each step.

This is the prompt most people don't think to use. It turns vague requirements into an actual plan. For more templates like this, the general AI prompts guide has 12 you can adapt for any work task.

Prompt 10: Build a QA checklist before you share the numbers

You've built the spreadsheet. Now you're about to send it to your manager, your client, or the whole team. This prompt helps you not embarrass yourself.

You are a spreadsheet auditor. I am about to share a financial summary spreadsheet. The spreadsheet contains [describe briefly: what it tracks, time period, audience]. Create a QA checklist of the most important things I should verify before sending: formula accuracy, broken references, hardcoded numbers that should be formulas, inconsistent date formats, totals that don't match their source data, and anything else a reviewer might catch. Format as a checklist I can print or copy.

Run through this before every significant send. It takes three minutes and saves you from the "actually there's an error in row 47" reply.

How to write your own AI spreadsheet prompts

The ten prompts above cover the most common situations. But your spreadsheet is not a common spreadsheet. It has weird inherited logic, columns named things like "Final_FINAL_v3," and at least one formula referencing a tab that no longer exists.

So here's how to write your own prompt from scratch, using the role-context-task-constraints-format structure.

Role. Start with "You are a spreadsheet expert" or "You are a data analyst." This tells the model to stop being a generalist and start being specific. It matters more than it sounds.

Context. Describe your data. Not the whole file, just the relevant columns. Column letters, what's in them, whether the data is text or numbers or dates. "Column A is order dates formatted as MM/DD/YYYY, column B is product SKU as a text string, column C is units sold as a whole number." That's enough.

Task. Say exactly what you want. Not "help me with my formula" but "write a formula that counts how many rows have a date in column A that falls within the last 30 days."

Constraints. This is where most people skip ahead and regret it. Does it need to work in Excel 2019? Then XLOOKUP is off the table. Are you in Google Sheets? Then certain Excel-only functions won't work. Do you need the formula to handle blank cells gracefully? Say so. Every constraint you include is a failure mode you're preventing.

Format. Tell it how to give you the answer. "Show me the formula, then explain each argument in plain English, then show me an example of what it would return if column A had the date 03/15/2025." Specific format requests get specific answers.

Write prompts this way and you'll get something usable on the first try about 80% of the time. The other 20% is iteration: copy the formula into your sheet, see what breaks, describe the breakage, and ask again. Two rounds is almost always enough.

What not to do with these prompts

AI is useful here, but it's not infallible and it's not a vault.

Do not paste confidential data. That means: client names, employee records, payroll figures, HR data, health information, legal documents, financial forecasts marked confidential, or anything your company's data policy says stays internal. Anonymize first. Use fake names and rounded numbers for structure, then apply the real data yourself once you have the formula.

Do not send AI-generated analysis you can't explain. If your manager asks "how did you get this number?" and your answer is "the chatbot calculated it," that's a problem. For more on what AI actually can and can't do under the hood, this plain-English explainer is worth five minutes.

Verify every formula before it touches live data. AI gets cell references wrong. It sometimes uses functions that don't exist in older versions of Excel. It occasionally hallucinates a syntax that looks plausible but isn't. Test in a dummy row first.

Check your dates. Date formatting is where AI suggestions go sideways most often, especially when mixing Excel and Google Sheets conventions. Always verify.

Don't skip the explanation. Every prompt above asks AI to explain what it wrote. That's not just for learning. It's how you catch errors. If the explanation doesn't match your data structure, the formula is wrong.

When AI spreadsheet prompts save you time and when they don't

The honest answer on timing: AI is fastest on well-defined, language-shaped problems. Writing a formula you know the logic for but can't get the syntax right. Cleaning data with a consistent structure. Debugging an error where you can describe exactly what you're seeing. These are the sweet spots.

Where it slows you down is when the problem isn't actually a formula problem. If your data is genuinely inconsistent, if the business logic hasn't been agreed on yet, or if the spreadsheet is doing something it was never designed to do, AI will cheerfully write you formulas that produce wrong answers very efficiently. That's worse than the original problem.

A useful question to ask before prompting: can I describe this problem clearly in one paragraph? If yes, AI can probably help. If you spend five minutes trying to write the prompt and keep restarting, the problem is probably not the formula. It's the underlying data or the underlying question.

The deeper skill isn't memorizing prompts. It's knowing when the problem is a formula problem versus a judgment call versus a "this data is actually broken" problem that no formula will fix. AI can help with the first. You handle the other two.

If you want to get better at this generally, the starter guide to using AI at work covers the mindset shift that makes every prompt better, not just spreadsheet ones. And for the non-technical people who want a broader view of which AI skills actually matter in 2026, without having to learn to code, this breakdown is a realistic look at what's worth your time.

Frequently asked questions

Can I use ChatGPT or Claude to write Excel formulas?

Yes, both work well for writing and explaining formulas. Give them specific context: what your columns contain, what data types, and what result you want. The more detail you provide, the less back-and-forth you'll need. Always test the formula before applying it to real data.

Is it safe to paste my spreadsheet data into ChatGPT?

It depends on what's in it. Never paste confidential employee records, client data, payroll figures, financial forecasts marked private, or anything your company's data policy restricts. Anonymize sensitive details before using them as examples. When in doubt, describe the structure without the real values.

Why does the formula AI gives me return an error?

Usually one of three things: the cell reference doesn't match your actual data layout, a number is stored as text (or vice versa), or the function isn't available in your version of Excel or Sheets. Include your column letters and data types in the prompt, ask for an IFERROR wrapper, and test on one row before copying down.

Do I need any special AI tools for spreadsheet help?

No. A free ChatGPT account or Claude's free tier handles all of these prompts. You don't need a spreadsheet-specific AI plugin or a $97 course. The quality of the output depends almost entirely on how well you describe the problem, not which tool you use.

What's the best way to prompt AI for spreadsheet help?

Use the role-context-task-constraints-format structure. Tell it you want a spreadsheet expert, describe your data layout, specify the exact task, add any constraints (like compatibility with Google Sheets or Excel 2019), and say what format you want the answer in. That one habit eliminates about 80% of useless responses.

Can AI replace knowing how to use spreadsheets?

No. It's a faster way to get syntax and explanations, but you still need to understand your data, know when a number looks wrong, and be able to explain your work to someone else. If you want to understand where AI genuinely helps versus where human judgment is irreplaceable, the relevant chapter in Don't Replace Me covers exactly that distinction.