How to analyse data in Excel using AI?

Why Manual Excel Analysis Can't Keep Up — and How AI Changes the Workflow

If you've ever lost an afternoon to VLOOKUP errors, half-empty pivot tables, and a dataset that refuses to clean itself, you already know the problem: manual Excel analysis doesn't scale with the questions you're being asked. The hours spent on data prep, formula debugging, and rebuilding charts are hours not spent actually interpreting results.

AI is changing that, and fast. According to market research from Precedence Research, the AI-in-data-analytics market is projected to grow from USD 31.22 billion in 2025 to USD 310.97 billion by 2034 — a nearly tenfold expansion driven largely by GenAI automating exactly the tasks that eat your week: cleaning, transformation, and visualization.

The shift is qualitative, not just financial. Early AI helpers mostly offered chat-window advice — you'd ask for a formula, copy it, fix it, paste it. Today, AI executes inside the workbook itself. A detailed comparison of Copilot and ChatGPT captures the distinction neatly: ChatGPT is like asking an expert for insight, whereas Copilot actually executes the insight inside your spreadsheet — generating charts, highlighting trends, even running Python from a prompt like "perform a Monte Carlo simulation on our sales forecast."

Two workflow models now dominate:

• Native in-workbook assistants — Microsoft Copilot, Claude for Excel, ChatGPT add-ins — that edit, chart, and analyze directly in the file.
• Upload-and-analyze tools — like ChatGPT's Advanced Data Analysis — where AI runs Python on your exported file and returns results.

The performance gap between them is real. In one independent finance test, Claude produced a roughly 90% usable first-draft forecast in under two minutes — work that took three to four hours manually. Wharton professor Ethan Mollick has called ChatGPT with Code Interpreter "an impressive data scientist" in its own right, automating much of the complexity of quantitative analysis.

But speed claims deserve scrutiny. Vendor benchmarks conflict — GPT for Work claims its add-in processes 900 rows per minute versus Copilot's roughly 9, while Microsoft's own marketing claims the opposite — and independent reviewers note that no neutral benchmark declares a winner. The practical rule: match the tool to the task, and always verify outputs against your source data.

If you want to push this workflow further, agent skills can automate the follow-on steps — cleaning, charting, reporting. SkillzBench is building a free, independently verified directory that security-scans and benchmarks skills before you install them, so the ones you equip actually lift your results rather than your risk.

Choosing Your AI Tool: Native Assistants, Bulk Add-ins, and Upload-and-Analyze

Not all AI Excel tools are built the same, and the differences matter more than most marketing pages admit. The market has settled into three broad categories: native assistants that live inside your workbook, bulk add-ins designed to process entire columns, and upload-and-analyze platforms where you hand a file to a chatbot and let it run Python. Choosing between them comes down to four practical criteria: does it work where your data lives, can it handle bulk data, does it go beyond formulas into pivots, charts and plain-language Q&A, and does it offer model choice (a recent tool roundup frames it exactly this way).

Native assistants — Microsoft Copilot, Claude for Excel, the ChatGPT add-in, Gemini in Sheets — shine at in-workbook execution. Copilot can generate formulas, build charts, clean data and even run Python scripts from natural-language prompts, which is why one comparison put it bluntly: "ChatGPT is like asking an expert for insight, whereas Copilot actually executes the insight within your BI software" (DataStudios analysis).

Where they stumble is scale. Most built-in assistants cannot reliably apply prompts across thousands of rows. In vendor testing, Copilot processed roughly 9 rows per minute and completed only 40 of 1,000 translations, while GPT for Work claims up to 1 million rows per run — figures worth treating cautiously, since the vendor publishing them sells the competing product.

On raw analysis quality, the picture is more encouraging. In an independent finance test by f9finance, Claude produced a forecast model roughly 90% usable in under two minutes — work that would take three to four hours manually — while Copilot relied on simplistic statistical projections and ChatGPT returned structurally flawed output.

• Native assistants suit beginners who want charts, pivot tables and summaries without writing formulas.
• Bulk add-ins are built for row-by-row operations across large datasets, though speed claims are largely unverified.
• Upload-and-analyze tools excel at exploratory Python analysis on exported files, per Ethan Mollick's view that ChatGPT with Code Interpreter is "an impressive data scientist."

Here is the honest caveat: no neutral benchmark declares a winner across these categories. Querio notes that benchmark results simply don't exist yet, and recommends running the same data and prompts through multiple tools, comparing the time your team spends reviewing and correcting outputs — because "the subscription price is only part of the cost." The f9finance test proves the point: incorrect rate-volume handling overstated margin compression by about 4 percentage points in an otherwise excellent model.

Whichever category you pick, verification is non-negotiable — inspect the underlying formulas or code rather than treating output as a black box. And if you want to automate the surrounding workflow, agent skills can handle much of it; SkillzBench is building a free directory that verifies, security-scans and benchmarks skills before you trust them with your spreadsheets.

Step-by-Step: Cleaning, Visualizing, and Extracting Insights with AI in Excel

AI can now do the heavy lifting of Excel analysis — but only if your workbook is ready for it. Follow these steps and you'll go from messy raw data to decision-ready insights in a fraction of the usual time.

Step 1: Prepare your workbook. Copilot in Excel requires files stored in OneDrive or SharePoint with AutoSave enabled, and your data must sit in consistently structured tables with clear headers. Verify dates and amounts before accepting any AI changes — garbage in, garbage out still applies, and industry research warns that missing variables and fragmented data produce biased AI outputs when governance is weak.

Step 2: Clean your data with natural-language prompts. Instead of hunting through menus, describe what you want. Try prompts like:

• "Remove outliers from the Sales column that are more than three standard deviations from the mean."
• "Fill missing values in the Region column based on the closest matching record."
• "Show me how you identified duplicates and parsed the dates before applying changes."

That last prompt matters: asking the AI to explain its transformation logic — duplicates, missing values, date parsing — is a recommended verification practice, not a nice-to-have. In one independent test, incorrect rate-volume handling overstated margin compression by roughly 4 percentage points, a mistake only caught by inspecting the underlying work.

Step 3: Generate charts and pivot tables by asking. Copilot is well suited for beginners who want charts, pivot tables, and summaries without writing formulas. A prompt like "Create a pivot table showing revenue by region and quarter, then chart the trend" produces both in seconds. For deeper context, ask follow-ups such as "Highlight the three fastest-growing product categories."

Step 4: Run advanced analysis with Python. Copilot's Python support, introduced in late 2024, lets you run code inside the worksheet. A prompt like "Perform a Monte Carlo simulation on our sales forecast" produces a working script, and the Analyst agent shows the underlying code so you can inspect it rather than treating output as a black box.

Step 5: Verify everything. Save inputs, code, assumptions, and outputs together for reproducibility, and compare results against your source data. One informal head-to-head test found Claude's first-draft forecast was roughly 90% usable in under two minutes versus 3–4 hours manually — but that final 10% is exactly where human review earns its keep.

If you want to push further, agent skills can automate parts of this workflow end to end — SkillzBench is building a free directory that security-scans and benchmarks skills before you commit them to your stack.

Verify Everything: Avoiding the 4-Percentage-Point Mistake

A single methodology error can quietly distort your conclusions more than any wrong number ever could. In one independent finance test, an AI tool mishandled rate-volume logic in a forecast model, overstating margin compression by roughly 4 percentage points — a subtle mistake that looked perfectly plausible on the surface.

That's why verification is non-negotiable. The f9finance test also found that Claude's first-draft forecast was ~90% usable in under two minutes, versus 3–4 hours of manual work — but "usable" is not the same as "correct." Speed only pays off if you catch the errors the AI introduces, and the best practice guidance is blunt: never treat AI output as a black box.

So what does good verification look like in practice?

• Inspect the underlying formulas or code. Copilot's Analyst agent shows the code behind its analysis; use it. If a tool won't reveal its logic, that's a red flag, not a convenience.
• Ask the AI to explain its transformation logic. How did it handle duplicates, missing values, and date parsing? The f9finance error happened precisely because the rate-volume interaction was handled incorrectly — a question you'd only catch by asking.
• Keep a regression suite of known-answer questions. Maintain a fixed set of prompts where you already know the correct result, and run them whenever you switch tools or models. This catches silent regressions fast.
• Check governance before uploading sensitive data. Confirm storage, retention, and training-exclusion policies with your security team, and don't upload PHI until compliance confirms the service is covered by a BAA.

Governance matters more than most people realize. Copilot operates within the Microsoft 365 tenant boundary, honoring existing permissions and DLP policies, while third-party add-ins and upload-based tools require separate compliance review. As market research notes, missing variables and fragmented data can produce biased AI outputs when robust governance is lacking.

One more practical habit: save inputs, code, assumptions, and outputs together for reproducibility. And remember the querio.ai warning — no neutral benchmark declares a winner among these tools, so test with your own data and factor in the time your team spends reviewing outputs, not just the subscription price.

The same verification mindset applies to the AI tooling you build around Excel. When choosing agent skills to automate adjacent steps — cleaning, transformation, reporting — it pays to know which ones are trustworthy. SkillzBench is building exactly that: a free directory where every skill is security-scanned, version-pinned, and benchmarked, with a trust score from 0–100 so you can verify before you rely.

Build Your Own Test, Then Automate the Rest with Agent Skills

The sticker price of an AI tool tells you almost nothing about what it actually costs you. As one practical evaluation guide puts it, "the subscription price is only part of the cost. Factor in the time your team spends reviewing outputs, too."

That's why the single most reliable way to pick a tool is to build your own test. Run the same dataset and the same prompts through two or three tools, then measure how long you spend reviewing and correcting what comes back. A head-to-head finance test found Claude's first-draft forecast was roughly 90% usable in under two minutes, while Copilot leaned on simplistic statistical projections and ChatGPT produced structurally flawed output — differences a pricing page would never reveal.

Your test doesn't need to be elaborate. A structured comparison might include:

• A fixed set of prompts with known correct answers, run identically through each tool
• A timer on review and correction time, not just generation time
• A bulk-processing check, since most built-in assistants cannot reliably apply prompts across thousands of rows
• A verification pass against your source data for every output

That last point matters more than any benchmark. The same finance test showed that incorrect rate-volume handling overstated margin compression by roughly four percentage points — a silent error that only a manual check would catch.

Once you've settled on a workflow that works, the next move is automating the steps around it: cleaning, validation, reporting, the repetitive glue work that surrounds the analysis itself. That's where agent skills come in — instruction packages that extend coding agents like Claude Code, Codex, and Cursor into repeatable, multi-step workflows.

Here's the catch: skills vary wildly in quality, and published research has found that roughly 26% of skills carry at least one vulnerability. Installing an unvetted skill into a workflow that touches your business data is a risk most teams shouldn't take blind.

SkillzBench is being built to solve exactly that. It security-scans, validates, and benchmarks every skill in a sandbox — testing each one with the same model, with and without the skill, to measure the actual Lift — and publishes a trust score alongside it. Everything is free: the directory, the MCP connector, the benchmarks, and the alerts.

Skills, proven. Browse verified, benchmarked agent skills and join the SkillzBench waitlist at skillzbench.com to be first in line when it launches.