How to Use AI for Excel Automation — Complete Guide [2026]
Let’s be honest: most of us are using Excel at maybe 20% of its capacity. We know there are faster ways to do what we’re doing — better formulas, automated reports, dynamic dashboards — but learning them takes time we don’t have. So we copy-paste data manually, wrestle with VLOOKUP at 9pm, and wonder why the spreadsheet always breaks when someone else touches it.
AI changes this completely. With the right prompts, ChatGPT or Microsoft Copilot can write formulas you’d never figure out alone, generate VBA macros in seconds, explain what a complicated formula does in plain English, and help you clean messy data with one command.
Here’s what you’ll learn in this guide:
- The 5 highest-leverage AI use cases for Excel automation
- How to prompt AI to write any formula or macro you need
- A comparison of the best AI tools for Excel work
- Step-by-step walkthroughs with real prompt examples
- Advanced techniques for building fully automated workbooks
No coding background required. If you can describe what you want in plain English, AI can build it for you in Excel.
Why AI for Excel Automation Matters in 2026
The Productivity Gap
Excel users fall into two camps. Camp A spends 3 hours building a report manually every Monday morning. Camp B spends 30 minutes building an automated report once — then runs it in 5 minutes every week after that. The difference isn’t intelligence or dedication. It’s knowing how to use Excel’s automation features.
Historically, the barrier to automation was learning VBA, Power Query, or complex nested formulas. That barrier just disappeared. AI can write all of that for you. The only skill you need is the ability to describe what you want.
How AI Changes the Game
Before AI, a non-technical analyst who needed a macro to auto-format a weekly report had three options: spend a weekend learning VBA, hire a developer ($50–$150/hour), or keep doing it manually. Now they describe the macro in plain English to ChatGPT and get working code in 30 seconds.
The same is true for formulas. “SUMPRODUCT with multiple criteria filtered by date range” used to require a tutorial. Now you describe the business logic and the formula appears. This democratization of Excel power is one of the most underrated productivity shifts of 2026.
Best AI Tools for Excel Automation Compared
| Tool | Excel Integration | Formula Help | VBA/Macros | Power Query | Price |
|---|---|---|---|---|---|
| Microsoft Copilot (M365) | Native (in-app) | Excellent | Excellent | Excellent | Included with M365 ($6+/mo) |
| ChatGPT Plus | Browser-based | Excellent | Excellent | Very good | $20/month |
| Claude Pro | Browser-based | Excellent | Very good | Good | $20/month |
| Google Gemini (Workspace) | Google Sheets focus | Good | Limited | N/A | Included with Workspace |
| Excel’s built-in AI features | Native | Good | Basic | Basic | Included with M365 |
Bottom line: Microsoft Copilot is the most integrated if you’re on Microsoft 365. ChatGPT is the most versatile if you want help outside Excel too. Both are excellent. If you use Excel daily and don’t have M365, ChatGPT Plus at $20/month is the fastest path to automation.
Step-by-Step Guide: How to Automate Excel with AI
Step 1: Generate Formulas from Plain English
This is the most immediate win. Instead of hunting for the right formula or stacking nested functions, describe what you want and copy the result.
Prompt for a Complex Formula:
I'm working in Excel. I have:
- Column A: Product names
- Column B: Sales rep names
- Column C: Sale dates (formatted as dates)
- Column D: Revenue amounts
I want a formula in cell F2 that:
- Sums all revenue in column D
- Only for sales rep "John Smith" (in column B)
- Only for sales in the last 30 days (compared to today)
Write the formula and explain what each part does.
Typical AI Output:
=SUMPRODUCT((B2:B1000="John Smith")*(C2:C1000>=TODAY()-30)*(D2:D1000))
Paste it, done. No tutorial required.
Prompt for XLOOKUP with Error Handling:
Write an Excel formula using XLOOKUP that:
- Looks up the value in cell A2 within the range Sheet2!A:A
- Returns the corresponding value from Sheet2!C:C
- If nothing is found, returns "Not Found" instead of an error
- If A2 is blank, returns an empty string
Step 2: Write VBA Macros Automatically
VBA macros are the backbone of Excel automation — they can format reports, move data, send emails, and run entire workflows. AI writes them from plain English.
Prompt for a Formatting Macro:
Write a VBA macro for Excel that does the following:
1. Goes to the sheet named "Weekly Report"
2. Selects all cells in the used range
3. Auto-fits all column widths
4. Sets all headers in row 1 to bold, with a blue background (hex #003087) and white font
5. Adds a border around every cell in the used range
6. Freezes row 1
7. Saves the workbook
Add comments to explain each section.
Prompt for a Data Transfer Macro:
Write a VBA macro that:
1. Opens a specific file: C:\Reports\sales_data.xlsx
2. Copies all data from Sheet1, column A through column E, starting from row 2
3. Pastes it into the active workbook on a sheet called "Import" starting at cell A2
4. Closes the source file without saving
5. Shows a message box saying "Import complete" when done
To use VBA code from AI:
- Press Alt + F11 in Excel to open the VBA editor
- Insert > Module
- Paste the code
- Press F5 to run, or assign it to a button
Step 3: Clean Messy Data with AI
Raw data is almost always messy. AI can write the formulas and macros to clean it in minutes.
Prompt for Data Cleaning Formulas:
I have a column of email addresses in column A that are messy. Some issues:
- Extra spaces before/after the address
- Some are in ALL CAPS
- Some have "mailto:" prepended to them
Write Excel formulas to:
1. Remove leading/trailing spaces
2. Convert to lowercase
3. Remove "mailto:" if present
Combine all three into one formula in column B.
Prompt for Duplicate Detection:
Write an Excel formula for column C that:
- Marks the cell "DUPLICATE" if the value in column A appears more than once in the entire column A range
- Marks it "UNIQUE" if it only appears once
Use COUNTIF. Explain the formula.
Step 4: Build Automated Reports with Power Query
Power Query transforms repetitive report-building into a one-click refresh. AI can write the M code.
Prompt for Power Query M Code:
Write Power Query M code that:
1. Imports a CSV file from this path: C:\Data\monthly_sales.csv
2. Removes any rows where the "Revenue" column is blank or zero
3. Creates a new column called "Quarter" based on the "Date" column
(Q1 = Jan-Mar, Q2 = Apr-Jun, Q3 = Jul-Sep, Q4 = Oct-Dec)
4. Groups the data by "Quarter" and sums the "Revenue" column
5. Sorts the result by Quarter ascending
Provide the complete M code I can paste into the Advanced Editor.
Step 5: Debug Broken Formulas
Paste a broken formula with a description of what it should do and let AI diagnose it.
Prompt for Formula Debugging:
This Excel formula is giving me a #VALUE! error. Here it is:
=IF(AND(A2>DATE(2025,1,1),B2="Active"),VLOOKUP(C2,Sheet2!$A$1:$D$100,3,0),"N/A")
The intent is:
- If date in A2 is after Jan 1 2025 AND B2 says "Active"
- Then look up the value in C2 on Sheet2 and return column 3
- Otherwise show N/A
What's causing the error and how do I fix it?
Pro Tips & Advanced Techniques
Common Mistakes to Avoid
Mistake 1: Not testing AI-generated VBA before using on real data. Always test macros on a copy of your file first. AI code is usually correct but occasionally has edge-case bugs.
Mistake 2: Giving AI vague descriptions. “Make my spreadsheet better” gives nothing. “Auto-format the pivot table in column A through G with alternating row colors” gives you working code.
Mistake 3: Ignoring Power Query. Most Excel users skip Power Query entirely. It’s the most powerful automation feature in Excel and AI makes it accessible even if you can’t read M code.
Mistake 4: Not asking AI to explain the code. Always ask “explain what each line does” — this teaches you the logic so you can modify it yourself next time.
Mistake 5: One-and-done. Use AI iteratively. If the first macro doesn’t handle blank rows, say “now modify it to skip blank rows in column A” and iterate until it’s perfect.
Power User Strategies
Strategy 1: Build a macro library. Save AI-generated macros in a personal macro workbook. Over time you’ll have a library of automation code you can deploy instantly.
Strategy 2: Create a “describe your spreadsheet” system prompt. Open ChatGPT with a pasted description of your workbook structure (column names, sheet names, data types). Then ask for multiple formulas in one session without re-explaining.
Strategy 3: Use AI to document your workbooks. Paste a complex formula and ask AI to write a plain-English comment explaining it. Add these as cell notes for your future self or colleagues.
Strategy 4: Ask for error handling. Always add “include error handling so it doesn’t break if the data is missing or blank” to macro prompts. This saves hours of debugging.
Strategy 5: Use Copilot’s in-app capabilities. If you have Microsoft 365, Copilot in Excel can analyze data, suggest charts, and write formulas directly in the sidebar — no copy-paste needed.
Related: How to Use ChatGPT for Data Analysis
Frequently Asked Questions
Q: Do I need to know VBA to use AI-generated macros? A: No. You only need to know how to open the VBA editor (Alt + F11), create a module, and paste code. AI handles the actual writing.
Q: Will AI-generated Excel formulas work in Google Sheets too? A: Most standard Excel formulas (SUMIF, VLOOKUP, XLOOKUP, IF) work in Sheets. VBA macros do not — Sheets uses Google Apps Script. Tell AI “write this for Google Sheets using Apps Script” and it will adapt.
Q: Is Microsoft Copilot worth it for Excel users? A: If you’re on Microsoft 365 Business (which costs $6–$22/month anyway), Copilot for M365 adds significant value if you use Excel heavily. The in-app integration is seamless. If you’re on a personal plan, ChatGPT Plus at $20/month is more versatile.
Q: Can AI handle Excel files with thousands of rows? A: AI writes code that works on any size dataset — the formula or macro doesn’t “know” how big your data is. Just ensure your ranges are large enough to cover your data (e.g., A2:A100000 instead of A2:A50).
Q: How do I handle errors in AI-generated formulas?
A: Wrap the output in IFERROR: =IFERROR([your formula],"Error - check input"). Then paste back to AI and say “this formula sometimes returns an error — add error handling.”
Q: Can AI create Excel dashboards? A: Yes — ask AI to write VBA that creates charts, sets chart types, assigns data ranges, and formats them. It can build entire dashboards programmatically. Alternatively, describe your KPIs and ask for a step-by-step guide to building the dashboard manually.
Q: What about Python for Excel? Is AI useful there? A: Excel now has Python integration (via Anaconda). AI is extremely useful for writing Python scripts for Excel — just specify “write this as an Excel Python script using pandas.”
Excel and Data Skills Pay Off in the Job Market Professionals who combine Excel expertise with AI automation are among the most sought-after candidates in finance, operations, and analytics. Find your next career on doda
— Japan’s leading job platform with thousands of data and finance roles.
Conclusion & Call to Action
AI has removed the technical barrier between you and a fully automated Excel workflow. Whether you need a formula you’ve never been able to figure out, a macro to eliminate a 2-hour manual task, or a Power Query pipeline to clean incoming data automatically — you can get there with a well-written prompt and 5 minutes.
Key takeaways:
- Describe formulas in plain English and AI writes them — no formula syntax knowledge needed
- VBA macros can automate almost any repetitive Excel task; AI writes the code for free
- Power Query is the most underused Excel feature; AI makes the M code accessible
- Always test macros on a copy of your data before running on production files
- Iterate with follow-up prompts to refine until the code is exactly right
Want the exact prompt templates used in this guide — plus 80 more for Excel, Google Sheets, and data analysis? Grab our Complete ChatGPT Prompt Collection — it includes a dedicated “Excel & Data” section with formulas, macros, and Power Query prompts you can use immediately.
For a complete system to automate your work with AI, our AI Productivity Playbook covers Excel, email, reporting, and more in one end-to-end framework.
Related: Best ChatGPT Prompts for Productivity 2026
Disclosure: This article may contain affiliate links. We may earn a commission at no additional cost to you.
Related Tools
Format and validate JSON data → JSON Formatter Test regular expressions in real-time → Regex Tester
Create a monthly budget → Budget Planner Calculate your ideal freelance rate → Freelance Rate Calculator
Related Templates
Start using these techniques right away with our Excel templates:
- Smart Budget Tracker (Excel Template) — Automated calculations and visual dashboards
- The AI Productivity Playbook 2026 — AI-powered Excel automation workflows