# AI Data Analysis in Excel (2026)
You’re staring at a spreadsheet with 50K rows of messy data. Pivot tables feel slow. VBA macros are brittle. You don’t have a data engineer on staff — but you *do* have Excel. And maybe a bit of AI.
Forget “AI-powered” marketing fluff. In 2026, real AI in Excel means *augmentation*, not replacement. You still own the analysis — AI just handles the grunt work: cleaning, labeling, pattern spotting, and writing formulas. This isn’t magic. It’s tooling. Let’s get into how it actually works.
## Using Excel’s Built-in AI: Copilot (2026 Reality Check)
Microsoft’s Copilot in Excel is the most accessible option — but only if you’re on Microsoft 365 Copilot (not the free tier). It’s not free. It’s not always right. But it *is* fast.
### How Copilot actually works under the hood
Copilot doesn’t run models inside Excel. It sends your context (selected cells, sheet name, formula bar, and your prompt) to Azure OpenAI. It then *rewrites* your workbook using Excel’s API — not by pasting text, but by generating valid `=XLOOKUP()`, `=FILTER()`, or `=SUMIFS()` formulas, or inserting new sheets.
**Limitations to know:**
– Only works in Excel for Microsoft 365 (desktop or web)
– Requires Azure OpenAI service enabled (admin-controlled)
– Doesn’t handle large datasets (>100K rows) well — truncation happens silently
– No local/private model option (data leaves your network)
**Try this in Excel today:**
1. Select a column of unstructured text (e.g., product names like “Wireless Mouse – Bluetooth, Black”).
2. Type in a cell: `=COPILOT(“Extract the brand name from each entry”)`
3. Hit Enter.
Copilot responds with:
“`excel
=TRIM(LEFT(SUBSTITUTE(A2,” – “,REPT(” “,100)),100))
“`
*(This is Copilot’s *code*, not a static answer.)*
That formula splits on `” – “` and grabs the first chunk. It’s not perfect — if your delimiter isn’t consistent, it fails. But it’s a starting point. Review, test, then adapt.
## Writing Python in Excel with `pyxll` (For the 20% Who Need More)
Copilot is fine for basic tasks. But what if you need clustering, time-series forecasting, or NLP? Excel can’t do this natively — unless you bring Python in.
Enter `pyxll`: a commercial add-in that embeds Python *inside* Excel. Think of it as a bridge: you write Python functions, call them from cells, and Excel renders results live.
### Step-by-step: Sentiment analysis on product reviews
1. Install `pyxll` (free trial, paid for production).
2. In Excel, press `Alt + F11` → Insert → Module → paste:
“`python
# pyxll.pyxll.py
from pyxll import xl_func
import pandas as pd
from textblob import TextBlob # pip install textblob
@xl_func(“data_range: object, return_range: str”)
def sentiment_scores(data_range, return_range):
# data_range is a pandas DataFrame from Excel
df = data_range
df[“sentiment”] = df.iloc[:, 0].apply(lambda x: TextBlob(str(x)).sentiment.polarity)
# Write back to Excel
return df[[“sentiment”]].values.tolist()
“`
3. In Excel: `=pyxll.sentiment_scores(A2:A100, “B2”)`
Boom — polarity scores (-1 to 1) for each review. You can then `=FILTER(A:B, B:B>0.3)` to find positive comments.
**Caveats:**
– `textblob` is slow on 10K+ rows. Use `vaderSentiment` for speed (it’s rule-based, not ML).
– Python environment must be identical on all machines (use `requirements.txt`).
– No GPU acceleration — CPU only.
## Automating Repetitive Analysis with `openpyxl` + LLMs
Sometimes you don’t need real-time analysis. You need a script that runs at 2 AM and emails a summary. For that, combine `openpyxl` with a local LLM (like Llama 3.2 via Ollama).
### Workflow:
1. Export Excel to CSV (faster, less metadata)
2. Send CSV to LLM with a prompt: *”Summarize sales trends by region. Output JSON.”*
3. Parse JSON, inject results back into Excel.
Here’s the script (`analyze_sales.py`):
“`python
import openpyxl
import requests
import json
import pandas as pd
# 1. Read Excel as DataFrame (via pandas + openpyxl engine)
df = pd.read_excel(“sales_2026.xlsx”, engine=”openpyxl”)
# 2. Summarize with local LLM (Ollama running on :11434)
prompt = (
“Analyze this sales data summary. ”
“Return JSON: {top_region: str, growth_pct: float, key_insight: str}”
“\n\nData:\n” + df.to_string()
)
response = requests.post(
“http://localhost:11434/api/generate”,
json={
“model”: “llama3.2”,
“prompt”: prompt,
“stream”: False
}
)
result = json.loads(response.text)[“response”]
# 3. Write back to Excel
wb = openpyxl.load_workbook(“sales_2026.xlsx”)
ws = wb[“Summary”]
ws[“B2”] = result[“top_region”]
ws[“B3”] = float(result[“growth_pct”])
ws[“B4”] = result[“key_insight”]
wb.save(“sales_2026_annotated.xlsx”)
“`
Run it:
“`bash
pip install openpyxl requests pandas
python analyze_sales.py
“`
**Why this works:**
– `openpyxl` reads/writes `.xlsx` without launching Excel.
– Ollama runs locally — no data leaves your machine.
– JSON output is type-safe and easy to parse.
**Downside:** LLMs hallucinate metrics. Always verify the JSON values against raw data.
## Cleaning Data with `pandas` + `cleanlab` (No Code in Excel)
Not every task needs Excel. Sometimes you clean *before* importing.
`cleanlab` (v2.0+, 2026) finds label errors and outliers in tabular data. Pair it with `pandas` to prep your Excel file.
### Example: Fixing inconsistent status codes
“`python
import pandas as pd
from cleanlab.experimental.ml import clean_label_ranking
from sklearn.ensemble import RandomForestClassifier
df = pd.read_excel(“orders.xlsx”)
# Convert status to numeric codes
df[“status_code”] = df[“status”].astype(“category”).cat.codes
# Train a quick model to find likely mislabeled rows
clf = RandomForestClassifier()
clf.fit(df[[“amount”, “days_ago”]], df[“status_code”])
# Get label quality scores
ranked = clean_label_ranking(
clf,
df[[“amount”, “days_ago”]],
df[“status_code”]
)
# Flag suspicious rows
suspicious = df.loc[ranked[“label_quality”] < 0.4]
suspicious.to_excel("orders_flagged.xlsx")
```
Now open `orders_flagged.xlsx` — these are rows where the model is confused. Manually verify them. This beats manual spot-checking.
**Note:** `cleanlab` assumes label noise, not missing data. For missing values, use `sklearn.impute.SimpleImputer` first.
## When AI *Fails* in Excel (And What to Do Instead)
AI in Excel has hard limits. Know them.
### Common failure modes:
- **Large datasets (>500K rows)** → Copilot times out, `openpyxl` memory spikes.
**Fix:** Use `dask.dataframe` to chunk reads, or switch to DuckDB (see below).
– **Complex logic (nested `IFS`, `LET`, `REDUCE`)** → Copilot’s formulas break edge cases.
**Fix:** Write the logic in Python, expose via `pyxll`.
– **Private data (PII, financials)** → Cloud LLMs are risky.
**Fix:** Use Ollama + `llama.cpp` or Hugging Face `transformers` with `device_map=”auto”`.
### Better alternative for big data: DuckDB + Excel
DuckDB reads Excel directly. You run SQL with AI-powered functions.
“`sql
— duckdb.sql
SELECT
region,
AVG(sales) AS avg_sales,
LLM_SUMMARIZE(description) AS insight
FROM ‘sales_2026.xlsx’
GROUP BY region;
“`
Install DuckDB CLI:
“`bash
pip install duckdb
“`
Then in Python:
“`python
import duckdb
con = duckdb.connect()
result = con.execute(“””
SELECT
region,
AVG(sales) AS avg_sales,
LLM_SUMMARIZE(description) AS insight
FROM ‘sales_2026.xlsx’
GROUP BY region
“””).df()
result.to_excel(“sales_insights.xlsx”, index=False)
“`
DuckDB’s `LLM_SUMMARIZE` is a user-defined function (UDF) that calls a local model. It’s not built-in, but the community has open-source examples.
## Key Takeaways
– **Copilot is a formula assistant**, not an analyst — always review its output.
– **Local LLMs (Ollama) + `openpyxl`** give full control and privacy for batch analysis.
– **`pyxll` bridges Python and Excel** — use it for ML, NLP, or complex algorithms.
– **Clean data first** — use `cleanlab` or `pandas` to prep before Excel.
– **Don’t force Excel for big data** — DuckDB or Polars are faster and scale better.
## Next Steps
1. **Today:** Open Excel. Paste `=COPILOT(“What’s the trend in B2:B100?”)`. Compare its formula to your own `=SLOPE(B2:B100, A2:A100)`.
2. **This week:** Install Ollama, run `llama3.2`, and write a `pyxll` function to compute moving averages in Python.
3. **This month:** Build a small pipeline: CSV → `cleanlab` → flagged rows → manual review → updated Excel.
AI won’t replace your Excel skills. It’ll replace the boring parts. Now go clean some data.
