ChatGPT Prompts for Excel: 10 Formulas & Fixes (2026)
Excel's formula syntax is precise and unforgiving. One misplaced argument, one column offset, one data-type mismatch — and you get a cryptic error code instead of an answer. ChatGPT eliminates most of that friction: describe what you want in plain English, and it writes the formula, explains every argument, and debugs the error. The catch is that generic prompts produce generic results. This guide gives you 10 structured, copy-paste prompts for the Excel tasks that trip people up most.
At a Glance
Prompts inside10 copy-ready templates
StructureRole · Context · Task · Format
Best forFormula generation, error debugging, VBA macros
Copy-readyYes — fill [brackets] and run
How to Prompt ChatGPT for Excel (The Right Way)
The single most effective thing you can do when asking ChatGPT for Excel help is describe your column layout and include sample values. ChatGPT writes syntactically correct formulas for any scenario — but without knowing that Column A contains text product codes and Column B has numbers formatted as currency, it defaults to generic examples that break on your actual data. Give it the data context first; the formula output improves dramatically.
The four-element prompt structure — Role, Context, Task, Format — works particularly well for Excel because it maps directly onto what ChatGPT needs to give you a usable result. The Context element is where you paste your column layout. The Format element is where you specify whether you want a formula only, a formula with an explanation, or a step-by-step breakdown. Without those, you'll get a generic answer that needs a second round of clarification.
Key principle: Always tell ChatGPT your column letters and row numbers, and whether your data is text or numbers. "Column A has Order IDs as text strings, Column B has Customer Names" is enough context for it to write a working VLOOKUP. "Give me a VLOOKUP" is not.
1. Formula Generation — Write a Formula from Scratch
(Role) You are an Excel expert.
(Context) My spreadsheet has: Column A = Product Name, Column B = Units Sold, Column C = Unit Price. Data starts in row 2, headers in row 1.
(Task) Write a formula for Column D that calculates total revenue (Units Sold × Unit Price). Also write a formula for cell E2 that sums all revenue where Product Name contains the word "Widget" anywhere in the cell.
(Format) Each formula on its own line with a 1-sentence plain-English explanation. No VBA — spreadsheet formulas only.
Role Excel expert
Context column layout + data types
Task two specific formulas
Format formula per line + 1-sentence explanation
2. Formula Explanation — Decode an Existing Formula
(Role) You are an Excel trainer explaining formulas to a non-technical colleague.
(Context) Here is a formula I found in my spreadsheet: [paste your formula here]. I'm not sure what each part does.
(Task) Explain this formula in plain English. Break it down argument by argument, including any nested functions. Tell me what happens if any of the referenced cells are blank or contain an error value.
(Format) Numbered breakdown — one entry per argument or nested function. End with a single-sentence plain-English summary of what the whole formula does.
Role Excel trainer
Context paste your formula
Task argument-by-argument explanation
Format numbered breakdown + plain summary
3. Debug an Error — Fix #N/A, #VALUE!, or #REF!
(Role) You are an Excel debugging assistant.
(Context) I have this formula: [paste your formula]. It is returning a [#N/A / #VALUE! / #REF!] error. The data it references is in [describe the range — e.g., "Sheet2, Column A has product codes stored as text strings, not numbers"].
(Task) Diagnose the most likely causes of this error and provide a corrected formula for each possible cause. Also explain how to prevent this error type in future formulas.
(Format) Numbered list of likely causes, most probable first. For each: 1-sentence explanation + corrected formula. Add a 1-line prevention tip at the end.
Role debugging assistant
Context formula + error type + data description
Task diagnose + correct + prevent
Format numbered causes, most likely first
ChatGPT Formula Response — What It Looks Like
ChatGPT — Formula Generation Response
You
(Role) You are an Excel expert.
(Context) Column A = Product Name (text), Column B = Units Sold (number), Column C = Unit Price (currency). Data starts in row 2.
(Task) Write a formula for D2 that calculates total revenue, and a formula for E2 that sums revenue where Product Name contains "Widget."
(Format) Each formula on its own line with a 1-sentence explanation. No VBA.
ChatGPT
Revenue formula (D2) — multiply and copy down the column:
=B2*C2
Multiplies Units Sold (B2) by Unit Price (C2) to return the revenue for that row; drag it down to apply to every product row.
Conditional sum formula (E2) — sum all "Widget" revenue:
=SUMIF(A:A,"*Widget*",D:D)
Scans all of Column A for cells that contain "Widget" anywhere in the text (the asterisks are wildcards), then sums the corresponding values in Column D.
Note: SUMIF is not case-sensitive, so "widget" and "WIDGET" both match. If you need case-sensitive matching, you'll need a SUMPRODUCT formula — let me know and I'll write that version.
VLOOKUP vs XLOOKUP: Prompts That Get the Right One
Use XLOOKUP if you're on Excel 365 or Excel 2021 — it searches in any direction, handles errors natively with a built-in [if_not_found] argument, and is easier to read. Use VLOOKUP if the workbook will be opened by anyone on Excel 2019 or earlier, since XLOOKUP is not backward compatible. Tell ChatGPT which version you need and why; it will write the correct syntax without guessing.
Feature
VLOOKUP
XLOOKUP
Search direction
Left to right only
Any direction (left, right, up, down)
Error handling
Requires wrapping in IFERROR()
Built-in [if_not_found] argument
Column index
Number (fragile — breaks if columns shift)
Return range (robust — references the column directly)
Exact match default
No — must set 4th arg to FALSE or 0
Yes — exact match is the default
Wildcard support
Yes
Yes
Excel version required
All versions
Excel 365, Excel 2021+
Readability
Harder — column numbers are opaque
Clearer — you see the actual column range
4. VLOOKUP Prompt — For Excel 2016/2019 Compatibility
(Role) You are an Excel formula specialist.
(Context) I need a VLOOKUP because my organization uses Excel 2016 and XLOOKUP is not available. My lookup table is on Sheet2: Column A = Employee ID, Column B = Department, Column C = Manager Name. I'm working in Sheet1 where Column A has Employee IDs starting in row 2.
(Task) Write a VLOOKUP formula for Sheet1 cell B2 that returns the Department for the Employee ID in A2. Also write a version that returns "Not Found" instead of #N/A when the ID does not exist in the lookup table.
(Format) Two formulas, each on its own line. Add a 1-sentence note explaining the difference between the two versions and when to use the error-handling version.
Role formula specialist
Context Excel version + exact table layout
Task VLOOKUP + error-safe version
Format two formulas + usage note
5. XLOOKUP Prompt — Right-to-Left and Error Handling
(Role) You are an Excel 365 formula expert.
(Context) I'm using Excel 365. I need to look up a value in Column C (Product Code) and return the corresponding value from Column A (Product Name). Column C is to the right of Column A — VLOOKUP cannot handle this right-to-left lookup. The lookup value I'm searching for is in cell E2.
(Task) Write an XLOOKUP formula that performs this right-to-left lookup. Include the [if_not_found] argument to return "Unknown Product" when no match exists. Do not use IFERROR wrapping.
(Format) Single formula, then a brief breakdown of each argument on a separate line — argument name, what I put in it, and why.
Role Excel 365 expert
Context column positions + version
Task right-to-left XLOOKUP with error arg
Format formula + per-argument breakdown
Pivot Table, Data Cleaning, and Formatting Prompts
For pivot tables, tell ChatGPT your exact column headers and what question you want to answer — it returns a field layout (Rows, Columns, Values, Filters) tailored to your dataset. For data cleaning, describe the inconsistency you see and give two or three example cell values; ChatGPT will write the nested formula chain needed to standardize them. For conditional formatting, describe the visual rule in plain English and it generates the formula to paste into Excel's "Use a formula to determine which cells to format" dialog.
6. Pivot Table Design — Build the Right Field Layout
(Role) You are an Excel data analyst.
(Context) I have a sales dataset with these columns: Date, Region, Salesperson, Product Category, Units Sold, Revenue. I want to answer this question: "How does monthly revenue compare across Regions and Product Categories?"
(Task) Tell me exactly how to set up a pivot table to answer this question: what field goes in Rows, Columns, Values, and Filters. Then explain how to group the Date field by month. Then explain how to add a calculated field that shows Revenue Per Unit (Revenue ÷ Units Sold).
(Format) Step-by-step numbered instructions using my exact column names. Add a note if any step differs between Excel 365 and Excel 2019.
Role data analyst
Context exact column headers + business question
Task field layout + date grouping + calculated field
Format numbered steps, version notes
7. Data Cleaning — Standardize Messy Text
(Role) You are an Excel data cleaning specialist.
(Context) I have a column of customer names (Column A) that are inconsistently formatted. Examples of what's in my cells: "JOHN SMITH", " jane doe " (with spaces before and after), "R. THOMAS KELLY". I need all names in Proper Case with no leading or trailing spaces.
(Task) Write Excel formulas to: (1) standardize all names to Proper Case with no extra spaces — put this in Column B; (2) extract just the last word (last name) from each cleaned name — put this in Column C. Assume data starts in row 2.
(Format) Two formulas, one per line. Each followed by a 1-sentence explanation of what it does and why each nested function is needed.
Role data cleaning specialist
Context messy data examples + target format
Task two cleaning formulas with destinations
Format formula + explanation of nesting
8. Conditional Formatting — Traffic-Light Rules
(Role) You are an Excel formatting expert.
(Context) I have a monthly sales tracker. Column B has Target values, Column C has Actual values for the same rows. I want a visual traffic-light system: cells in Column C turn red when Actual is more than 10% below Target, yellow when Actual is 1–10% below Target, and green when Actual meets or exceeds Target.
(Task) Give me the exact conditional formatting rules to create this system using "Use a formula to determine which cells to format." For each rule: the formula to enter, the fill color to set (as a descriptive name or hex), and which cells to apply it to.
(Format) Three rules, listed as: Rule Formula | Fill Color | Apply To. Add a short note about the order in which Excel evaluates multiple conditional formatting rules and which order I should set them in.
Role formatting expert
Context column layout + three-tier visual rule
Task three CF formulas with colors and ranges
Format Rule | Color | Apply To table + priority note
ChatGPT excels at data-centric tasks across tools. If you regularly use Excel alongside code or databases, see the data analysis prompts guide for prompts that go beyond the spreadsheet.
VBA Macro Prompts (No Coding Required)
You do not need to know VBA to get working macros from ChatGPT. Describe what you want to automate in plain English — what triggers it, what data it acts on, what the end result should look like — and ChatGPT writes commented VBA code you can paste directly into the Visual Basic Editor (Alt+F11 in Excel). Always test on a copy of your file first; VBA macros cannot be undone with Ctrl+Z.
9. VBA Macro — Automate a Repetitive Weekly Task
(Role) You are a VBA developer for Excel.
(Context) I need to automate a task I do every Monday: copy data from Sheet1 columns A through F (all rows with data, excluding the header in row 1), paste it as values only — no formulas — into Sheet2 starting at the next empty row, then delete all rows in Sheet1 where Column C is empty.
(Task) Write a complete VBA macro that performs these three steps in sequence. Add a comment above each step explaining what it does. End the macro with a MsgBox that confirms completion and shows how many rows were copied.
(Format) Complete VBA Sub block ready to paste. Add a 3-line setup note above the code explaining: where to open the editor (Alt+F11), where to paste the code (Insert → Module), and how to run it (F5 or assign to a button).
Role VBA developer
Context exact sheets + columns + trigger condition
Task three-step macro + MsgBox confirmation
Format complete Sub + commented steps + setup note
10. Chart Recommendation — Best Chart for Your Data
(Role) You are an Excel data visualization expert.
(Context) I have monthly revenue data for 4 product lines over 24 months. I want to show two things simultaneously: the trend over time for each product line, and how the product lines compare to each other at any given month.
(Task) Recommend the single best Excel chart type for this data and explain why it fits these two goals better than alternatives. Then give me step-by-step instructions to build it, including how to add the product line data series correctly.
(Format) Lead with the chart type in one sentence. Then numbered setup steps. End with exactly 3 formatting tips — one for the axis labels, one for the legend, one for the color scheme — to make the chart presentation-ready.
Role visualization expert
Context data shape + two goals
Task recommendation + rationale + build steps
Format 1-sentence lead + numbered steps + 3 formatting tips
ChatGPT VBA Response — What the Code Looks Like
ChatGPT — VBA Macro Response
You
(Role) You are a VBA developer for Excel.
(Context) I need to automate copying Sheet1 columns A–F (data rows only, not header) to Sheet2 as values, then delete rows where Column C is empty.
(Task) Write a complete VBA macro with comments and a MsgBox confirmation.
(Format) Complete Sub block. Setup note at top.
ChatGPT
Setup note: Press Alt+F11 to open the Visual Basic Editor. Click Insert → Module. Paste the code below into the module. Press F5 to run, or assign it to a button via Developer → Insert → Button.
Sub WeeklyDataTransfer()
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim lastRow As Long
Dim destRow As Long
Dim i As Long
Dim copied As Long
Set wsSource = ThisWorkbook.Sheets("Sheet1")
Set wsDest = ThisWorkbook.Sheets("Sheet2")
' Step 1: Find the last row with data in Sheet1
lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
' Step 2: Find the next empty row in Sheet2
destRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1
' Step 3: Copy columns A-F (values only) to Sheet2
copied = 0
For i = 2 To lastRow ' Row 2 skips the header
wsSource.Range("A" & i & ":F" & i).Copy
wsDest.Cells(destRow, 1).PasteSpecial Paste:=xlPasteValues
destRow = destRow + 1
copied = copied + 1
Next i
Application.CutCopyMode = False
' Step 4: Delete rows in Sheet1 where Column C is empty
For i = lastRow To 2 Step -1 ' Loop backwards to avoid row-shift issues
If wsSource.Cells(i, "C").Value = "" Then
wsSource.Rows(i).Delete
End If
Next i
MsgBox copied & " rows copied to Sheet2. Empty Column C rows removed from Sheet1.", _
vbInformation, "Transfer Complete"
End Sub
Important: Test this on a copy of your file first. VBA changes cannot be undone with Ctrl+Z.
(Role) VBA developer + (Context) exact task → complete, commented Sub with backward-loop safety and MsgBox
For prompts aimed at technical tasks like code generation and debugging, see the coding prompts guide — the same structured approach applies to Python, SQL, and more.
Structured prompts turn ChatGPT into a reliable Excel collaborator — formulas, macros, and formatting rules on demand.
Frequently Asked Questions
Can ChatGPT write Excel formulas directly?
Yes. Describe your column layout, include the column letters and data types, and tell ChatGPT what you want to calculate. It writes syntactically correct formulas and explains every argument. The key is context: "Column A has product codes as text strings, Column B has sales quantities as numbers" produces a formula that works on your data. "Give me a SUM formula" does not.
Does ChatGPT work with Excel or only Google Sheets?
Both. Specify which in your prompt and ChatGPT adjusts syntax accordingly. Most core Excel functions — SUMIF, VLOOKUP, IF, and the formula operators — work identically in Google Sheets. Key differences: Google Sheets supports ARRAYFORMULA differently, and XLOOKUP availability in Sheets is limited. Stating "I'm using Excel 365" or "I'm using Google Sheets" in your Context element removes any ambiguity.
Can ChatGPT debug #VALUE!, #N/A, and #REF! errors?
Reliably, yes — given enough context. Paste the formula, state the error you see, and describe what the referenced cells contain (text vs. numbers, blank cells, cross-sheet references). ChatGPT identifies the most likely causes in order of probability and provides corrected formulas. The #N/A error is particularly common with VLOOKUP and usually comes from a data-type mismatch — ChatGPT knows to check that first.
How do I get ChatGPT to write VBA macros?
Describe the task in plain English in your prompt: what triggers the macro, which sheets and columns it touches, and what the end result should look like. ChatGPT writes complete, commented VBA code with a setup note explaining where to paste it (Alt+F11 → Insert → Module). Always test on a copy of your workbook — VBA runs immediately and cannot be undone with Ctrl+Z.
Is XLOOKUP better than VLOOKUP?
Yes, if your Excel version supports it. XLOOKUP (available in Excel 365 and Excel 2021) can search in any direction, has a cleaner syntax that doesn't break when columns shift, and handles missing values natively with an [if_not_found] argument — no wrapping in IFERROR required. The only reason to use VLOOKUP in 2026 is backward compatibility: if the workbook needs to open in Excel 2019 or earlier, VLOOKUP is the safer choice.
Can ChatGPT help me set up pivot tables?
Yes. Give it your exact column headers, the business question you want to answer, and your Excel version. ChatGPT returns a complete field layout — what goes in Rows, Columns, Values, and Filters — tailored to your data and your question. It can also explain how to group date fields by month or quarter, how to sort and filter the pivot, and how to add calculated fields. The more specific your column names and question, the more precise the setup instructions.
Comments
Comments (0)
Leave a Comment