Excel × AI
Top 10 Excel formulas you can replace with AI prompts — and save hours every week.
VLOOKUP. Nested IFs. SUMIFS. They work — but they take time, and one wrong bracket breaks the whole spreadsheet. Here are ten Excel jobs you can hand to ChatGPT or Gemini instead, with a copy-paste prompt for each.
Estimated time saved: 3–4 hours a week for most people who work in spreadsheets daily.
- 1.
VLOOKUP / XLOOKUP
The pain: Matching values across two sheets and remembering which column index to use.
Try this prompt"I have two lists. List A has customer names and emails. List B has customer names and order totals. Match them by name and give me one table with name, email and total. Flag any names that don't match."
⏱ Saves about 10–15 min per merge.
- 2.
Nested IF statements
The pain: Stacking 4+ IFs to bucket values, then debugging the one bracket that's off.
Try this prompt"Here is a list of deal sizes. Bucket each one as Small (<$1k), Medium ($1k–$10k), Large ($10k–$100k) or Enterprise ($100k+). Return the original list with a new column called Tier."
⏱ Saves about 20 min per ruleset.
- 3.
SUMIFS / COUNTIFS
The pain: Summing values across multiple conditions without breaking the cell references.
Try this prompt"From this sales data, give me total revenue per region per quarter for 2025. Format it as a clean table with regions as rows and quarters as columns. Add a grand total row and column."
⏱ Saves about 15 min per report.
- 4.
TEXT / CONCATENATE / TEXTJOIN
The pain: Stitching first name, last name, title and company into a clean string.
Try this prompt"Turn this list of names and titles into polite email opening lines like 'Hi Sarah, I noticed you lead marketing at Acme…'. Keep the tone warm and professional."
⏱ Saves about 30 min per outreach batch.
- 5.
LEFT / RIGHT / MID / FIND
The pain: Extracting the bit before the @, after the slash, or between two dashes.
Try this prompt"From this list of email addresses, extract the company domain (the part after @, without the .com). Return two columns: email and company."
⏱ Saves about 10 min per cleanup.
- 6.
Pivot tables
The pain: Setting up rows, columns, values and filters just to answer one question.
Try this prompt"Look at this transaction data. What were my top 5 product categories by revenue last month, and how did each one change vs. the month before? Show numbers and percentage change."
⏱ Saves about 20 min per analysis.
- 7.
Conditional formatting rules
The pain: Building 6 colour-coding rules to spot outliers.
Try this prompt"Scan this list of monthly expenses and call out anything that looks unusual — sudden jumps, duplicate charges, or vendors I only paid once. Explain why each one stood out."
⏱ Saves about 25 min per audit.
- 8.
DATEDIF / NETWORKDAYS
The pain: Working out 'days between', skipping weekends, then arguing with timezones.
Try this prompt"For each row, calculate working days between Start Date and End Date (skip weekends, ignore public holidays). Also tell me which projects took longer than 30 working days."
⏱ Saves about 15 min per schedule.
- 9.
Data cleanup (TRIM, CLEAN, PROPER, SUBSTITUTE)
The pain: Fixing trailing spaces, weird capitalisation, and inconsistent country names.
Try this prompt"Clean this contact list: trim extra spaces, fix capitalisation in names, standardise country names (USA, US, U.S. → United States), and remove obvious duplicates."
⏱ Saves about 45 min per list.
- 10.
Forecasting (TREND, FORECAST, regression)
The pain: Setting up a forecast formula you'll second-guess anyway.
Try this prompt"Here are 12 months of revenue. Project the next 3 months based on the trend. Tell me the assumptions you made, and give me a best-case and worst-case scenario."
⏱ Saves about 30 min per forecast.
How to use these prompts
- 1. Copy your data from Excel (Ctrl/Cmd + A, then Ctrl/Cmd + C).
- 2. Open ChatGPT, Gemini or Claude.
- 3. Paste the prompt above, then paste your data underneath.
- 4. Ask for the result as a table you can paste back into Excel.
For sensitive data, use a business/enterprise plan that doesn't train on your inputs, or anonymise names and figures first.
Frequently asked
Can AI really replace Excel formulas?
For one-off analysis, cleanup and quick answers — yes. For recurring automated reports, formulas still win. Use AI for thinking, Excel for repeatable pipelines.
Which AI is best for Excel tasks?
ChatGPT, Gemini and Claude all handle spreadsheet questions well. Paste a sample of your data and describe what you want.
Do I need to learn prompting?
A little. Good prompts describe the data, the task, and the output format. One Prompt a Day sends one beginner-friendly prompt every morning so you build the habit without studying.