Excel is everywhere at work, yet many people quietly avoid formulas, copying numbers by hand or asking that one colleague who “knows Excel”. It doesn’t have to be that way. Most everyday spreadsheets rely on a small set of formulas that anyone can learn, and once you understand how they work, you’ll save hours every month.
AI makes an excellent spreadsheet tutor. You can describe what you want in plain English and get a formula, ask it to explain any formula piece by piece, paste in an error and find out what went wrong, or ask for practice exercises using your own kind of data. The aim is to understand, not just copy, so you can build on it. This guide takes about an hour for the basics.
Quick answer: Describe your data and what you want to calculate in plain English, and ask an AI chatbot for the Excel formula plus a step-by-step explanation. Test it on a copy of your sheet. When something breaks, paste the formula and error message and ask what’s wrong. Learn SUM, IF, COUNTIF, SUMIF, XLOOKUP and simple text and date functions first, with practice tasks.
What you’ll need
- Excel (desktop, web or mobile) or Google Sheets, which uses very similar formulas
- A spreadsheet to practise on, ideally a copy of something real
- An AI chatbot and about an hour
- Microsoft’s official Excel help pages for checking details
Step-by-step: learn Excel formulas with AI
- Learn the basics first. Ask the AI to explain how formulas work: the equals sign, cell references, ranges and the difference between relative and absolute references (with $ signs).
- Describe what you want. Explain your columns and goal in plain English, and ask for a formula with an explanation (prompt 1). For example: “total sales for the North region in column D”.
- Test on a copy. Try the formula on a copy of your sheet and check a few results by hand.
- Break it down. Ask the AI to explain each part of the formula (prompt 2). If you can explain it back, you’ve learned it.
- Fix errors. #N/A, #VALUE!, #REF! and #DIV/0! all mean different things. Paste the formula and error and ask what’s wrong (prompt 3).
- Practise. Ask for five practice exercises using the kind of data you work with (prompt 4), then check your answers with the AI.
- Build your toolkit. Keep a “cheat sheet” tab with formulas you’ve learned and what they do.
Copy-paste prompts
1. Write me a formula
I'm using [EXCEL VERSION / GOOGLE SHEETS]. My data is in [RANGE], with headers: [COLUMN NAMES AND WHAT THEY CONTAIN]. I want to [WHAT YOU WANT TO CALCULATE]. Give me the formula, tell me which cell to put it in, and explain how it works step by step in plain English. If there's a simpler alternative for older Excel versions, mention it.
2. Explain this formula
Explain this Excel formula to me like I'm a beginner, piece by piece: [PASTE FORMULA]. Tell me what each function and argument does, what the $ signs mean if there are any, and give a simple example of what it would return with sample data.
3. Fix my error
My formula is [PASTE FORMULA] in cell [CELL]. It returns [ERROR OR WRONG RESULT]. The data it uses looks like this: [DESCRIBE OR PASTE A FEW ROWS, NO SENSITIVE DATA]. What's causing the problem, and how do I fix it? Explain why it happened so I can avoid it next time.
4. Practice exercises
I work in [ROLE] and use spreadsheets for [TASKS]. I'm learning [FUNCTIONS, SUCH AS SUMIF, XLOOKUP AND IF]. Create a small sample data set (about 15 rows) I can paste into Excel, plus 5 practice tasks that get gradually harder. Don't give me the answers until I ask.
Recommended tools
- ChatGPT: clear formula explanations and practice exercises.
- Microsoft Copilot: built into Microsoft 365 Excel on eligible plans, and can work directly with your sheet.
- Gemini: works inside Google Sheets.
- Claude: good at explaining complex nested formulas.
- Grok: quick for one-off formula questions.
Tips and mistakes to avoid
- Understand before you rely on it. AI formulas can look right but be subtly wrong. Check results against a few rows by hand.
- Mention your version. Newer functions like XLOOKUP and FILTER aren’t available in older versions of Excel.
- Keep sensitive data out. Describe columns or use made-up sample rows rather than pasting real customer or salary data, and follow your company’s AI policy.
- Use tables. Formatting data as an Excel table makes formulas easier to read and update.
- Avoid hard-coding numbers. Put things like tax rates in their own cell and reference it.
- Learn keyboard shortcuts. F4 toggles $ signs; Ctrl+Shift+Enter is rarely needed in modern Excel.
- Check the official docs. Microsoft’s support pages explain each function with examples.
FAQ
Which Excel formulas should I learn first?
SUM, AVERAGE, COUNT, IF, COUNTIF, SUMIF, XLOOKUP (or VLOOKUP), and simple text and date functions cover most everyday tasks.
Do Excel formulas work in Google Sheets?
Most common formulas work the same, but some newer or specialist functions differ. Tell the AI which you’re using.
Can AI build a whole spreadsheet for me?
It can design one and suggest formulas, and tools like Copilot can work inside Excel. Learning the basics means you can check and fix it.
How long does it take to get comfortable with formulas?
Most people feel much more confident after a few short practice sessions. Fifteen minutes a day for a fortnight, using your own work data, is usually enough to master the everyday essentials.
Try it yourself with ChatGPT
I'm using [EXCEL VERSION / GOOGLE SHEETS]. My data is in [RANGE], with headers: [COLUMN NAMES AND WHAT THEY CONTAIN]. I want to [WHAT YOU WANT TO CALCULATE]. Give me the formula, tell me which cell to put it in, and explain how it works step by step in plain English. If there's a simpler alternative for older Excel versions, mention it.
Get the free AI Agents Playbook
50 practical AI workflows, plus one practical how-to a week in your inbox.
Free. No spam. Unsubscribe any time.