Of all the ways AI can help at work, Excel is where it saves the most time. Formulas you would have spent twenty minutes searching for, messy data that needs cleaning, a monthly summary nobody wants to write: ChatGPT, Claude, Gemini and Copilot handle all of it in seconds – if you ask the right way.
This guide gives you 12 prompts you can copy, adapt and use today. They work with any of the big assistants and with every current version of Excel, from 2019 to Microsoft 365.
The four rules for good Excel prompts
- Describe your data: sheet names, column letters, headers and a few example rows.
- Name your Excel version: otherwise the AI may suggest functions you do not have.
- Say what the result should look like: one formula, a new column, a chart, a short text.
- Test on a copy, and never paste personal data into a free or personal AI account.
Know your Excel version first
The most common reason an AI-written formula fails is simple: it uses a function your Excel does not have. Check your version under File → Account, then mention it in every prompt.
| Feature | Excel 2019 | Excel 2021 | Excel 2024 | Microsoft 365 |
|---|---|---|---|---|
| TEXTJOIN, IFS, SWITCH, MAXIFS | ✓ | ✓ | ✓ | ✓ |
| XLOOKUP, LET | – | ✓ | ✓ | ✓ |
| Dynamic arrays: FILTER, SORT, UNIQUE, SEQUENCE | – | ✓ | ✓ | ✓ |
| TEXTSPLIT, VSTACK, HSTACK, TAKE, DROP and other new text and array functions | – | – | ✓ | ✓ |
| IMAGE function, dynamic charts | – | – | ✓ | ✓ |
| Copilot and Python in Excel | – | – | – | ✓ (with the right plan) |
Excel 2024 is the most capable one-time-purchase version and covers every function used in this guide. Microsoft 365 adds Copilot, Python in Excel and the very newest functions as Microsoft releases them.
The 12 prompts
Replace the parts in square brackets with your own details. The more specific you are, the better the answer.
1. Write a lookup formula
Typical answer: =XLOOKUP(A2, Products!A:A, Products!C:C, "Not found"). In Excel 2019, the AI should switch to =IFERROR(INDEX(Products!C:C, MATCH(A2, Products!A:A, 0)), "Not found") – which is exactly why naming your version matters.
2. Explain a formula you inherited
Ideal for spreadsheets built by a former colleague. Ask a follow-up such as “How would you simplify it?” and you often get a cleaner version.
3. Fix an error
Common culprits the AI will point out: numbers stored as text, extra spaces and lookups that are not set to an exact match.
4. Clean messy text
Expected result: =PROPER(TRIM(A2)), which works in every version.
5. Split one column into several
In Excel 2024, =TEXTSPLIT(A2, " ") splits in one go. In older versions the AI will combine LEFT, MID and FIND – or suggest Data → Text to Columns, which is often quicker for a one-off job.
6. Turn text into real dates
A classic UK pitfall: Excel or the AI may read the text as US format and turn 3 October into 10 March. Always say “UK format, day first”. A safe formula for fixed-width text is =DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)).
7. Summarise by month and region
Asking for both options is a good habit: formulas update automatically, while a PivotTable is faster to build and easier to change.
8. Find and highlight duplicates
The rule will look like =COUNTIF($A$2:$A$500, A2)>1. The AI can also explain where to enter it: Home → Conditional Formatting → New Rule → Use a formula.
9. Build a live top-10 list
In Excel 2024 the answer can be as short as =TAKE(SORT(A2:B500, 2, -1), 10). In Excel 2019 you will get a LARGE and INDEX/MATCH construction that does the same job with more steps.
10. Choose the right chart
Good assistants also tell you what to leave out – 3D effects, too many colours, unnecessary gridlines.
11. Automate a repetitive task with a macro
Always test macros on a copy of your workbook, save it as .xlsm and only run code you understand. Asking for comments and a confirmation step makes the macro easier to check and safer to use.
12. Turn the numbers into a summary
This is where ChatGPT and Claude shine. Check every number in the text against your spreadsheet before you send it – AI still makes occasional mistakes with figures.
Keep your data safe
- Use sample rows. To write a formula, the AI only needs the structure, not your real customers. Replace names and amounts with dummy values.
- Remove personal data before uploading a file. Under UK GDPR you are responsible for where customer data goes, and a personal AI account is not the right place for it.
- Use business plans for company work. ChatGPT Business, Claude Team, Gemini in Google Workspace and Microsoft 365 Copilot offer stronger data protection than free accounts.
- Keep the original. Work on a copy, so a wrong formula or macro cannot damage your only version.
Copilot in Excel or a separate assistant?
With Microsoft 365, Copilot works directly inside your workbook. It can see your tables, create formulas in place and, with the newest updates, use Python for deeper analysis. That is convenient but requires a subscription. With Excel 2019, 2021 or 2024, a separate assistant in the browser does almost the same job: you describe the data, paste in the formula and check the result. For most everyday tasks the difference is a few clicks, not a different level of quality. Our comparison of ChatGPT, Claude, Gemini and Copilot shows which assistant suits which work.
Get the newest Excel functions without a subscription
Excel 2024 includes XLOOKUP, dynamic arrays, TEXTSPLIT, TAKE and the IMAGE function – everything the prompts above use. It comes with Office 2024, permanently licensed and delivered by email straight after payment.
Office 2024 Professional Plus → · Office 2024 Standard → · All Office licences →
Frequently asked questions
What is the best AI for Excel?
For formulas and explanations, ChatGPT, Claude, Gemini and Copilot are all strong. Copilot is the most convenient because it works inside the workbook, but it needs Microsoft 365. Claude and ChatGPT are particularly good at explaining complex formulas and writing macros.
Can ChatGPT edit my Excel file?
You can upload a workbook to ChatGPT, Claude or Gemini and ask for analysis or changes. They return results or a new file; your original stays untouched. Copilot in Excel, by contrast, works directly in the open workbook.
Why does the AI’s formula not work in my Excel?
Usually for one of three reasons: the function does not exist in your Excel version, your data looks different from what you described, or your Excel uses a different list separator – some European language settings expect semicolons instead of commas. Tell the AI your version and region, and paste the exact error message.
Does Excel 2024 have Copilot?
No. Copilot in Excel requires a Microsoft 365 subscription. Excel 2024 has the modern formula functions used in this guide, such as XLOOKUP, FILTER and TEXTSPLIT, and you can use any AI assistant in the browser alongside it.
Is it safe to use AI with company spreadsheets?
Yes, if you follow two rules: use a business plan with data protection for real company data, and remove personal data before you share anything. For writing formulas, anonymised sample rows are all the AI needs.
Sources: Microsoft – What’s new in Office 2024, Microsoft Excel Blog – New text and array functions. Last updated: 10 October 2026.
