# AI for Excel Automation: Real-World Scripts That Actually Work
You’ve got data. It’s in Excel. It’s messy. It needs cleaning, transforming, and reporting — daily. You don’t need another “AI will do everything” pitch. You need scripts that run tonight, fix the issue tomorrow, and don’t require a PhD in prompt engineering.
AI isn’t replacing Excel — it’s replacing the *tedium*. In 2026, tools like Microsoft 365 Copilot, Python’s `openpyxl`/`pandas`, and lightweight LLMs (e.g., Mistral 7B, Gemma 2) integrate directly into Excel workflows. But only if you know *how* to wire them together without breaking your sanity.
Let’s cut through the noise. Here’s how to automate Excel with AI — the way real engineers do it: pragmatically, reliably, and with full visibility into what’s happening under the hood.
—
## Why Excel + AI Still Needs Code (and Why Prompts Alone Fail)
Copilot in Excel is nice for ad-hoc questions like “What’s the trend in column C?” But try this:
– “Reformat all rows where column B matches regex `^INV-\d{8}$` and append status column based on a lookup table in another sheet.”
– “Run this logic: if `Date > 2026-01-01` and `Amount < 0`, set `Category = 'Refund'`, else `Category = 'Sales'`.”
Copilot will hallucinate. Or stall. Or ask for clarification *again*. Because AI doesn’t *know* your schema, your edge cases, or your data quality issues.
The reliable pattern in 2026 is: **AI for logic generation + human-tested code for execution**.
Use AI to draft a Python script, then run it with `pandas`. Or use AI to generate VBA *only if* you’re locked into legacy Windows and can’t install Python. More on that shortly.
---
## Option 1: Python + `pandas` (Best for Reproducibility & Scaling)
If you’re doing more than 50 rows or need repeatable runs, skip VBA. Go Python.
### Setup (30 seconds)
```bash
pip install pandas openpyxl python-dotenv
```
Create `excel_automator.py`:
```python
import pandas as pd
import os
from pathlib import Path
# Load Excel — handles .xlsx, .xls (via xlrd fallback), .xlsm (macros disabled by default)
def load_excel(path: str) -> dict[str, pd.DataFrame]:
“””Load all sheets into a dict of DataFrames.”””
try:
return pd.read_excel(path, sheet_name=None)
except ValueError as e:
raise ValueError(f”Unsupported file format or corrupted file: {e}”)
# Example: Clean invoice data
def clean_invoices(df: pd.DataFrame) -> pd.DataFrame:
“””Fix invoice IDs, standardize dates, infer categories.”””
# Drop completely empty rows
df = df.dropna(how=’all’)
# Normalize invoice IDs: strip whitespace, force uppercase
df[‘InvoiceID’] = df[‘InvoiceID’].astype(str).str.strip().str.upper()
# Drop rows with invalid IDs (regex check)
valid_mask = df[‘InvoiceID’].str.match(r’^INV-\d{8}$’, na=False)
df = df[valid_mask].copy()
# Convert date — handle multiple common formats
df[‘Date’] = pd.to_datetime(df[‘Date’], format=’mixed’, dayfirst=False)
# Apply category logic
df[‘Category’] = df.apply(
lambda row: ‘Refund’ if row[‘Date’] > pd.Timestamp(‘2026-01-01’) and row[‘Amount’] < 0
else 'Sales',
axis=1
)
return df
# Save back to Excel (overwrites original — use `to_excel('output.xlsx')` to preserve)
def save_excel(data: dict[str, pd.DataFrame], path: str):
with pd.ExcelWriter(path, engine='openpyxl') as writer:
for sheet, df in data.items():
df.to_excel(writer, sheet_name=sheet, index=False)
# Main pipeline
if __name__ == "__main__":
raw_path = "raw_data.xlsx"
if not Path(raw_path).exists():
raise FileNotFoundError("raw_data.xlsx not found")
sheets = load_excel(raw_path)
cleaned = {name: clean_invoices(df) for name, df in sheets.items()}
save_excel(cleaned, raw_path) # Overwrite in place
print("✅ Cleaned and saved. 0 human hours wasted.")
```
Run: `python excel_automator.py`
### Why this works:
- **`format='mixed'`** handles `01/02/2026`, `Jan 2, 2026`, `2026-01-02` in one column.
- **`dropna(how='all')`** avoids deleting rows with partial blanks.
- **`apply()`** is slow on huge data (>100k rows) — for those, use vectorized ops (see below).
### For large datasets: vectorize the category logic
“`python
# Replace the apply() call with:
cond1 = (df[‘Date’] > pd.Timestamp(‘2026-01-01’))
cond2 = df[‘Amount’] < 0
df['Category'] = np.where(cond1 & cond2, 'Refund', 'Sales')
```
~100x faster on 100k rows.
---
## Option 2: Copilot in Excel (Use *Strategically*)
Copilot works best when you give it *structured input* and *clear constraints*. Don’t ask it to “fix the sheet.” Ask it to *generate the formula*.
### Workflow:
1. Paste your raw data into a new sheet.
2. In a cell: `=AI.FORMULA("If column B starts with 'INV-' and length > 12, extract first 12 chars after ‘INV-‘; else return ‘INVALID'”)`
3. Paste the result into your sheet.
But here’s what Copilot *won’t* tell you: **AI formulas break silently**. If your data has `INV-123456789` (9 digits), the regex might grab `123456789` and truncate. Or if your locale uses commas for decimals, `VALUE()` fails.
So use Copilot *only* for:
– Generating formulas from natural language (e.g., “calculate 30-day moving average of column C”)
– Drafting Power Query M code (see next section)
Example: Ask Copilot:
> “Write M code to:
> – Load from Excel table ‘SalesData’
> – Filter rows where Date >= #date(2026,1,1)
> – Add a column ‘Quarter’ = Date.QuarterOfYear([Date])
> – Return result”
Copilot gives you M code. **Paste it into Power Query Editor → Advanced Editor → Paste → Close & Load.**
### Power Query M Code (Copilot-Generated Example):
“`powerquery-m
let
Source = Excel.CurrentWorkbook(){[Name=”SalesData”]}[Content],
FilteredRows = Table.SelectRows(Source, each [Date] >= #date(2026, 1, 1)),
AddedQuarter = Table.AddColumn(FilteredRows, “Quarter”, each Date.QuarterOfYear([Date]), Int64.Type)
in
AddedQuarter
“`
✅ Works every time.
⚠️ **Limitation**: Power Query can’t run external APIs or write to other files. Use it for *transformations*, not orchestration.
—
## Option 3: Local LLM + Excel (For Custom Business Logic)
Need to classify product descriptions? Summarize free-text feedback? Run a decision tree based on unstructured notes?
In 2026, you can run Mistral 7B or Gemma 2 locally (via `llama.cpp` or `ollama`) and pipe Excel rows into it.
### Minimal setup (macOS/Linux/Windows with WSL)
1. Install Ollama: `curl -fsSL https://ollama.com/install.sh | sh`
2. Pull a small model: `ollama run mistral`
3. Test: `ollama run mistral “Classify: ‘Refund for defective toaster’ as Refund or Sale”`
Output: `Refund`
### Now, automate with Python:
“`python
import pandas as pd
import requests
import json
def classify_text(text: str) -> str:
“””Call local Mistral model via Ollama API.”””
try:
response = requests.post(
“http://localhost:11434/api/generate”,
json={
“model”: “mistral”,
“prompt”: f”Classify this text as ‘Refund’ or ‘Sale’: ‘{text}’. Return ONLY the word.”,
“stream”: False
},
timeout=5
)
if response.status_code == 200:
result = response.json()
return result[‘response’].strip()
else:
return “ERROR”
except Exception as e:
return “API_FAIL”
# Apply to a DataFrame column
df = pd.read_excel(“feedback.xlsx”)
df[‘Category’] = df[‘Comment’].apply(classify_text)
# Save results
df.to_excel(“feedback_classified.xlsx”, index=False)
print(“✅ 500 rows classified. Took 3 minutes (LLM latency included).”)
“`
### Reality check:
– **Latency**: ~2–5 seconds per row on a 16GB RAM laptop. Not real-time.
– **Cost**: $0 (if using local CPU inference).
– **Accuracy**: ~92% on simple binary classification. Drop to ~78% on nuanced categories.
Only use this if:
– Your data is sensitive (can’t send to cloud).
– You need custom logic beyond rules.
– You’re okay with waiting.
—
## Critical Limitations (You Need to Know)
– **Copilot doesn’t preserve formatting**: It rewrites cells but strips colors, borders, and merged cells. Always test with a copy first.
– **VBA + AI = fragile**: Copilot’s VBA suggestions often use `Select`, `Activate`, or hardcoded ranges (`A1:D100`). These break when rows are added. Prefer `Range(“Table1[Column]”)`.
– **AI hallucinates edge cases**: It will suggest `VLOOKUP` for fuzzy matching. `VLOOKUP` *does not do fuzzy matching*. Use `XLOOKUP` with `match_mode=2` or `pandas.merge_asof`.
– **Excel’s 1M row limit**: `pandas` handles 10M+ rows if you use `dtype` optimization (e.g., `pd.CategoricalDtype` for strings). Excel does not.
—
## Key Takeaways
– **Use Python + `pandas`** for anything beyond trivial automation — it’s reproducible, debuggable, and scales.
– **Let AI draft, not execute**: Ask Copilot for formulas/M code, then inspect and paste *manually*.
– **Local LLMs work**, but only for batch jobs where latency is acceptable. Don’t expect real-time.
– **Always version-control your scripts** — `git commit -am “fix invoice regex”` beats “fixed something in Excel.xlsx”.
– **Test with a 5-row sample first** — AI-generated logic often misses `
