ChatGPT and Claude Prompts for Spreadsheet Work: A Copy-Paste Library
ChatGPT and Claude Prompts for Spreadsheet Work: A Copy-Paste Library
AI tools are genuinely useful for spreadsheet work — not because they replace knowing how formulas work, but because they dramatically reduce the time spent writing complex nested formulas, debugging #VALUE! errors, generating data validation rules, and writing Apps Script. The catch is that generic prompts produce generic results.
This library contains prompts tested against real spreadsheet problems, organized by task type.
Formula Writing
Pattern: Write a formula that does [specific task] in [column/cell]
Vague prompts produce vague formulas. Specific prompts produce formulas you can paste directly. Compare:
❌ Vague: “Give me a formula to look up data.”
✓ Specific prompt: I have a Google Sheet. Column A has employee IDs (text, like EMP-001). Column B has employee names. On a separate tab called ‘Payroll’, column C has the same employee IDs. In column D of the Payroll tab, I want to return the employee name from Sheet1. Write an XLOOKUP formula for cell D2 that I can copy down the column.
The prompt specifies the sheets, columns, data types, and output cell. No guessing required — you get a formula you can paste directly.
Pattern: Write a formula with specific error handling
Prompt: Write an Excel formula for cell C2 that divides B2 by A2. If A2 is zero or blank, return 0 instead of an error. If the result is greater than 1, format it as a percentage. I need this to work in Excel 2019.
Specifying the Excel version matters — XLOOKUP and LET don’t exist in 2019, and a good AI response will use compatible syntax.
Pattern: Combine multiple conditions
Prompt: Google Sheets formula for E2: return ‘High Priority’ if D2 is greater than 1000 AND C2 says ‘Overdue’, return ‘Review’ if D2 is greater than 500 OR C2 says ‘Overdue’, otherwise return ‘Normal’. Use IFS or nested IF — whichever is cleaner.
Debugging Errors
Pattern: Here is my formula and the error — explain why
Don’t just paste the formula and ask why it’s broken. Include the error message, describe the data in each referenced cell, and share what you expected vs. what you got.
Prompt: This VLOOKUP returns #N/A even though I can see the value exists: =VLOOKUP(A2, Sheet2!B:D, 2, 0). A2 contains the number 1042. Sheet2 column B also contains 1042 in row 5. The data in Sheet2!B was imported from a CSV. What are the most likely reasons this is failing and how do I fix each one?
Expected output: the AI will identify text-vs-number mismatch as the primary culprit for CSV data and give you a TEXT() or VALUE() conversion formula to test.
Pattern: My formula works in one cell but not when I copy it down
Prompt: This formula works correctly in C2: =VLOOKUP(A2, $F$2:$H$100, 2, 0). When I copy it to C3, it returns the wrong result. A3 contains a different lookup value. Explain what might cause this and show me the formula for C3 as it should appear.
Data Cleaning
Pattern: Write formulas to clean this specific type of dirty data
Prompt: I have a column of phone numbers in Google Sheets that came from a form. They’re in mixed formats: (555) 123-4567, 555-123-4567, 5551234567, +1 555 123 4567. I want to normalize them all to 555-123-4567 (no country code, no parentheses, dashes between groups). Write a formula that handles all these input formats.
This type of prompt is where AI tools save the most time — normalization formulas involving REGEXREPLACE are painful to write from scratch.
Pattern: Write a QUERY to filter/transform this data
Prompt: I have a Google Sheet with columns: A=Date, B=Region, C=Product, D=Units Sold, E=Revenue. Write a QUERY formula that returns rows where Region is ‘North’ or ‘West’, Date is in 2024, and Revenue is greater than 5000. Return only columns A, B, C, and E. Include a header row.
Apps Script
Pattern: Write a script that automates [specific task]
Prompt: Write a Google Apps Script that runs when I click a custom menu item. The script should look at column A for any cell with the text ‘DONE’, find the corresponding row, and move that entire row to a sheet called ‘Archive’. After moving, delete the row from the original sheet. The script should handle the case where the Archive sheet doesn’t exist yet.
Pattern: My Apps Script throws an error — explain and fix it
Prompt: This Apps Script throws ‘TypeError: Cannot read properties of undefined (reading getRange)’ on line 4. Here is the full script: [paste script]. The sheet I’m trying to access is called ‘Data’. Explain why this error occurs and show me the corrected script.
Templates and Data Architecture
Pattern: Design the data structure for [specific tracking need]
Prompt: I want to track freelance project invoices in Google Sheets. Each invoice has: client name, project name, invoice number, invoice date, due date, amount, status (Draft/Sent/Paid/Overdue), and payment date. I also want a summary view showing total outstanding, total paid this month, and overdue amounts by client. Design the tab structure and column layout. I’ll build it myself — I just want the architectural recommendation.
Asking for the architecture before building saves the pain of restructuring data after you’ve already entered 200 rows.