How to Use ChatGPT for Excel in 2026 (Complete Guide)
TL;DR
ChatGPT writes Excel formulas, VBA macros, and data analysis scripts from plain English descriptions. The free tier handles formula generation and explanations. ChatGPT Plus ($20/mo) lets you upload Excel files for direct analysis. Microsoft Copilot works inside Excel but handles simpler tasks. For complex formulas, macros, and multi-step analysis, ChatGPT is currently more capable.
Table of contents
ChatGPT has become the most powerful Excel assistant available. Whether you need to write complex formulas, analyze data, automate reports with VBA macros, or build dashboards — ChatGPT handles it all. This guide walks you through every use case with 20+ real formula examples you can copy and paste.
Get tools like these delivered weekly
Subscribe free →Quick navigation
Excel is still the world's most-used data tool — but writing formulas, debugging errors, and building macros has always been the painful part. ChatGPT changes that equation entirely. Describe what you want in plain English, and get working formulas, macros, and analysis in seconds.
This guide covers every practical way to use ChatGPT with Excel, from basic formula generation to advanced VBA automation. Every example is tested and ready to use.
1. Writing Excel Formulas with ChatGPT
The most common use case: describe what you need and ChatGPT writes the formula. The key to good results is describing your data layout clearly — column names, data types, and expected output.
How to prompt for formulas
A good formula prompt includes three things: your data layout, what you want to calculate, and any conditions. Here's the template:
I have an Excel spreadsheet with:
- Column A: Employee Name
- Column B: Department
- Column C: Sales Amount
- Column D: Date (MM/DD/YYYY)
Write a formula that calculates the total sales for the "Marketing" department in Q1 2026.
ChatGPT returns:
=SUMIFS(C:C, B:B, "Marketing", D:D, ">="&DATE(2026,1,1), D:D, "<="&DATE(2026,3,31))
Lookup formulas
ChatGPT handles all types of lookups. Tell it you want a VLOOKUP and it will also suggest the modern alternatives (XLOOKUP, INDEX/MATCH) with explanations of why they may be better.
Prompt: "Look up an employee's salary from a table in Sheet2 based on their ID in cell A2"
VLOOKUP:
=VLOOKUP(A2, Sheet2!A:C, 3, FALSE)
XLOOKUP (recommended):
=XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "Not Found")
INDEX/MATCH:
=INDEX(Sheet2!C:C, MATCH(A2, Sheet2!A:A, 0))
Nested IF formulas
Nested IFs are where ChatGPT truly shines. Instead of manually building nested logic, describe the rules:
Prompt: "Grade students: 90+ = A, 80-89 = B, 70-79 = C, 60-69 = D, below 60 = F. Score is in B2."
=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", B2>=60,"D", TRUE,"F")
2. Analyzing Data with ChatGPT
ChatGPT Plus ($20/mo) includes Advanced Data Analysis, which lets you upload Excel files directly. ChatGPT reads the data, runs Python analysis, creates visualizations, and explains findings in plain English.
What you can do with file uploads
- Trend analysis: "Show me the monthly sales trend and highlight any seasonal patterns"
- Statistical summaries: "Calculate mean, median, standard deviation, and outliers for each column"
- Correlation analysis: "Which factors are most correlated with customer churn?"
- Pivot-style analysis: "Break down revenue by region, product category, and quarter"
- Forecasting: "Project next quarter's sales based on the historical trend"
Using the free tier for analysis
Without file uploads, you can still analyze data by pasting it as text. Copy a range from Excel, paste it into ChatGPT, and ask your question. This works well for datasets under a few hundred rows. For larger datasets, describe the structure and ask ChatGPT to write formulas or pivot table instructions.
3. Creating VBA Macros with ChatGPT
VBA macros automate repetitive Excel tasks — formatting reports, merging data from multiple sheets, sending email alerts, generating PDFs. ChatGPT writes production-ready VBA code from plain English descriptions.
Example: Auto-format a monthly report
Prompt: "Write a VBA macro that formats a report: bold the header row, add borders to all data cells, set column widths to auto-fit, add alternating row colors (light gray/white), and freeze the top row."
Sub FormatReport()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim lastRow As Long, lastCol As Long
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
' Bold header row
ws.Range(ws.Cells(1, 1), ws.Cells(1, lastCol)).Font.Bold = True
' Add borders
ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Borders.LineStyle = xlContinuous
' Auto-fit columns
ws.Columns.AutoFit
' Alternating row colors
Dim i As Long
For i = 2 To lastRow
If i Mod 2 = 0 Then
ws.Range(ws.Cells(i, 1), ws.Cells(i, lastCol)).Interior.Color = RGB(245, 245, 245)
End If
Next i
' Freeze top row
ws.Rows(2).Select
ActiveWindow.FreezePanes = True
End Sub
How to add VBA macros to Excel
- Press Alt + F11 to open the VBA editor
- Go to Insert > Module
- Paste the code ChatGPT generated
- Press F5 to run, or close the editor and use Alt + F8 to run from Excel
- Save the file as .xlsm (macro-enabled workbook)
Example: Merge data from multiple sheets
Prompt: "Write a VBA macro that copies all data from sheets named Jan, Feb, Mar into a sheet called Summary, stacking them vertically with no duplicate headers."
ChatGPT generates the complete macro with error handling, header detection, and dynamic range management. Always test macros on a copy of your workbook first.
4. Cleaning & Transforming Data
Dirty data is the bane of spreadsheet work. ChatGPT writes formulas and macros for every common data cleaning task.
Common cleaning formulas
Remove extra spaces:
=TRIM(A2)
Standardize names to proper case:
=PROPER(TRIM(A2))
Extract email domain:
=RIGHT(A2, LEN(A2) - FIND("@", A2))
Split first/last name:
First: =LEFT(A2, FIND(" ", A2) - 1)
Last: =RIGHT(A2, LEN(A2) - FIND(" ", A2))
Remove non-numeric characters from phone numbers:
=TEXTJOIN("", TRUE, IF(ISNUMBER(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)*1), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), ""))
(Enter with Ctrl+Shift+Enter for older Excel versions)
Convert text dates to real dates:
=DATEVALUE(A2)
Find and replace using formulas:
=SUBSTITUTE(A2, "old text", "new text")
Flag duplicates:
=IF(COUNTIF(A$2:A2, A2)>1, "Duplicate", "Unique")
Remove leading zeros:
=VALUE(A2)
5. Building Dashboards with ChatGPT
ChatGPT can design an entire Excel dashboard for you — from the data model to the pivot tables, charts, and layout. Describe what metrics you want to track, and ChatGPT provides step-by-step instructions or VBA code to build it.
Dashboard prompt example
Prompt: "I have sales data with columns: Date, Product, Region, Revenue, Units Sold, Cost. Design a dashboard that shows:
1. Monthly revenue trend chart
2. Revenue by region (pie chart)
3. Top 5 products by revenue (bar chart)
4. KPI cards: Total Revenue, Total Units, Avg Order Value, Profit Margin
5. A slicer for filtering by region and date range
Give me step-by-step instructions to build this in Excel."
ChatGPT will provide instructions for creating the pivot tables, chart types, conditional formatting for KPI cards, and slicer connections. For a faster approach, ask for a VBA macro that creates the entire dashboard programmatically.
6. Automating Reports
If you build the same report every week or month, ChatGPT can write a macro that does it automatically — pulling data, calculating metrics, formatting the output, and even saving the file as PDF or emailing it.
Prompt: "Write a VBA macro that:
1. Refreshes all pivot tables in the workbook
2. Copies the Dashboard sheet as a new workbook
3. Saves it as PDF in C:\Reports\ with filename 'Weekly_Report_YYYY-MM-DD.pdf'
4. Opens Outlook and creates a new email with the PDF attached, addressed to team@company.com with subject 'Weekly Report - [date]'"
Pro tip: Combine this with Windows Task Scheduler to run the macro at a set time. Ask ChatGPT: "How do I schedule an Excel macro to run every Monday at 9 AM using Task Scheduler?" It will provide the complete VBScript and Task Scheduler configuration.
7. 20+ Formula Examples (Copy & Paste Ready)
Here are formulas ChatGPT commonly generates for business use cases. All are tested and ready to use — just adjust the cell references to match your data.
Financial
1. Year-over-year growth:
=((B2-B1)/B1)*100
2. Running total:
=SUM($B$2:B2)
3. Compound interest:
=B2*(1+C2/12)^(D2*12)
4. Weighted average:
=SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10)
5. Loan payment (monthly):
=PMT(C2/12, D2*12, -B2)
Date & Time
6. Business days between dates:
=NETWORKDAYS(A2, B2)
7. Age from birthdate:
=DATEDIF(A2, TODAY(), "Y")
8. Next Monday:
=A2 + 7 - WEEKDAY(A2, 3)
9. Quarter from date:
="Q"&ROUNDUP(MONTH(A2)/3,0)
10. Fiscal year (starting April):
=IF(MONTH(A2)>=4, YEAR(A2), YEAR(A2)-1)
Text & Data
11. Extract numbers from text:
=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),0),ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A2)))/10)
12. Concatenate with separator:
=TEXTJOIN(", ", TRUE, A2:A10)
13. Count words in a cell:
=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1
14. Extract initials:
=LEFT(A2,1)&MID(A2,FIND(" ",A2)+1,1)
Conditional & Lookup
15. VLOOKUP with error handling:
=IFERROR(VLOOKUP(A2, Sheet2!A:C, 3, FALSE), "Not Found")
16. Multiple criteria lookup (INDEX/MATCH):
=INDEX(D:D, MATCH(1, (A:A=G2)*(B:B=H2), 0))
(Ctrl+Shift+Enter)
17. Dynamic ranking:
=RANK(B2, $B$2:$B$100, 0)
18. Conditional count with multiple criteria:
=COUNTIFS(A:A, "Sales", B:B, ">50000", C:C, "2026")
Array & Dynamic
19. Unique values from a range:
=UNIQUE(A2:A100)
20. Sort data by column:
=SORT(A2:C100, 2, -1)
21. Filter rows by condition:
=FILTER(A2:C100, B2:B100>50000, "No results")
22. Generate a sequence:
=SEQUENCE(12, 1, 1, 1)
8. ChatGPT vs Microsoft Copilot vs Gemini for Spreadsheets
| Feature | ChatGPT | Microsoft Copilot | Gemini (Sheets) |
|---|---|---|---|
| Works inside Excel | No (separate app) | Yes (native) | Google Sheets only |
| Formula generation | Excellent | Good | Good |
| VBA macros | Excellent | Limited | Apps Script only |
| File upload analysis | Yes (Plus) | Yes (in Excel) | Yes (in Sheets) |
| Complex logic | Best | Good | Good |
| Price | Free / $20/mo | $30/user/mo (M365) | Free / $20/mo |
| Best for | Complex formulas & macros | In-app convenience | Google Sheets users |
Our recommendation: Use ChatGPT for complex formula creation, VBA macros, and data analysis. Use Microsoft Copilot for quick in-spreadsheet tasks if you already pay for Microsoft 365 Copilot. Use Gemini if you work primarily in Google Sheets. Many power users combine ChatGPT with Copilot — ChatGPT for building macros and complex logic, Copilot for day-to-day spreadsheet interactions.
Related resources
FAQ
Can ChatGPT actually write Excel formulas?
Yes, and it's one of its best use cases. Paste your column structure and describe what you want, and ChatGPT generates the formula. It handles VLOOKUP, INDEX/MATCH, XLOOKUP, array formulas, SUMIFS, and complex conditional logic flawlessly. For Excel users, ChatGPT is the fastest way to write formulas you don't remember. Free tier works fine; Plus ($20/mo) is slightly better at complex multi-step formulas.
Is ChatGPT better than Excel's built-in AI (Copilot)?
Different tools for different jobs. Microsoft Copilot ($30/user/mo) lives inside Excel and can act directly on your spreadsheet — sort, filter, create charts, find patterns. ChatGPT is a separate tool where you paste data and ask questions. Copilot is faster for in-spreadsheet work; ChatGPT is cheaper and more flexible. Many Excel users pay only for ChatGPT Plus at $20/mo and skip Copilot.
How do I give ChatGPT my Excel data without copy-pasting?
Three options. (1) ChatGPT Plus accepts file uploads — drag an .xlsx file and ask questions. (2) Code Interpreter (in Plus) can read, transform and chart Excel data. (3) For small tables, paste as text. Uploads are the fastest path for files under 20MB. For large files, use Code Interpreter to process in chunks or filter before uploading. Avoid pasting sensitive data into ChatGPT Free — paid tiers have better privacy defaults.
Can ChatGPT create Excel formulas with nested IF statements?
Yes. ChatGPT handles nested IFs 5-7 levels deep without issue. It will also suggest cleaner alternatives (IFS, SWITCH, LAMBDA) when appropriate. For formulas with more than 10 conditions, ChatGPT recommends breaking them into a lookup table — good advice. This is faster than writing nested formulas manually and catches common errors (unbalanced parentheses, wrong comparison operators).
Will ChatGPT solve #REF!, #NAME?, and #VALUE! errors?
Yes. Paste the formula and the error, and ChatGPT explains what's wrong and suggests the fix. Common fixes: #REF! = deleted cell in range, fix by updating reference. #NAME? = misspelled function name or missing quotes. #VALUE! = text where number expected, fix with VALUE() or IFERROR. ChatGPT is faster than searching online forums for error solutions and rarely wrong on Excel errors.
Can ChatGPT write VBA macros for Excel?
Yes, and surprisingly well. Describe what you want the macro to do, and ChatGPT writes working VBA code. It handles user forms, file I/O, loops, and error handling. For complex macros (automation of multi-step workflows, external API calls), GPT-5 is the best model available. Always test macros on a copy of your file before running on real data — ChatGPT occasionally uses deprecated syntax or unsafe practices.
Can ChatGPT analyse Excel data and tell me insights?
Yes, with Code Interpreter (ChatGPT Plus). Upload an Excel file and ask 'what patterns do you see?'. ChatGPT will run Python pandas analysis, generate charts, and summarise findings. For small datasets (under 1,000 rows) this replaces hours of pivot table work. For large datasets, it's faster than writing SQL. Accuracy is good but always verify insights against the raw data — Code Interpreter occasionally picks the wrong column or misses edge cases.
Is Microsoft 365 Copilot worth $30/month for Excel users?
For heavy Excel users — yes. Copilot inside Excel can draft pivot tables, generate charts, explain data trends and automate repetitive tasks without leaving the spreadsheet. For light users, ChatGPT Plus at $20/mo covers 80% of the value at 2/3 the price. The Copilot advantage is workflow speed (no copy-paste). The ChatGPT advantage is flexibility (more models, plugins, voice). Pick based on how much time you spend in Excel per day.
Can ChatGPT build Excel dashboards?
It can plan and guide dashboard construction but not build one for you. Paste your data structure, describe your dashboard goals, and ChatGPT outputs formulas for KPIs, recommends chart types, and suggests layouts. You still build in Excel. For fully automated dashboards, use Power BI Copilot ($20-40/user/mo) which generates dashboards from natural language. ChatGPT + Excel is the cheap path; Power BI Copilot is the productive one.
How accurate is ChatGPT at Excel tasks?
For formulas and VBA: 95%+ on common tasks, 80-90% on complex edge cases. For data analysis: 85-95% with Code Interpreter, lower without. For dashboards: planning is strong, execution requires manual work. The typical error mode is ChatGPT using functions that don't exist in older Excel versions (Excel 2016 vs 365). Always test on a copy first and ask ChatGPT to explain what the formula does — debugging is easier than rewriting.
Should I learn Excel or just use ChatGPT?
Learn Excel basics (formulas, references, pivot tables) and use ChatGPT for advanced stuff. Pure AI reliance is fragile — when ChatGPT is wrong, you can't tell without fundamentals. The successful pattern: use ChatGPT to 10x your productivity on tasks you could do manually if needed. In 2026 job market, Excel + AI fluency is worth more than Excel alone.