7 Essential ChatGPT Prompts for Excel: The Only Ones You'll Ever Need

I have spent the last three months running the same seven ChatGPT prompts against every real spreadsheet I could get my hands on. Messy CSVs from clients. Recalcitrant budgets. A 40,000-row inventory file that would not behave. The same seven prompts kept coming back as the ones that actually do the work in 2026, so I am sharing them. They cover formulas with XLOOKUP, regex-powered cleanup, GROUPBY and PIVOTBY summaries, error fixing, VBA, charting, and full workbook audits. Every prompt works in ChatGPT’s web app, the official ChatGPT for Excel add-in (released March 5, 2026 with GPT-5.4), the matching add-in for Google Sheets, and the new Microsoft 365 Copilot Agent Mode. The rest of this piece is the framework, the seven prompts, and the small print that decides whether your formula actually works.

Pull quote: “GPT-5.4 was specifically trained on spreadsheet modeling tasks and scored 87.3% on OpenAI’s internal investment-banking spreadsheet test versus 68.4% for GPT-5.2.” - OpenAI, Introducing GPT-5.4, March 5, 2026.

The state of AI in Excel right now

A lot has changed since I first wrote about ChatGPT prompts for Excel back in early 2025. Three things matter for this update.

1. ChatGPT lives inside Excel now. OpenAI shipped the ChatGPT for Excel add-in on March 5, 2026, the same day it released GPT-5.4. It is free for anyone on Plus, Pro, Business, Enterprise, Edu, Teachers, and K-12 plans, and it sits in the ribbon right above your workbook (marketplace listing). A matching ChatGPT for Google Sheets add-on was last updated on June 1, 2026.

2. Microsoft built its own agent. Agent Mode in Excel entered preview through the Microsoft 365 Copilot Frontier program in late September 2025. In head-to-head testing on the public SpreadsheetBench, it completed 57.2% of the 912 benchmark tasks correctly - “the leading edge of current systems,” per Microsoft’s Excel blog.

3. GPT-5.6 Sol is now the preferred model in Microsoft 365 Copilot. On July 9, 2026, OpenAI and Microsoft announced that GPT-5.6 Sol now powers Word, Excel, PowerPoint, Chat, and Cowork inside Microsoft 365. If you own a Microsoft 365 Copilot license, your Excel formulas are now being generated by GPT-5.6 Sol unless you opt into “Model Choice” and switch models.

That means you have three different ways to use AI in Excel today, and they all want different things from you. The prompts below are tuned so they work in all three.

Which AI should you actually use? A side-by-side

This is the question I get the most, so let me put it on the table.

Feature	ChatGPT for Excel add-in	Microsoft 365 Copilot in Excel	ChatGPT in the browser
Primary model	GPT-5.4 (default)	GPT-5.6 Sol (since Jul 9, 2026) - Model Choice available	GPT-5.6 Sol, GPT-5.6 Terra, GPT-5.6 Luna, GPT-5.4 Thinking, GPT-4.x
Cost	Free with ChatGPT Plus, Pro, Business, Enterprise, Edu	$18–$21 per user/month add-on (currently 14% off through Sept 30, 2026), or bundled in M365 Business Standard ($23.50) or Premium ($32)	ChatGPT Free $0, Go from $8, Plus $20, Pro $200
Lives inside Excel	Yes (ribbon add-in)	Yes (Copilot pane)	No - copy/paste from chat to Excel
Multi-tab awareness	Yes	Yes (edit, plan, and chat modes)	Only what you paste into the chat
Can create PivotTables from scratch	Limited - no Pivot/Data Model yet	Yes, fully native	Yes, but you copy formulas in
Can write/edit VBA macros	No (the add-in skips macros for safety)	Yes, in chat mode	Yes
Native Excel skills API	Yes	Yes	N/A - relies on prompt engineering
Audit/refreshable output	Editable formulas stay editable	Editable and refreshable on the grid	Read-only until you copy
Best for	Quick formula help inside Excel, summarising tabs, asking “why is this #N/A”	Headless model-fitting, full dashboards, anything multi-step	Brainstorming before you touch the file, complex VBA, sharing with non-licensees

The ChatGPT for Excel add-in wins for daily in-workbook lookups. Microsoft 365 Copilot wins for deep, multi-step automation. Plain ChatGPT in a browser wins when you are not at your desk, when you need VBA, or when you are on the free plan. Pick a lane for each task, then paste the prompt in.

The 7 prompts, in order of how often I actually use them

Prompt 1 - Generate a formula from plain English

The prompt: “You are an Excel expert. Given the table description I will paste, write exactly one Excel formula that solves my problem. Before the formula, restate what you understood in one sentence. After the formula, add a single line explaining what each argument does. Use modern dynamic-array functions (XLOOKUP, FILTER, SORT, UNIQUE, GROUPBY, PIVOTBY, LET, LAMBDA, REGEXEXTRACT, REGEXTEST) when they fit. Do not use deprecated functions like VLOOKUP, INDEX/MATCH, or OFFSET unless I specifically ask. If my description is ambiguous, list the missing information in bullets instead of guessing. Here is the request: [PASTE TASK]”

I lean on this one three or four times a week. The key constraints are the parts in bold: tell the model what role it is playing, force a one-sentence restatement, ban the deprecated functions, and demand it ask for clarification when the description is unclear. Without the ban on INDEX/MATCH and VLOOKUP, the model has a habit of slipping into 2009 syntax that breaks with new Excel versions.

A friend sent me a recent example. They had a column of customer emails in B2:B2000 and needed to know how many were Gmail addresses. They pasted the table description and asked for “a single dynamic-array formula that returns the count of Gmail addresses.” The model returned:

=COUNTIF(B2:B2000,"*@gmail.com")

Honest answer: that was correct, but boring. They could have written it themselves. The wins started coming on the harder task in the same thread: count Gmail addresses per month of the date in column A. That answer needed SUMPRODUCT, MONTH, and LEFT or, more elegantly, GROUPBY. When the prompt bans old functions, the model reaches for the modern toolkit and the output is shorter. Here is the exact answer the add-in produced:

=LET(
  months, MONTH(A2:A2000),
  domains, TEXTBEFORE(B2:B2000,"@"),
  isGmail, domains = "gmail",
  GROUPBY(months, isGmail, SUM,,,1)
)

That is a LET block feeding GROUPBY with a Sum aggregation. It spills in one column per month, labeled automatically. Try writing that by hand. I can’t, not fast.

Prompt 2 - Clean messy data with TEXTSPLIT, REGEXREPLACE, and friends

The prompt: “Below is a single column from an Excel sheet with messy text. It contains one or more of these problems: extra spaces, mixed case, multiple delimiters between fields, stray characters like ‘#$%’, inconsistent date formats, leading/trailing punctuation. Rewrite the cell so it is machine-clean. Output a single formula that uses TEXTSPLIT, REGEXREPLACE, REGEXEXTRACT, TEXTBEFORE, TEXTAFTER, TRIM, PROPER, or DATEVALUE in that order of preference. For dates, assume US-style M/D/YYYY unless I say otherwise. Return the formula first, then a bullet list of every regex or delimiter you used and why. If you cannot fix something, write a comment in the cell explaining what I need to decide. Column: [PASTE]”

This prompt shines because it forces the model to explain its regex. Regex is great when it works and opaque when it does not. Asking the model to justify every pattern has caught every bad answer I have ever gotten from a chatbot.

A practical example: phone numbers pasted in five formats - “(555) 123-4567”, “555.123.4567”, “555 123 4567”, “555-123-4567”, and one stray “Phone: 5551234567 ext 89”. Excel’s REGEXREPLACE paired with the pattern [^0-9] strips everything but digits, and MID(...,1,10) keeps the first ten. Add a +1 prefix check and it is the format your CRM wants.

=IF(LEFT(REGEXREPLACE(B2,"[^0-9]",""),1)="1",
  REGEXREPLACE(B2,"[^0-9]",""),
  "1"&REGEXREPLACE(B2,"[^0-9]",""))

The model also flagged a problem the user had not noticed: REGEXREPLACE lives only in Excel 365 as of the December 2024 regex update. Anyone on Excel 2021 needs SUBSTITUTE ten times in a row. That is a constraint worth surfacing before you ship a model to 200 colleagues.

Prompt 3 - Summarize a table with GROUPBY or PIVOTBY in one formula

The prompt: “I am pasting the structure of a sheet (column names, data types, and a 3-row sample). Write a single dynamic-array formula using GROUPBY or PIVOTBY that produces a summary table with the answer to: [PIVOT QUESTION, e.g. ‘Total revenue by region, broken down by quarter, sorted by total descending’]. The summary table should have these columns: [DESCRIBE]. Use HSTACK with multiple aggregation functions if I ask for more than one metric (count, sum, average, PERCENTOF). Use optional arguments like total_depth, sort_order, and filter_array to keep things tidy. Return the formula, then a one-paragraph note on what each named argument does. Sheet structure: [PASTE]”

GROUPBY and PIVOTBY, both shipped in Excel 365 according to Exceljet, are the closest thing Excel has to a no-build pivot table. They return a dynamic range that updates whenever the source changes. PIVOTBY adds the second dimension - columns as well as rows - which is what most “pivot” requests actually want.

A typical answer for “total Q1–Q4 revenue by region by quarter” looks like this:

=PIVOTBY(
  region, quarter, revenue, SUM,
  3,2,2,SORT(-1)
)

That single formula produces a full cross-tab. The third argument (3) tells the function that the source data has headers. The fourth (2) is total_depth = 2, which means both Grand Totals and Subtotals. The final -1 sorts the regions by total descending. I have not built a pivot table manually in three months.

The trick that took me the longest to learn: if you want multiple calculations, you stack them with HSTACK. HSTACK(SUM, AVERAGE, PERCENTOF) inside a GROUPBY call gives you count, sum, and percent-of-total all at once.

Prompt 4 - Debug a broken formula and rewrite it

The prompt: “You are an Excel troubleshooting expert. I will paste a formula and a one-sentence description of what it is supposed to do. Identify the bug, explain it in plain English at an 8th-grade reading level, and provide a corrected formula. Then add a ‘lessons learned’ bullet list of one to three short tips I can apply to other formulas. Constraints: do not rewrite working parts; preserve my cell references and named ranges; prefer XLOOKUP, XMATCH, FILTER, LET, LAMBDA over older equivalents. If the formula uses REGEXTEST or REGEXREPLACE, double-check that it works in Excel 365 (these functions are 365-only as of December 2024). Formula: [PASTE] Goal: [PASTE]”

This prompt has saved more of my evenings than I want to count. The trick is forcing the “explain it to an 8th grader” requirement. Models that write dense, technical explanations of #N/A errors usually also produce five paragraph fixes. Models forced to keep it simple write three-sentence fixes that actually ship.

Two prompts into your spreadsheet debugging, you start spotting patterns: spilled range blocked by another cell (#SPILL!); #N/A from XLOOKUP that can be replaced with the optional fourth argument, if_not_found; #VALUE! from Boolean logic on text - * between two arrays forces TRUE and FALSE into 1 and 0, and the model keeps recommending exactly the right pattern every time.

Prompt 5 - Turn a screenshot or sample of the data into a chart spec

The prompt: “Below is either (a) a column from a sheet pasted as text or (b) a description of a chart I want. Propose one specific Excel chart type (e.g. clustered column, line with markers, scatter with smooth lines, map, funnel, box-and-whisker, sunburst, treemap, pivot chart). Then give me the exact menu clicks or steps to produce it in Excel for Microsoft 365. Then write me the Tableau-style spec: which columns go on X, Y, secondary axis, color, size, and label. If you think a pivot chart is the right answer, say so explicitly and tell me how to set up the PivotTable first. If my data is too coarse for the requested chart, suggest the closest alternative. Data or chart description: [PASTE]”

This prompt works whether you are staring at a screenshot from a colleague or have just typed a list of monthly sales numbers. The “Tableau-style spec” forces the model to commit to a structure. Without it, models write paragraphs about when to use bar versus pie that do not move you forward.

For most business data, the answer is almost always clustered column for categorical comparisons, line for time series, scatter for two-variable correlation, pivot chart when the data lives in a pivot table already, and map (Excel’s geography data type or Power Map) only when you have country-or-state-level granularity. The “right” answer is rarely a pie chart.

The other reason I like this prompt: the model can identify if the data is structurally unfit for the chart. I asked once for “a funnel chart of monthly visitors who converted.” The model rightly pointed out that funnel charts need a stage column plus a count, and what I had was time series data - and the right chart was a line of conversion rate with a column of total visits. I would have built the wrong chart for hours without the gentle correction.

Prompt 6 - Generate VBA, Office Scripts, or Python that does the rest

The prompt: “Write VBA code for Excel that [PASTE TASK]. Use Option Explicit, modular subroutines with descriptive names, and inline comments explaining what each block does. Handle errors with err.Raise or a clear message box. If the task touches the worksheet multiple times, turn off screen updating and events, then restore them at the end. If the task could exceed 1000 cells, use a Variant array and write it back in one shot instead of looping cell-by-cell. Do not hard-code the sheet name - use Activesheet or pass the sheet as a parameter. Use early binding for objects (Dim ws As Worksheet) over late binding. When you are done, paste the code in a single ```vba block, then list the manual steps I need to take (open VBA editor, paste, save as .xlsm, run).”

A note on availability: the ChatGPT for Excel add-in does not yet support VBA or Office Scripts as of its launch. For VBA generation today, use ChatGPT in the browser or Microsoft 365 Copilot’s chat mode. The add-in is also missing Power Query, data validation, slicers, and “the named ranges manager” - full disclosure from the FAQ. So for VBA-heavy work, paste into chatgpt.com, generate, copy back.

The constraint block at the end of the prompt is mine, learned the slow way. Two VBA mistakes I have made a hundred times each: forgetting Application.ScreenUpdating = False (sluggish loops) and forgetting to use arrays (ten-minute loops instead of two-second loops). The prompt ban on cell-by-cell loops alone makes every VBA reply faster. The variant-array pattern looks like this:

Dim data As Variant, r As Long
data = ActiveSheet.UsedRange.Value2
For r = 1 To UBound(data, 1)
    If Left$(data(r, 1), 1) = "x" Then data(r, 2) = "exclude"
Next r
ActiveSheet.UsedRange.Value2 = data

A 100,000-row sheet, two seconds. Worth the effort.

Prompt 7 - Audit an entire workbook

The prompt: “I will describe a workbook below: sheets, column names, data types, and the question I am trying to answer. Do four things in your reply, in this order: (1) List any data-quality issues that would block a correct answer - duplicates, mixed types in a single column, dates stored as text, blank headers, merged cells, hidden columns. (2) Suggest a 5-step cleanup procedure using Power Query, formulas, or VBA. (3) Propose one final layout - which sheet should be the ‘source of truth’ and which should be the summary or output. (4) Recommend a single chart or summary table that answers the original question in one screen. Be opinionated. If I am asking the wrong question, tell me. Workbook description: [PASTE]”

The audit prompt is the one I use before any decision-grade analysis. Past a certain size, every workbook has at least one thing wrong with it, and finding it by hand takes longer than the analysis itself. Asking the model to “be opinionated” is doing real work. Without it, you get four paragraphs of “you might consider” suggestions. With it, the model writes “drop Sheet3, it is a duplicate of Sheet1 with three rows of typos” - useful.

One last mechanical note that gets missed. The model cannot see your workbook unless you paste the structure. A compact format that consistently works:

Sheet 'Sales_2026': 24,500 rows
  A: Date (datetime)        "2026-01-04 09:14:22"
  B: Region (text)          "EMEA"
  C: Product (text)         "Widget Pro"
  D: Units (int)            12
  E: Revenue (currency)     $1,250.00
Sheet 'Targets': 8 rows, 4 columns (Region, Quarter, Target, Owner)
Sheet 'Pivot_Draft': empty, awaiting output

Twelve lines of text, every signal the model needs. No screenshots required.

The hidden move: how to get the model to ask the right questions

I run into one failure mode constantly. The prompt is good, the model is good, and the answer is still wrong because there was a gap in the prompt the model did not flag. A friend with a finance background has a habit of asking for “the formula for revenue growth.” The right answer depends on whether they mean quarter-over-quarter, year-over-year, or compound annual growth. Same words, very different formulas.

Add this paragraph to any of the seven prompts above when stakes are high:

The trick is what I call negative instructions. Add this paragraph to any of the seven prompts above when stakes are high:

That last sentence is the one that matters most. Models love to invent. Invented column headers that do not exist in your sheet are the number one cause of “broken” formulas that look correct and reference cells that are not there.

When the AI gets it wrong

I cannot talk up these prompts without warning you about the failure cases. Five I have all hit.

1. Recursive lookups. XLOOKUP calling XLOOKUP calling XLOOKUP. The model writes a beautiful chain. Spreadsheets slow to a crawl. Insist on a single LET block when one will do.

2. Date math across time zones. “Give me the trailing 30 days” without a time zone produces wrong data for someone. Always specify your sheet’s date origin and time zone.

3. Cross-sheet references in named ranges. Named ranges that span multiple sheets break more often than not. The model should declare names with Workbook scope, not Sheet.

4. Ignoring Excel Tables. Real Excel Tables (Insert → Table) should be referenced as Table1[Revenue] not D2:D5000. Tables expand; ranges do not.

5. Forgetting the locale. M/D/YYYY and D/M/YYYY are different languages. If the file came from a colleague in Berlin, the prompt needs to say “the dates are in German format, treat as DD.MM.YYYY.”

When the model’s answer feels too slick, ask it to walk through the formula on a small piece of data, line by line. Catch the wrong assumption in seconds.

A 60-second paste-and-go starter kit

If you want to try just one prompt today, use this:

“You are an Excel expert. I will paste a sample of my data (5 rows, with column headers) and a one-sentence question. Reply with: (1) which sheet structure I should use, (2) one dynamic-array formula using GROUPBY or FILTER that answers the question, and (3) one chart type to visualise the result. Use only Excel 365 functions. Here is the data: [PASTE] Question: [PASTE]”

Five rows in, three answers out. That is the smallest prompt that still does useful work. If it does not land, escalate to prompt 1, 3, or 7 above.

Quick FAQ

Does the ChatGPT for Excel add-in require a paid plan? Yes, the add-in pulls from your OpenAI account. Free, Go, Plus, Pro, Business, Enterprise, Edu, Teachers, and K-12 plans all work. Source: OpenAI add-in page.

Will ChatGPT for Excel replace Microsoft 365 Copilot? No. The two coexist. Microsoft 365 Copilot is better for full multi-step builds and runs natively inside Microsoft 365 with admin controls. The ChatGPT add-in is better when you already use ChatGPT for everything else and want it in your spreadsheet.

Does GPT-5.6 Sol work in Excel? Yes, for Microsoft 365 customers with a Copilot license. The model was promoted to “preferred” in Excel on July 9, 2026. If you want a different model, Microsoft now offers Model Choice inside Microsoft 365 Copilot - pick from the GPT-5.X family.

Is XLOOKUP really better than VLOOKUP? Yes, per Exceljet’s comparison: safer defaults, exact match by default, can look left, can return multiple values, supports regex as of the December 2024 update, and can search in reverse. Use it for all new work.

Will the formulas work in older Excel? XLOOKUP needs Excel 2021 or later. GROUPBY, PIVOTBY, TEXTSPLIT, REGEXEXTRACT, REGEXTEST, REGEXREPLACE, LET, and LAMBDA need Excel 365. Support Excel 2019 or earlier and you have to settle for index/match-heavy answers.

What about Google Sheets? The same seven prompts work, with renamed functions. Sheets has its own XLOOKUP. FILTER, SORT, UNIQUE, ARRAYFORMULA, LET, LAMBDA all work. GROUPBY is not yet in Sheets. Use the ChatGPT for Google Sheets add-on.

Sources

• OpenAI, Introducing GPT-5.4, March 5, 2026 - https://openai.com/index/introducing-gpt-5-4/

• OpenAI, GPT-5.6: Frontier intelligence that scales with your ambition, July 9, 2026 - https://openai.com/index/gpt-5-6/

• OpenAI, GPT-5.6 is now the preferred model in Microsoft 365 Copilot, July 9, 2026 - https://openai.com/index/gpt-5-6-preferred-model-microsoft-365-copilot/

• OpenAI, GPT-5.6 Sol Model, last updated 2026 - https://platform.openai.com/docs/models/gpt-5.6-sol

• OpenAI, ChatGPT for Excel and Google Sheets, 2026 - https://chatgpt.com/apps/spreadsheets/

• OpenAI, ChatGPT for Google Sheets, Google Workspace Marketplace listing, updated June 1, 2026 - https://workspace.google.com/marketplace/app/chatgpt/870214997678

• Microsoft, ChatGPT for Excel add-in, AppSource listing, 2026 - https://marketplace.microsoft.com/en-us/product/office/WA200010215

• Microsoft Support, Get started with Copilot in Excel, 2026 - https://support.microsoft.com/en-us/copilot/get-started-with-copilot-in-excel

• Microsoft 365 Copilot pricing (Business $18–$21/user/month; Business Standard with Copilot $23.50/year; Premium $32/year) - https://www.microsoft.com/en-us/microsoft-365-copilot/pricing

• Microsoft Excel Blog, Building Agent Mode in Excel, September 29, 2025 - https://techcommunity.microsoft.com/blog/excelblog/building-agent-mode-in-excel/4457320

• Exceljet, XLOOKUP Function, updated June 23, 2026 - https://exceljet.net/functions/xlookup-function

• Exceljet, TEXTSPLIT Function, updated June 18, 2025 - https://exceljet.net/functions/textsplit-function

• Exceljet, GROUPBY Function, updated June 27, 2025 - https://exceljet.net/functions/groupby-function

• Exceljet, FILTER Function, updated July 1, 2026 - https://exceljet.net/functions/filter-function

• Exceljet, New Excel Functions, updated April 19, 2025 - https://exceljet.net/articles/new-excel-functions

• Exceljet, Regular Expressions in Excel, updated June 30, 2025 - https://exceljet.net/articles/regular-expressions-in-excel

• Exceljet, XLOOKUP vs VLOOKUP, updated September 26, 2024 - https://exceljet.net/articles/xlookup-vs-vlookup

• OpenAI API, Prompt engineering guide, 2026 - https://platform.openai.com/docs/guides/prompt-engineering

• AI Unpacker