ChatGPT
Best ChatGPT Prompt for Excel & Data
Copy, customize, and use this ChatGPT prompt for Excel & Data.
Beginner
Why ChatGPT?
ChatGPT writes Excel and Google Sheets formulas with the best accuracy of any AI tool — it knows when to use XLOOKUP vs VLOOKUP, constructs nested IF logic correctly, and explains formulas in plain English. Also strongest for natural-language data analysis task descriptions.
Prompt Template
You are an expert Excel and Google Sheets analyst. You write formulas that work the first time, explain them clearly, and flag potential issues before they cause problems.
Task: {{task_description}}
Tool: {{tool}}
Data description: {{data_description}}
Column structure: {{column_structure}}
Provide:
1. **The formula** — exact, ready to paste, with correct cell references based on the structure I described
2. **Plain English explanation** — what the formula does, step by step, no jargon
3. **Where to place it** — which cell to start in and whether to drag/copy
4. **Potential issues** — edge cases that could break this formula (e.g. blanks, duplicates, case sensitivity)
5. **Alternative approach** — if there's a simpler formula for the same result, show it
If my data description is ambiguous, ask one clarifying question before writing the formula.
Do NOT write placeholder formulas. Write the actual formula using the column names and row numbers I provided. Example Output
Formula (paste in Sheet1, cell D2, then drag down to D51):
=XLOOKUP(B2, Sheet2!$A:$A, Sheet2!$B:$B, "Not found")
Show full example
Plain English: XLOOKUP looks at the salesperson name in B2, searches for that exact name in column A of Sheet2, and returns the corresponding commission rate from column B of Sheet2. If the name isn't found, it returns 'Not found' instead of an error.
Where to place it: Cell D2 in Sheet1. After pasting, click D2 and drag the fill handle down to D51.
Potential issues:
1. Spaces and capitalization: 'John Smith' won't match 'john smith' or 'John Smith '. Clean both columns with =TRIM(PROPER()) if you have inconsistent formatting.
2. Duplicate names in Sheet2: XLOOKUP returns the first match only. If you have two 'John Smith' entries in Sheet2, add a middle initial or ID column.
Alternative (if using Excel 2016 or older which lacks XLOOKUP):
=IFERROR(VLOOKUP(B2, Sheet2!$A:$B, 2, FALSE), "Not found")
VLOOKUP is slightly less flexible but works in all Excel versions.
Make it yours
Your Generated Prompt
Prompt copied! What's next?
Got your AI output? Make it better.
Paste what ChatGPT generated into Coda One and keep the next rewrite or cleanup pass in one browser flow.
Tips for Better Results
Always tell ChatGPT your exact column letters and row numbers — it can't see your spreadsheet, but with a precise column structure it will write formulas that paste correctly without modification.
Example (filled in)
Task: I have a sales table and a separate commission rates table. I want to automatically pull the commission rate for each salesperson based on their name.
Tool: Excel
Data: Sales table in Sheet1, Commission rates table in Sheet2
Column structure: Sheet1: A=Date, B=Salesperson Name, C=Revenue, D=Commission Rate (blank — this is where I want the formula). Sheet2: A=Salesperson Name, B=Commission Rate. Data starts row 2 in both sheets, 50 rows in Sheet1.