Excel Formulas with ChatGPT: 25 Prompts for Data Analysis in 2026

The answer you came for: in 2026, you have three dominant ways to get AI inside Excel. Pick the one that matches your workflow, use the 25 prompts below, and stop debugging VLOOKUP errors at midnight.

The 2026 AI-for-Excel Landscape

Tool	Best For	Pricing	Where It Lives
ChatGPT for Excel (GPT-5.5)	Full workbook creation, multi-sheet reasoning, scenario modeling	Free�Plus $20/mo, Pro $200/mo	Native Excel/Sheets sidebar add-in
Microsoft Copilot + =COPILOT()	Formula generation, in-cell AI, chart creation inside M365	$30/user/mo add-on	Built into Excel ribbon and formula bar
ChatGPT Advanced Data Analysis	One-off file uploads, Python-generated charts, ad-hoc analysis	Free�Plus $20/mo	Browser chat interface
Claude (1M-token context)	Complex multi-tab workbooks, financial modeling, formula debugging	Free�Pro $20/mo	Browser chat interface
Formula Bot	Formula generation and explanation only	Free tier available	Web + Sheets add-on
Numerous.ai	Bulk in-cell AI across thousands of rows (=AI function)	Free tier available	Excel/Sheets add-in
GPT for Work	Multi-step spreadsheet automation with sidebar chat	Free trial�Pro ~$10/mo	Excel/Sheets add-in

As of May 2026, ChatGPT for Excel and Google Sheets is generally available across all plans, powered by GPT-5.5. OpenAI launched it in beta on March 5, 2026, originally for Business/Enterprise/Edu users, and expanded it globally by May 5. Microsoft’s =COPILOT() functionwhich brings LLM calls directly into cell formulasrolled out to Beta Channel users in August 2026 and continues expanding through the Insider program.

“The most expensive Excel mistake in 2026 isn’t a wrong VLOOKUP. It’s trusting a fluent model with a vague prompt, pasting the result into a spreadsheet, and shipping a decision that feels data-driven but isn’t.”

How to Prompt AI for Excel (The 5 Rules)

AI writes faster formulas than any human. It also invents data, misinterprets units, and hallucinates confident-looking numbers. These five rules prevent garbage-in-garbage-out:

1. Specify your Excel version and locale. Microsoft 365 supports dynamic arrays and LAMBDA. Excel 2019 doesn’t. Comma vs. semicolon separators matter. Say which you use.
2. Describe the data layout. Table names, column letters, sample rows, and expected output. The difference between “calculate revenue” and “in table SalesTbl, multiply column [Units] by column [UnitPrice] where [Region]=‘West’” is the difference between a formula that works and one that silently produces wrong numbers.
3. Force validation. End every prompt with “also give me 3 edge cases and a manual sanity check.” AI will produce pretty-looking tables even when wrong. Explicit validation demands catch errors before they become decisions.
4. Split complex tasks. Ask for a data audit first, then the formula, then the explanation. Staged prompting reduces hallucinationthe same principle that powers high-accuracy Text-to-SQL frameworks.
5. Use the tool that fits the problem. Formula generation needs a different tool than bulk cell classification. The comparison table above maps the right tool to the right task.

Weak prompt:

“Write a formula to calculate revenue.”

Strong prompt:

“In Excel 365 with comma separators, I have a table named SalesTbl with columns Date, Region, Product, Units, UnitPrice, and Discount. Write a formula in cell H2 for monthly net revenue (Units � UnitPrice � (1-Discount)) filtered to Region=‘West’. Return 0 if no rows match. Handle blanks in Discount. Also give me 3 edge cases and a manual sanity check.”

25 ChatGPT Prompts for Excel Data Analysis

Data Cleaning (Prompts 1�5)

1. Standardize text casing. “Column A contains names in mixed case (some ALL CAPS, some lowercase). Write a formula to convert everything to Proper Case. Also flag any cells that contain numbers within the name string.”

2. Strip invisible characters. “Column B imported from a CRM export. Values contain non-breaking spaces and trailing whitespace. Write a formula that produces clean, single-space-separated text, and add a helper column that returns TRUE if the original cell had hidden characters.”

3. Extract structured data from unstructured text. “Column C has email addresses. Write formulas to extract the domain, the username before the @, and the top-level domain separately into columns D, E, and F. Handle subdomains like [email protected].”

4. Parse delimited addresses. “Column G contains full addresses in ‘Street, City, State, ZIP’ format. Write TEXTSPLIT or LEFT/MID/FIND formulas to isolate each component. Handle apartment numbers after a second comma.”

5. Identify incomplete records. “Columns H through M are required fields. Write a formula that returns the specific field name blank for each row. If multiple fields are blank, list all of them. If all populated, return ‘Complete.’”

Lookup & Reference (Prompts 6�10)

6. Two-way matrix lookup. “Pricing matrix on Sheet2: products in column A, quantity tiers in row 1. Write a nested XLOOKUP formula that returns the intersection price for the product in C5 and the quantity in D5.”

7. Return all matching values. “Table OrdersTbl has OrderID and Product. Write a FILTER formula that returns every OrderID where Product matches cell B1. Results must spill into a column.”

8. Approximate match with next larger value. “Column A: sorted revenue thresholds. Column B: commission rates. Write an XLOOKUP for the amount in D2 that returns the rate for the next larger threshold when no exact match exists.”

9. Lookup across multiple sheets. “Sheets Jan, Feb, and Mar have identical structures. Write a formula that looks up cell A2 across all three sheets and returns the corresponding value from column C.”

10. Dynamic INDEX-MATCH-MATCH. “Table KPI_Tbl: months in row 1, metric names in column A. Return the intersection value for the metric in B2 and month in B1. Formula must survive column/row reordering.”

Conditional Analysis (Prompts 11�15)

11. Multi-tier classification with IFS. “Column E has scores 0�100. Assign: ‘Exceptional’ =95, ‘Exceeds’ =85, ‘Meets’ =70, ‘Below’ =50, ‘Critical’ <50. Also generate a COUNTIF summary of the distribution.”

12. Differential bonus calculation. “Column F: department, column G: base salary. Write a SWITCH formula for bonuses: Sales 12%, Engineering 8%, Operations 6%, others 4%.”

13. Outlier detection with standard deviation. “Column H: monthly revenue. Write a LET formula that returns ‘Outlier’ for values more than 2 standard deviations from the mean. Compute mean and SD once via LET.”

14. Date range overlap test. “Column J: start dates, column K: end dates. L1 = period start, L2 = period end. Return ‘In Period’ if the date range overlaps, otherwise ‘Outside Period.’”

15. Conditional formatting trigger formula. “Return TRUE when column M is a duplicate AND column N is blank. Do not flag if column N has data, even if column M duplicates exist.”

Aggregation & Summary (Prompts 16�20)

16. Multi-criteria SUMIFS. “Table RevenueTbl: Region, Product, Month, Revenue. SUMIFS for Region=‘North’, Product=‘Widget’, Month between D1 (start) and D2 (end). Use date-aware logic.”

17. Weighted average with conditions. “Column A: category, B: volume, C: price. Calculate the volume-weighted average price for the category in D1. Exclude zero-volume rows.”

18. MAXIFS with top-N tiebreaker. “Column E: department, F: employee, G: sales. Return the employee with the highest sales per department. If tied, return both names separated by a comma using LET and TEXTJOIN.”

19. Unique count with dynamic criteria. “Column H: dates, column I: customer IDs. Count unique customers who transacted in the month matching cell J1. Use UNIQUE and FILTER.”

20. Running total with conditional reset. “Column K: dates, column L: daily deposits. Create a running total in column M that resets to zero at each new month. Must work when data is filtered.”

Error Handling, Dates & Advanced (Prompts 21�25)

21. Graceful error wrapping. “A complex formula sometimes returns #DIV/0!, #N/A, or #VALUE!. Rewrite using IFERROR to return a message identifying the error type: ‘Missing data’ for #N/A, ‘Division by zero’ for #DIV/0!”

22. Business day calculation with holidays. “Column A: start date, B: end date. Sheet ‘Holidays’ column Z lists company holidays. Write NETWORKDAYS.INTL excluding weekends and the holiday list.”

23. Fiscal quarter with custom start month. “Column C: dates. Fiscal year starts October. Return ‘FY2026-Q1’ for Oct�Dec 2026, ‘FY2026-Q2’ for Jan�Mar 2026, etc. No helper columns.”

24. Dynamic array LAMBDA for reusable logic. “Write a LAMBDA named TAXCALC that accepts amount and tax rate, computes taxed amount rounded to 2 decimals. Usable across the workbook as =TAXCALC(A2, B2).”

25. Full validation contract prompt. “Before I trust this formula in production, build a validation contract. Paste 15 sample rows with known outputs. Audit the data, generate the formula, then produce: (a) row count reconciliation, (b) edge case tests for blanks/zeros/dates, (c) a manual calculation to verify on 3 random rows.”

How the 2026 Tools Differand When to Use Each

ChatGPT for Excel (GPT-5.5) is a sidebar add-in that can build entire multi-tab workbooks from plain language. It reasons across sheets, traces formula chains, explains why outputs changed, and asks permission before editing. OpenAI says it’s powered by GPT-5.5 and uses agentic usage limits shared with other agentic features. It runs inside Excel (desktop and web) and Google Sheets natively after a one-time install from the Microsoft Marketplace or Google Workspace Marketplace.

Microsoft Copilot is the native M365 answer. It lives in the Excel ribbon and generates formulas, pivot tables, and charts from natural language. The new =COPILOT() functionlaunched August 2026lets you call AI inside any cell formula. Syntax: =COPILOT("classify this feedback as positive or negative", D4:D18). It supports 100 calls per 10 minutes, up to 300 per hour, and works alongside IF, SWITCH, LAMBDA, and WRAPROWS.

Bolded definitions:

• Agentic usage limits The credit-based cap on how much AI work ChatGPT for Excel can perform per billing cycle. Larger workbooks and multi-step edits consume more credits.
• =COPILOT() function A native Excel formula that sends a natural language prompt and optional cell references to a large language model and returns results directly into the grid. Available to Microsoft 365 Copilot license holders.
• Spill range In Excel 365, a dynamic array formula that populates adjacent cells automatically without Ctrl+Shift+Enter. Indicated by the # suffix (e.g., A1#).
• LAMBDA An Excel function that lets you define custom, reusable functions using Excel’s formula language, without VBA.

FAQ

Is ChatGPT for Excel free? Free and Go plans include limited usage. Plus ($20/month) and Pro ($200/month) users get access subject to agentic usage limits. Business, Enterprise, and Edu had a free preview through June 2, 2026, after which usage follows each plan’s credit terms.

Does ChatGPT for Excel share data for training? For Business, Enterprise, Edu, and Teacher plans, data is not used for model training by default. Spreadsheet chats operate separately from regular ChatGPT history.

What’s the difference between ChatGPT for Excel and Copilot? ChatGPT for Excel builds, updates, and reasons across entire workbooks as a sidebar add-in. Copilot is built into the ribbon and generates formulas, charts, and summaries. The =COPILOT() function is an in-cell AI formula. ChatGPT uses credit/usage limits; Copilot requires a $30/user/month add-on with M365.

Can AI handle Excel files with millions of rows? Not in Excelthe hard limit is 1,048,576 rows per sheet. For datasets that outgrow Excel, use tools like Anomaly AI (handles up to 200MB files) or dedicated data warehouses.

What features are unsupported by AI tools? ChatGPT for Excel has limited VBA macro support, and limited Power Query, Pivot Data Model, data validation, slicer, timeline, and external connection support. Very large workbooks may return partial results.

Sources

• Introducing ChatGPT for Excel and new financial data integrations OpenAI (March 5, 2026, updated May 5, 2026)
• ChatGPT for Excel and Google Sheets OpenAI Help Center
• Bring AI to your formulas with the COPILOT function in Excel Microsoft Tech Community (August 18, 2026)
• Five ways to create and analyze an Excel spreadsheet with AI Microsoft Excel Blog (March 17, 2026)
• ChatGPT is Now Inside Excel and Google Sheets Analytics Vidhya (May 7, 2026)
• Excel AI Data Analysis Tools: 10 Compared (2026) Anomaly AI (April 1, 2026)
• 10 Best AI Tools for Excel in 2026 Kuse (February 27, 2026)
• ChatGPT Prompts for Data Analysis and Excel: The Playbook Rephrase (February 20, 2026)