50 AI Prompts for Excel (Free PDF)

50 AI Prompts for Excel (Free PDF)

By Ajay Anand, Microsoft Excel MVP · Last updated:

These 50 prompts help you get better results from ChatGPT, Claude, Gemini and Copilot in Excel. They cover formulas, cleaning data, analysis, charts, VBA macros, fixing errors and learning. Replace everything in [square brackets] with your own cells and ranges, and test every result on a few rows before you rely on it.

50 AI Prompts for Excel: copy-ready prompts for formulas, cleaning, analysis, charts and macros, by Ajay Anand, Microsoft Excel MVP

Download the free PDF (4 pages)

How do you use these prompts?

Copy a prompt, replace the parts in [square brackets] and paste it into your AI tool. The more exact your ranges and sheet names, the better the answer.

  1. Name the exact cells — for example “prices in D3:D100”, not “the price column”.
  2. Say your Excel version — Microsoft 365, 2021 or 2019, so the AI doesn’t use functions you don’t have.
  3. Say what to do with blanks and errors — the most common reason AI formulas fail.
  4. Test on a few rows — and on a copy of your file for macros.

For worked examples with screenshots, see How to Use AI to Write Excel Formulas.

The 50 prompts

Write formulas (10 prompts)

  1. Write a formula in [cell] that finds the [value] for the item in [cell] from the table in [range]. Show "Not found" if it's missing. I use [Excel version].
  2. Total the values in [range] where [column] is [criteria 1] and [column] is [criteria 2]. Put the formula in [cell].
  3. Count the rows in [range] where [column] is [criteria] and [column] is more than [number].
  4. In [cell], return "[label 1]" if [condition], "[label 2]" if [condition], otherwise "[label 3]". I'll fill it down.
  5. Write a formula that returns the last value in column [letter], even when new rows are added.
  6. Calculate the number of working days between the dates in [cell] and [cell], excluding the holidays in [range].
  7. Return the top [number] [items] by [value] from [range], sorted from largest to smallest, as a dynamic array.
  8. Extract the text between the first and second "[character]" in [cell].
  9. Combine first name in [cell] and last name in [cell] with a space, and ignore blank cells.
  10. Write a formula that shows the [average/total] for the current month from the dates in [range] and amounts in [range].

Clean and prepare data (8 prompts)

  1. Clean the names in column [letter]: remove extra spaces and put each name in Proper Case. Give me a formula and the steps.
  2. Find and remove duplicate rows in [range] based on [column], keeping the first one.
  3. The dates in column [letter] are stored as text like "[example]". Convert them to real dates.
  4. Split the full addresses in column [letter] into street, city and PIN code columns.
  5. The numbers in column [letter] are stored as text. How do I convert them to numbers without retyping?
  6. Standardise the city names in column [letter] so that spelling and capitals match a single list.
  7. Fill the blank cells in column [letter] with the value from the cell above.
  8. Give me Power Query steps to combine all the files in a folder into one table with the same headers.

Analyse and summarise (8 prompts)

  1. Create a PivotTable on a new sheet that shows total [value] by [field 1] and [field 2].
  2. Which [items] had the biggest change in [value] between [period 1] and [period 2]? Show the top 5.
  3. Summarise the main trends in this data in five bullet points that a manager can read in one minute.
  4. Calculate month-on-month growth for each [category] in [range] and highlight any drop larger than [number]%.
  5. Find any unusual values or outliers in column [letter] and explain why they stand out.
  6. Build a summary table next to the data with the total, average, minimum and maximum for each [category].
  7. Compare actual vs target in [range] and add a column showing the variance and variance %.
  8. Group the ages in column [letter] into bands of [number] years and count how many fall in each band.

Charts and dashboards (6 prompts)

  1. Suggest the best chart type to show [what you want to show] and explain why in two sentences.
  2. Create a line chart of monthly [value] for [year], with a clear title and data labels on the last point.
  3. Design a one-page dashboard layout for [topic] with three KPI tiles, one trend chart and one table.
  4. Add conditional formatting to [range] that colours values above [number] green and below [number] red.
  5. Create a dynamic chart title in [cell] that shows the selected [region/month] from [cell].
  6. Build a simple Gantt chart for the tasks, start dates and durations in [range] using conditional formatting.

Macros and automation (6 prompts)

  1. Write a short VBA macro for the sheet "[sheet name]" that [task]. Add a comment on each step.
  2. Write a VBA macro that deletes every completely empty row in the used range of the active sheet.
  3. Write a VBA macro that saves every worksheet in this workbook as a separate PDF named after the sheet.
  4. Write a VBA macro that creates a new sheet listing every sheet name with a hyperlink to it.
  5. Explain what this macro does, line by line, and point out anything risky: [paste code].
  6. My macro shows "[error message]" on the line [line]. Explain the cause and fix it: [paste code].

Explain and fix (6 prompts)

  1. Explain this formula step by step in simple English: [paste formula].
  2. Why does the formula in [cell] return [#N/A / #VALUE! / #REF!]? Give the likely causes and a fixed formula.
  3. Rewrite this nested IF formula using IFS or XLOOKUP so it's easier to read: [paste formula].
  4. This formula works in row 2 but gives wrong results when I fill it down. What's wrong? [paste formula].
  5. Make this formula work in Excel 2019, which doesn't have [function]: [paste formula].
  6. Check this formula for mistakes and edge cases, such as blanks and zero values: [paste formula].

Learn and plan (6 prompts)

  1. I know [functions you know]. What are the next five Excel skills I should learn for [job role], and why?
  2. Create a 7-day practice plan to learn [Excel topic], with one small exercise each day.
  3. Give me a sample dataset of [number] rows for practising [topic], with realistic first names and Indian cities.
  4. Explain the difference between [feature 1] and [feature 2] with one example where each is better.
  5. Turn these steps into a short checklist I can keep next to my desk: [paste steps].
  6. Quiz me on [Excel topic] with five multiple-choice questions, then explain the answers.

FAQ

Which AI tool should I use these prompts with?

They work with ChatGPT, Claude, Gemini and Microsoft Copilot, in a chat or in each tool’s Excel or Google Sheets add-in.

Are these prompts free?

Yes. You can copy them from this page or download the free PDF.

Why do my results differ from the AI’s first answer?

AI tools give slightly different answers each time. Add your Excel version, exact ranges and what to do with blanks, and test the result on a few rows.

Last updated · Written by Ajay Anand, Microsoft Excel MVP