Analyse Excel and data with AI: formulas, pivots, charts and clean data
AI can write the formula you have been fighting for an hour, clean a supplier's messy export, and turn a sales sheet into a pivot and a chart in minutes. It can also add a wrong total with full confidence. Here is the free route, the paid route, which tool fits which job, and how to check the result.
01AI is a fast bookkeeper who never sleeps, but still needs an audit
Think of AI as a new office bookkeeper who types ten times faster than anyone. Give them a box of receipts and an hour later you have a tidy sheet, totals and a chart. But they sometimes skip a row, count a header as a number or mix VAT-inclusive amounts with VAT-exclusive ones. So you keep the original receipts and you check the bottom line, every time.
02Free route vs paid route
Free: AI chat plus free spreadsheet tools
- Claude's free plan runs code on an uploaded Excel or CSV file and returns a new, formatted xlsx
- Free ChatGPT and Gemini can read a spreadsheet, explain it and suggest formulas (limits vary by plan)
- LibreOffice Calc: a full free spreadsheet with pivot tables and charts
- Python with pandas in JupyterLab: free, repeatable analysis that AI writes for you
- VisiData and OpenRefine: free tools to explore and clean large, messy files
Limit: Daily usage limits, no AI inside the Excel window, and you copy results back by hand
Paid: AI that works inside your workbook
About 20 USD a month for Claude Pro, ChatGPT Plus, Google AI Pro or Microsoft 365 Premium; Microsoft 365 Personal about 10 USD (vendor pages, 2026)
- Claude Pro: Claude for Excel add-in, works inside the open workbook with cell citations
- Microsoft 365 Personal, Family or Premium: Copilot in Excel; Premium adds Agent Mode for multi-step builds
- ChatGPT Plus: more data analysis in Python, interactive charts and tables
- Google AI Pro or a Workspace plan: Gemini side panel and the AI function in Google Sheets
- Julius AI Plus (about 20 USD): a dedicated data-analysis chat with cloud connectors
When paying is worth it: When you build or audit spreadsheets every week: quantities, quotes, costs or sales reports
03The safe way to work with data and AI
- 1CopyWork on a copy, keep the original file untouched
- 2DescribeExplain columns, units, currency, VAT and what you want
- 3AI buildsFormulas, pivot, chart or cleaning steps, in the sheet or in code
- 4CheckRow counts, totals vs source, spot-check 5 rows by hand
- 5Keep the recipeSave the prompt, formulas or Python code for next month
04The best tools for Excel and data analysis
Claude (chat and Claude for Excel)
AnthropicBest at: Reads a whole workbook, explains formulas with cell citations, fixes #REF! errors and builds clean new sheets or models.
Chat with code execution: upload xlsx or CSV, get analysis, charts and a new Excel file back
Pro about 20 USD a month (17 billed yearly): more usage and the Claude for Excel add-in (Pro, Max, Team, Enterprise)
Add-ons that help 5
- Add-onClaude for ExcelSidebar inside Excel (web, Windows, Mac): edits pivots, sorts, filters, adds conditional formatting and keeps formula links intact.paid
- SkillBuilt-in Excel (xlsx) skillAnthropic's skill that Claude uses automatically to create properly formatted spreadsheets with working formulas.included
- SettingCode execution and file creationMust be on for skills and file output; Claude computes in Python instead of guessing.included
- SettingInstructions in the add-inFixed rules for every chat, such as ILS with thousand separators and bold headers.included
- SettingProjectsKeep your price list, BOQ template and office conventions available in every chat.included
Microsoft Copilot in Excel
MicrosoftBest at: Already inside the Excel you use: formulas, PivotTables, charts and highlighting, without leaving the file.
No Copilot in Excel without a subscription
Microsoft 365 Personal 9.99 USD, Family 12.99 USD, Premium 19.99 USD a month, with AI usage limits
Add-ons that help 3
- ModeAgent ModeBuilds and edits a workbook step by step with tables, charts, PivotTables and formulas; on Premium and Copilot business licences, rolling out to more plans and regions (not yet in the EU per Microsoft).paid
- SettingAutoSave on OneDriveCopilot in Excel needs the file saved to OneDrive or SharePoint with AutoSave on.included
- SettingFrontier featuresPersonal, Family and Premium users can switch on preview features in Copilot settings.included
ChatGPT
OpenAIBest at: Upload a sheet and get Python analysis, interactive tables and charts you can explore.
Limited file uploads and data analysis, depending on model and current allowance
Plus about 20 USD a month: more data analysis and uploads
Add-ons that help 3
- ModeData analysis (Python)Reads xls, xlsx and CSV, runs pandas and shows the code it used.included
- ConnectorGoogle Drive, OneDrive, SharePointAttach the sheet straight from the cloud, where your plan allows connectors.included
- SettingProjectsKeep one job's files and rules together for monthly reports.included
Gemini in Google Sheets
GoogleBest at: For teams in Google Sheets: side panel that creates tables, formulas, charts and pivot tables, plus the AI function in cells.
The Gemini app is free; Gemini inside Sheets needs an eligible Workspace or Google AI plan
Google AI Pro 19.99 USD a month or a Workspace business plan (check current plans)
Add-ons that help 3
- Add-onAI function, =AI("prompt", range)Classify or summarise text row by row, for example tag complaints by type. Text only, up to 350 cells per run.paid
- GPT/GemYour own GemFixed rules for your reports: units, currency and column names.free
- SettingSave xlsx as Google SheetsGemini works best on native Sheets files, so convert the Excel file first.included
Julius AI
Julius AIBest at: A chat built only for data: upload files, ask in plain words, get charts and statistics.
Free plan with a daily credit allowance and lighter models
Plus 20 USD a month (16 billed yearly), Pro 45 USD
Add-ons that help 2
- ConnectorGoogle Drive, OneDrive, SharePointAvailable even on the free plan.free
- Add-onScheduled runsRepeat a weekly report automatically on paid plans.paid
LibreOffice Calc + Python pandas + VisiData
Free and open sourceBest at: Free, offline and private: AI writes the formulas or the pandas script, your computer does the work.
Everything is free: Calc, Python, pandas, JupyterLab, VisiData (GPLv3), OpenRefine
No paid plan needed
Add-ons that help 4
- Add-onPython and pandasRepeatable cleaning and analysis of thousands of rows; ask AI to write the script.free
- Add-onJupyterLabRun the script step by step and see each table, then export to Excel.free
- Add-onVisiDataOpen huge CSV or xlsx files in the terminal and get an instant histogram per column.free
- Add-onOpenRefineClean inconsistent names, such as 5 spellings of the same supplier.free
05Which tool for which job
| Job | Recommended tool | Why |
|---|---|---|
| Write or fix a complex formula | Claude for Excel or Copilot in Excel | Sees the real cells, explains the formula and traces #REF! or #DIV/0! |
| Pivot table and chart from sales data | Copilot Agent Mode, Claude for Excel or Gemini in Sheets | Builds the pivot directly in the workbook |
| Clean a messy supplier export | Claude or ChatGPT with Python, OpenRefine | Code fixes thousands of rows the same way every time |
| Quantity take-off (BOQ) sheet | Claude (chat or Excel add-in) | Builds a structured sheet with quantity, unit, rate and total formulas |
| Monthly cost or sales tracking | Python pandas script written by AI, or Claude Projects | One recipe you rerun every month |
| Tag free-text rows (complaints, notes) | Gemini AI function in Sheets | Classifies row by row inside the sheet |
| Very large files, privacy matters | Python, VisiData, LibreOffice (offline) | Data never leaves your computer |
| Quick look at a file without Excel | VisiData or Julius AI free | Open, sort and count in seconds |
06Example: a quantity take-off sheet
07Example: sales tracking with a pivot and a chart
08Example: cleaning a messy export
09Start in 5 minutes
- 1
Save a copy
Duplicate the file and add _AI to its name. Never let the AI edit the only original.
- 2
Remove what is sensitive
Delete or mask customer names, ID numbers and bank details if they are not needed for the question.
- 3
Open the right tool
In claude.ai turn on code execution and file creation, or open Copilot or the Claude add-in inside Excel.
- 4
Describe the data
Say what each column is, units, currency, VAT included or not, and what the output should look like.
- 5
Ask for formulas, not values
Write: totals must be live formulas, show me the formula you used.
- 6
Check the result
Compare row counts and the grand total with the source, and spot-check five rows by hand.
- 7
Save the recipe
Keep the prompt or the Python script in a Project so next month takes one minute.
10Do and don't
Do
- Work on a copy and keep the original file
- Ask for formulas and code you can see
- Check totals against an independent SUM
- Mask personal and financial details first
- Open only files you trust in an add-in
Don't
- Paste a total from chat into a quote without checking
- Let the AI overwrite source data
- Upload full payroll or client bank data to a free chat
- Assume a currency, unit or VAT rule the AI guessed
- Use downloaded templates blindly: they can hide instructions
11FAQ
Is the free version enough?
For occasional work, yes: free Claude or ChatGPT can analyse an uploaded file and return a new Excel file, and LibreOffice plus pandas cover the rest. Working inside the Excel window needs a paid plan.
Claude for Excel or Copilot in Excel?
Both work inside the workbook. Copilot comes with Microsoft 365 subscriptions; Claude for Excel needs a Claude Pro plan or higher and gives cell-level citations. Many offices use whichever subscription they already pay for. Check current plans.
Can I trust the numbers?
Not blindly. Ask for formulas, compare totals with the source and spot-check rows. Anthropic itself says not to use the add-in for final deliverables or audit-critical calculations without human review.
What about the privacy of financial data?
Read your plan's data settings, prefer business plans for company data, remove what is not needed, and use offline tools (Python, LibreOffice, VisiData) for the most sensitive files.
Can AI write macros or VBA?
Chat tools can write VBA code for you to paste and test, but Claude for Excel does not run macros or VBA. For repeat jobs a pandas script is often simpler and safer.