AI and Excel: formulas, analyses, spreadsheets
Written with AI and checked against the sources by a second AI. As of November 16, 2026. More in the notes on AI.
Excel can do almost anything if you know the right formula. That is exactly where an AI helps: it builds formulas from your description, explains old formulas and shows you the way through an analysis. For the result to be right, it needs precise details, and you need a fixed routine for checking.

Key points
- Name the program, version, language and columns. Otherwise the AI guesses function names and separators.
- Give it sample rows with made-up values instead of real data with names.
- Try new formulas and macros in a copy first.
- Work out two rows by hand, one of them at a boundary, such as exactly on the cutoff date.
- Built-in assistants like Copilot see your file. Whether you may use them with company data is up to your employer.
What an AI can take on in Excel
An AI like ChatGPT, Claude, Gemini or Copilot knows Excel’s functions well. What it doesn’t know is your spreadsheet: which column means what, which rule applies in your company, which values may be missing. Good to hand over are:
- Building formulas from a description
- Explaining long formulas part by part
- Describing the steps for a PivotTable or a chart
- Ideas for cleaning up messy data
- Drafting macros or queries that you check afterwards
The rules, the data and the judgment of whether a figure is plausible stay with you. An AI can miscalculate or misread a column, and it sounds just as sure of itself as usual.
In the trial lesson you put your first instruction together and get feedback. No account, in a few minutes. Start the trial lesson
Having formulas built
Arjun is a controller at a furniture factory. Every month he needs the revenue per region, but only for paid invoices. He used to filter and add for this. Now he describes his spreadsheet to the AI so precisely that it doesn’t have to guess.
I use Excel for Microsoft 365 in English.
Sheet “Invoices”: column A invoice date, column B region (North, South, West), column C net amount in euros, column D status (open or paid). Data from row 2, about 3,000 rows.
On the sheet “Summary” I need the total of paid amounts per region for one month. I enter the month in cell B1 as the first day of the month.
Give me the formula for region North and explain each part in one sentence.
Tell me what happens if column D says “Paid” with a capital P.The details on the program and language are not a minor point. Depending on the setting, the same function is called SUMIFS or, in German Excel, SUMMEWENNS, and the parts are separated by commas or semicolons. Some functions also exist only in newer versions.
| Task | Microsoft 365, Excel 2021/2024 | Excel 2019 and older |
|---|---|---|
| Look up the price for an item number | =XLOOKUP(A2,Prices!A:A,Prices!B:B,"missing") | =IFERROR(VLOOKUP(A2,Prices!A:B,2,FALSE),"missing") |
| List without duplicates | =UNIQUE(B2:B3000) | Data > Remove Duplicates, in a copy |
Both formulas in the first row return “missing” if the item number doesn’t exist. Which one you need depends on your version. If you don’t tell the AI, you may get a formula that only shows an error on your computer.
Understanding old formulas
Often the formula is already there; it’s just that nobody understands it anymore. Instead of deleting it, have it explained. Copy only the formula and write what is in the columns. You don’t need the whole file.
Explain this Excel formula to me part by part, one sentence per part:
=IF(AND(D2="paid",A2>=$B$1),C2*0.97,C2)
Column A is the invoice date, C the amount, D the status, B1 a cutoff date.
Then tell me in which case the formula returns a result you wouldn’t expect.The answer shows that paid invoices from the cutoff date on count three percent less. Whether this rule still applies in your company, the AI doesn’t know. You clarify that with the person responsible for the spreadsheet before you change anything.
Sample rows instead of real data
An AI understands your spreadsheet better if it sees a few rows. You don’t need real data for that. Write three to five rows with made-up values in the same form, including a tricky one, such as with an empty cell or a typo.
This is what my spreadsheet looks like (made-up values):
A: Date | B: Customer | C: Amount | D: Status
2026-11-03 | C-001 | 120.00 | paid
2026-11-05 | C-002 | 80.50 | open
2026-11-07 | C-003 | | paid
What should my formula do with an empty cell in C? Suggest two options and name one drawback of each.Replacing customer names with codes doesn’t make real data anonymous as long as you have the list with the names. For sample rows, made-up values are therefore the better choice. Upload real files with people in them only to an AI your employer has approved for this.
Cleaning up data
Ingrid is a bookkeeper at a construction company. The old software exports lists with spaces at the edges, dates as text and amounts in a format her Excel doesn’t read as numbers. She has the AI describe a way to fix this instead of changing every cell by hand.
In Excel in English I have a list of about 800 rows from an export.
Problems: column A contains dates as text, in the form 03.11.2026. Column C contains amounts like 1250,00 or 1.250,00 with a comma as the decimal separator. Column B has spaces at the beginning and end.
Describe step by step how to clean this up without overwriting the original columns.
If it is easier with Power Query, describe that way too. Ten steps per way at most.Ingrid works in a copy and afterwards compares the total of the amounts before and after the cleanup. If they don’t match, she looks for the rows that behaved differently.
Preparing analyses
For PivotTables and charts, the AI is a good guide when you don’t know where to start. Lars runs a bike rental and wants to know on which weekdays it is busiest.
I have a spreadsheet in Excel with all rentals in 2026: column A date, column B bike type, column C duration in hours.
I want to see how many rentals there were on each weekday, broken down by bike type.
Describe which helper column I need and how to build the PivotTable.
Suggest a suitable chart and say in one sentence why.Many AI services can also analyze uploaded spreadsheets themselves. That is practical, but check every result at a point you know. Does the total number of rentals match the number of rows in Excel? Only then do you trust the details.
Macros and queries
An AI also writes macros in VBA or steps for Power Query for you. That saves time but carries a risk: a macro can delete or overwrite data without asking.
- First have the AI explain what the macro does, line by line.
- Test it in a copy of the file, never in the original.
- Check that it only changes the sheets and ranges it is supposed to change.
- Save the working version with a date so you can go back.
AI directly in Excel
These days the AI often sits right inside the program, as of November 2026. Copilot is available in Excel with Microsoft 365, for personal use and at work. For ChatGPT there is an add-in for Excel and Google Sheets, and for Claude, with a paid plan, one for Excel. Gemini works in Google Sheets with Google Workspace.
A built-in assistant sees the file you are working in, with all the names and amounts in it. With a work account, your employer’s contracts with the provider apply. Whether you may use an add-in or a personal subscription with company files is decided by IT or your manager, not by you alone. Which service fits your work is compared in the guide Which AI is right for me?.
How to check every formula
- Paste the formula into a copy of the file first.
- Work out two rows by hand, one of them at a boundary, such as exactly on the cutoff date or at zero.
- Try an empty cell and a typo. What does the formula show then?
- Compare a total with a value you know for sure.
- Only when everything is right do you copy the formula into the real spreadsheet.
A numerical example: for a threshold of “discount from 500 euros”, you check one row with 499.99 and one with exactly 500 euros. That is where most errors lie, such as a “greater than” instead of “greater than or equal to”.
How to practice this
You practice formulas, cleaning up data and checking with edge cases in the learning app “KI können” in short lessons with examples from the office, and you put the instructions to work in your own AI, including your employer’s. The trial lesson needs no account.
Sources
- Microsoft Support, XLOOKUP function (opens in a new tab)
- OpenAI, ChatGPT for Excel (update of May 5, 2026, Excel and Google Sheets) (opens in a new tab)
- Claude Blog, Claude in Excel, PowerPoint, Word and Outlook (May 7, 2026) (opens in a new tab)
- Microsoft Learn, Data, Privacy, and Security for Microsoft Copilot (opens in a new tab)
- Google Workspace, Generative AI in Google Workspace Privacy Hub (opens in a new tab)
Information as of November 16, 2026. Laws and offers can change; this article is not legal advice.
Read more
Try it now
The trial lesson with examples for Financial controller. No account, a few minutes.
Start the trial lesson