60 AI Prompts for Excel That Actually Work (Copy, Paste, Get Results)

The most common feedback I get after my Excel AI workshops is this: "I know I should be using AI more, but I don't know what to ask it." That's a prompt problem, not a knowledge problem.

Every AI tool — whether it's ChatGPT, Claude, Microsoft Copilot, or Gemini — is only as useful as the instruction you give it. A weak prompt produces a generic answer. A well-structured prompt produces a formula you can paste directly into your spreadsheet, a macro that runs on the first try, or a step-by-step data cleaning plan tailored to your exact layout.

This is the library I wish I'd had when I started. Sixty prompts, tested in real workshops and real spreadsheets, organized by the task you're trying to do. Replace the text in [brackets] with your actual column names, data types, and requirements. Every prompt here works with ChatGPT, Claude, Copilot, or Gemini.

The One Thing That Makes a Prompt Work

Before the list: there is a single principle that separates a useful AI prompt from a useless one for Excel work.

Describe your spreadsheet layout first. Always.

Don't just say "write a formula to sum by category." Say "I have an Excel spreadsheet where column A has product categories, column B has month names, and column C has revenue figures. I need a formula in column D that sums all values in column C where column A equals a specific category." That second version gets you a formula. The first gets you a generic example that doesn't match your columns.

Every prompt in this library is structured this way. You'll see the pattern immediately.

Formula Writing Prompts (12)

These are the prompts I use most often in workshops when participants hit a formula wall.

1. Multi-Condition Sum

"I have an Excel spreadsheet where column A has [category names], column B has [sub-category names], and column C has [numeric values]. Write a formula in column D that sums all values in column C where column A equals [value1] AND column B equals [value2]. I'm using Microsoft 365."

The AI will give you SUMIFS. Ask it to follow up with a version that references dropdown cells instead of hardcoded values — that's what makes dashboards dynamic.

2. Lookup with a Fallback

"I have a master list in Sheet2 where column A has [unique IDs] and column B has [corresponding names]. In Sheet1, column A has IDs. Write a formula in Sheet1 column B that looks up the name from Sheet2. If no match is found, show 'Not Found' instead of an error."

For a deeper comparison of when to use VLOOKUP, XLOOKUP, or INDEX-MATCH for lookups like this, the VLOOKUP vs XLOOKUP guide is worth reading alongside this.

3. Extract Text from a Pattern

"Column A has entries formatted as '[First Part] - [Second Part] - [Third Part]'. Write a formula that extracts just the [first/second/third] part from each cell. I'm using [Excel version]."

On Microsoft 365, the AI will likely offer TEXTBEFORE and TEXTAFTER — far simpler than the MID/FIND approach needed in older versions. Always mention your Excel version.

4. Working Days Between Dates

"Column A has start dates and column B has end dates, both formatted as Excel dates. Write a formula that calculates the number of working days between them, excluding weekends but not public holidays."

If you also want to exclude Indian public holidays, follow up with: "Now add a third range in column D:D that lists holiday dates and exclude those too."

5. Dynamic Ranking with Ties

"Column B has performance scores for [number] people. Write a formula that ranks each person from highest to lowest, giving tied values the same rank. I want it to handle ties correctly — if two people tie for 3rd, the next rank should be 5th, not 4th."

6. Nested IF Replacement

"I have this formula that's hard to read: =IF(A2>200,'Very High',IF(A2>100,'High',IF(A2>50,'Medium','Low'))). Rewrite it using IFS or SWITCH to make it more readable, and explain the difference between the two approaches."

7. Dynamic Unique List

"Column A has [number] rows of [data type] with many duplicates. Write a formula that produces a unique, sorted list of values in a separate column, updating automatically when new data is added. I'm on Microsoft 365."

The AI will use SORT(UNIQUE(...)) — part of the dynamic array functions covered in the advanced formulas guide.

8. Running Total

"Column B has daily [sales/expenses/units] values from row 2 to row 200. Write a formula in column C that shows a running total — the cumulative sum up to and including each row. It should still work correctly if I add more rows later."

9. Cross-Sheet Aggregate

"I have 12 sheets named Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec. Each sheet has a total in cell [cell reference]. Write a formula on a Summary sheet that sums that cell across all 12 sheets."

10. Text-to-Number Conversion

"Column A has numbers that are stored as text — they're left-aligned in the cells and my SUM formula returns 0. Write a formula that converts them to real numbers so I can use them in calculations, without changing the original column."

11. Weighted Average

"Column B has [scores/ratings] and column C has weights for each one. The weights don't always add up to exactly 100. Write a formula that calculates the correct weighted average of the scores using the weights in column C."

12. Conditional Formatting Formula

"Write a conditional formatting formula for column C that highlights any cell red if its value is more than [percentage]% below the average of the entire column. Give me the exact formula to paste into the conditional formatting rule."

For more conditional formatting techniques beyond simple rules, the conditional formatting tips guide goes much deeper.

Data Cleaning Prompts (10)

Raw data is rarely clean. These prompts handle the most common issues I see in every corporate workshop I run.

13. Standardise Name Casing

"Column A has people's names entered inconsistently — some are ALL CAPS, some are all lowercase, some are Mixed. Write a formula in column B that converts all of them to Proper Case (first letter of each word capitalised), and flag any entries that are suspiciously short (under 4 characters) that might be data errors."

14. Keep Only the Most Recent Record per ID

"I have [number] rows of [customer/employee/product] records. Column A has IDs, column B has dates, column C onwards has other fields. Give me step-by-step instructions to keep only the most recent record for each ID and delete the older duplicates — without using a macro."

15. Split a Full Name Column

"Column A has entries in the format 'FirstName LastName'. Some entries have middle names as well — 'FirstName MiddleName LastName'. Write formulas to split this into separate columns for first name and last name, handling both two-word and three-word names."

16. Strip Non-Numeric Characters from Phone Numbers

"Column A has phone numbers in inconsistent formats — some have dashes, some have spaces, some have +91 country codes, some have brackets. Write a formula that returns only the numeric digits, no spaces or special characters. I'm on Microsoft 365."

On Microsoft 365, the AI will likely use TEXTSPLIT and TEXTJOIN in a clever combination. On older versions it will give you a nested SUBSTITUTE approach.

17. Flag Incomplete Rows

"I have a data entry sheet with columns A through [letter]. Write a formula in column [next letter] that checks whether any of the required columns in that row are empty, showing 'Incomplete' if any are blank and 'Complete' if all are filled."

18. Diagnose Why VLOOKUP Is Returning Wrong Results

"My VLOOKUP is returning values that don't match. The formula is =VLOOKUP(A2, Sheet2!A:C, 2, FALSE). I've checked the data visually and the IDs look identical, but VLOOKUP disagrees. List every possible cause of this and give me a formula I can use to diagnose which one it is."

This prompt solves one of the most frustrating Excel problems — mismatched data types, invisible spaces, and leading zeros — in minutes. For fixing formula errors more broadly, see the guide to debugging formulas with AI.

19. Validate Email Addresses

"Column A has email addresses submitted by users. Write a formula in column B that checks if each entry contains exactly one @ symbol and at least one dot after the @ symbol, returning 'Valid' or 'Invalid'. It shouldn't reject emails just because they look unusual — only catch obvious formatting errors."

20. Convert Text Dates to Real Dates

"Column A has dates in the text format [DD/MM/YYYY or MM-DD-YYYY or other format] but Excel isn't recognising them as dates — they're stored as text strings. Give me a formula that converts them to real Excel serial dates that I can format and use in date calculations."

21. Merge Updated Data from Two Sheets

"Sheet1 has a customer list with [Name in column A, Email in column B]. Sheet2 has the same customers but with their updated [phone number/address/status] in column [letter]. Write a formula to pull the updated information from Sheet2 into a new column in Sheet1, matching on the customer name."

22. Flag Rows with Outlier Values

"Column C has [number] numeric values. Write a formula in column D that flags any value that is more than 2 standard deviations from the mean of the entire column, showing 'Outlier' or blank. Also give me the formulas to calculate the mean and standard deviation separately so I can show them on a summary row."

VBA and Macro Prompts (10)

You don't need to know VBA to write macros anymore. These prompts produce working code. If you want a deeper introduction to the approach, the guide to generating VBA macros with AI covers how to test and install the code safely.

23. Auto-Format on Open

"Write an Excel VBA macro that runs automatically when the workbook opens. It should: bold the header row (row 1) with a dark blue background and white text, auto-fit all column widths, freeze row 1, and set the zoom level to 90%."

24. Export Sheet to PDF

"Write a VBA macro that exports the currently active sheet as a PDF file. Save it to the same folder as the workbook. Name the file using the sheet name plus today's date in YYYYMMDD format. Show a message box confirming the export location when done."

25. Loop Through Every Sheet

"Write a VBA macro that loops through every sheet in the workbook (except a sheet called 'Summary') and clears the contents of column [letter] on each sheet from row 2 downward, leaving the header in row 1 intact."

26. Create a Values-Only Snapshot

"Write a macro that copies the range [A1:G500] on Sheet1 and pastes it as values only (no formulas, no formatting) onto a new sheet. Name the new sheet 'Snapshot [today's date]'. If a sheet with that name already exists, delete it first before creating the new one."

27. Send an Email via Outlook

"Write a VBA macro that sends an email via Outlook using values from the active sheet: recipient email from cell B2, subject line from cell B3, email body from cell B4. Attach the current workbook file. Add a confirmation prompt before sending so I don't accidentally fire it off."

28. Delete Rows Matching a Condition

"Write a macro that scans column A from row 2 to the last data row and deletes any row where the cell is blank or contains the exact text 'DELETE'. It must scan from the bottom upward to avoid skipping rows when deleting. Show a count of how many rows were deleted when it finishes."

29. Auto-Generate a Summary Sheet

"I have a sheet called 'Raw Data' with columns: [Date], [Region], [Salesperson], [Product], [Revenue]. Write a macro that creates a new sheet called 'Summary' and populates it with total Revenue by [Region], sorted from highest to lowest. If the Summary sheet already exists, clear and rebuild it."

30. Protect All Sheets with a Password

"Write two short VBA macros — one that protects every sheet in the workbook with the password '[your password]' (allowing users to select cells but not edit them), and a second macro that unprotects all sheets with the same password. Put both in the same module."

31. Highlight Duplicates in a Column

"Write a VBA macro that scans column [letter] for duplicate values and highlights every duplicated cell in yellow. Only flag values that appear more than once — leave unique values with no fill colour. Show a count of duplicate cells found."

32. Import a CSV File Chosen by the User

"Write a macro that opens a file browser so the user can select any CSV file from their computer. Import it into a new sheet in the current workbook. Name the new sheet after the CSV filename (without the .csv extension). If a sheet with that name already exists, ask the user whether to replace it or cancel."

Data Analysis Prompts (10)

These go beyond individual formulas — they help you structure the analysis, not just calculate numbers.

33. Identify a Trend

"I have monthly [sales/revenue/usage] data in column A (months as text) and column B (numeric values) for the past [number] months. Write the formulas I need to: calculate the overall trend direction, identify the best and worst months, and flag any month where the value dropped more than [percentage]% from the previous month."

34. Year-over-Year Growth

"Column A has dates (daily entries) and column B has [revenue/units/calls]. Write formulas that calculate total [metric] for the current calendar year and total for the prior calendar year, then calculate year-over-year growth as a percentage. Assume today's date is dynamic — use TODAY() not a hardcoded year."

35. Pivot Table Setup Advice

"I have a dataset with these columns: [list your column names and what data each contains]. I want to answer this specific question: [state the business question]. Tell me exactly how to set up a pivot table — which fields go in Rows, Columns, Values, and Filters — and which aggregation to use for the Values field."

Pairing the AI's pivot setup advice with the techniques in the pivot tables guide gives you a complete workflow.

36. Correlation Analysis

"Column B has [variable 1, e.g. advertising spend] and column C has [variable 2, e.g. weekly sales] for [number] periods. Write the formula to calculate the Pearson correlation coefficient between them. Then explain what a result of [e.g. 0.73] means in plain language, and what I should and shouldn't conclude from it."

37. Revenue Forecast

"Column A has month labels from [start date] to [end date] and column B has actual revenue figures. Write a formula using FORECAST.ETS to project revenue for the next [number] months. Explain what the seasonality parameter controls and what value I should use for my data."

38. Pareto Analysis (80/20 Rule)

"I have a list of [customers/products/issues] in column A and their associated [revenue/frequency/cost] in column B. Walk me through how to sort, calculate cumulative percentages, and build a Pareto chart in Excel that shows which items account for 80% of the total — including the exact steps for adding the cumulative percentage line to the chart."

39. Two-Variable Sensitivity Table

"I have a financial model where cell [B5] has [input variable 1, e.g. selling price] and cell [B6] has [input variable 2, e.g. unit cost]. Cell [B10] calculates [output metric, e.g. gross margin]. Walk me through setting up a two-variable Data Table that shows the output for [variable 1] ranging from [min to max in steps] and [variable 2] ranging from [min to max in steps]."

This is one of the most powerful What-If tools in Excel. The What-If analysis guide covers Scenario Manager and Goal Seek alongside Data Tables.

40. KPI Dashboard Formula Design

"I'm building a KPI dashboard for a [type of team/business]. My raw data has these columns: [list columns]. Suggest the 5 most important KPIs to show, and for each one: write the formula, explain what it measures, and tell me the ideal chart type to visualise it on the dashboard."

41. Cohort Retention Setup

"I have a customer transactions table with columns: CustomerID, FirstPurchaseDate, RepeatPurchaseDate. Explain how to structure this data in Excel to calculate monthly cohort retention — what the rows and columns of my output table should represent, and which formulas to use."

42. Segment Performance Comparison

"My dataset has [number] rows with a [Region/Department/Category] column in column [letter] and a [metric] column in column [letter]. I want to compare the [mean/median/sum] of the metric for each segment side by side. What's the most efficient way to do this — formula approach vs. pivot table — and which should I use for [my specific goal]?"

Charts and Dashboard Prompts (8)

AI is surprisingly good at chart advice — not just building them, but helping you choose the right one.

43. Dynamic Chart That Expands Automatically

"I have a summary table with [Category] in column A and [Values] in column B. New rows are added each month. Give me two approaches to making a chart that automatically includes new data without me manually updating the chart's data range — one using an Excel Table, one using named ranges with OFFSET."

44. Conditional Bar Chart Colours

"I have a bar chart showing monthly [actual vs. target]. I want bars that exceed target to appear green and bars that fall short to appear red. Walk me through how to achieve this without VBA — using a helper column approach."

45. Sparklines for a Summary Table

"Column A has [product/region/person names] and columns B through M have monthly values for each row (Jan to Dec). Give me step-by-step instructions to add sparkline charts in column N that show the trend for each row, and how to set the axis scaling so the sparklines are comparable to each other."

46. Interactive Dropdown-Driven Chart

"I want a dashboard where a dropdown in cell [B2] lets me select a [region/product/team], and a chart below automatically updates to show only data for that selection. Walk me through building this using Data Validation for the dropdown, and either FILTER (Microsoft 365) or INDEX-MATCH for the chart data."

47. Waterfall Chart for P&L

"I have a P&L breakdown with line items in column A and values in column B — revenue at the top, then cost items as negatives, then a net profit total at the bottom. Give me step-by-step instructions to create a waterfall chart where positive contributions are green, negative are red, and the final total bar is a different colour."

48. Gauge Chart Without Add-ins

"I want to show a single KPI value (e.g. [72] out of [100]) as a gauge/speedometer chart in Excel using only built-in chart types — no add-ins or third-party tools. Walk me through building one using a doughnut chart, including the helper data structure I need to set up."

49. Colour-Scale Heatmap

"I have a grid where column A has [row labels], row 1 has [column labels], and the cells in between have numeric values. I want to apply a colour scale heatmap using conditional formatting — the highest value darkest green, the lowest value white. Walk me through the exact steps, including how to apply it across the entire grid in one rule."

50. Dashboard Layout Advice

"I'm building a one-page Excel dashboard for [describe audience, e.g. a monthly leadership review]. I have data on: [list 4–5 metrics or datasets you want to show]. Suggest a layout — how to arrange the elements, which chart type is best for each metric, and what to avoid so the dashboard reads clearly on a projector screen."

Debugging and Error-Fixing Prompts (6)

These prompts are for when something is wrong and you need a diagnosis, not just a fix. Context is everything here — always paste the actual formula and a few rows of sample data.

51. Fix a Formula Returning an Error

"This formula is returning a [#VALUE! / #REF! / #N/A / #DIV/0!] error: [paste your formula]. My data layout is: column A has [describe], column B has [describe]. Here's a sample of three rows where it fails: [paste data]. What's causing this specific error and how do I fix it?"

52. Find a Circular Reference

"Excel is warning me about a circular reference but I can't find it. The workbook has [number] sheets and approximately [number] formulas. Give me the systematic steps to locate the circular reference, including which menu options to use, and explain the most common accidental causes in large workbooks."

53. SUMIFS Returning Zero on Some Rows

"My SUMIFS formula works on most rows but returns 0 on some rows even though I can visually see matching data. The formula is: [paste formula]. Here are three rows where it fails and three where it works: [paste data]. List every possible cause of this discrepancy in order of likelihood and give me a diagnostic approach."

54. Workbook Running Slowly

"My Excel file has [number] rows and [number] columns. It freezes for [seconds] every time I enter data. I use [describe formulas — e.g. VLOOKUP in 15 columns, several SUMIFS]. Give me a systematic approach to diagnose the performance bottleneck and at least 5 specific changes I can make to speed it up significantly."

Slow workbooks are usually a VLOOKUP-on-entire-column problem. Switching to XLOOKUP with bounded ranges, or moving aggregations to pivot tables, typically cuts recalculation time by 70–90% in my experience.

55. Fix a #SPILL Error

"My formula is returning a #SPILL! error. The formula is [paste formula] and I entered it in cell [cell reference]. Explain all the possible causes of a #SPILL error and walk me through checking each one to find which applies to my situation."

56. Formula Works in One Cell But Not When Copied Down

"I have a formula in cell [cell reference] that gives the correct result: [paste formula]. When I copy it down to the rows below, it returns wrong values or errors. Here are the results I see vs. what I expect: [paste comparison]. Diagnose what's wrong with my cell referencing."

This is almost always a mixed reference problem — one reference that should be absolute (locked with $) isn't. The AI will spot it immediately if you give it the formula.

Power User Prompts (4)

These are for professionals who are comfortable with Excel and want to write cleaner, more maintainable work.

57. Convert a Repeated Formula to LAMBDA

"I use this formula in about 30 cells across my workbook: [paste formula]. I want to convert it into a LAMBDA function with a descriptive name so I can call it like a built-in function. Show me how to define it in the Name Manager, what to name it, and how to call it in a cell. I'm on Microsoft 365."

58. Rewrite a Complex Formula Using LET

"This formula is too long to understand at a glance: [paste complex formula]. Rewrite it using LET so that each repeated calculation has a meaningful name. Show me the before and after, and explain what each named part represents."

LET is one of the most underused functions in Microsoft 365. It turns a formula that takes 10 minutes to debug into one that's readable in 30 seconds.

59. Build a Robust Power Query

"I receive a [monthly/weekly] [CSV/Excel] export from [system name] and the column headers are inconsistent between exports — sometimes '[Header A]', sometimes '[header_a]', sometimes '[Header A ]' with a trailing space. Build me a Power Query transformation that standardises all column names regardless of casing or spacing, so my downstream formulas don't break."

Power Query is the right tool any time data transformation logic needs to survive file-format changes. The Power Query guide covers the full transformation toolkit.

60. Dynamic Array Combination Formula

"I want a single formula that returns a list of all unique values from column A where the corresponding value in column B is greater than [threshold], sorted alphabetically, and spilling automatically into the cells below. Write this using SORT, FILTER, and UNIQUE for Microsoft 365, and explain how to handle the case where no rows match the filter condition."

How to Adapt These Prompts to Your Data

The prompts above use placeholder text in brackets. Here's how to get the best results when you fill them in:

• Always name your columns specifically — "column A has invoice dates" is better than "column A has dates"
• State your Excel version — Microsoft 365, Excel 2021, Excel 2019, or Excel 2016; this determines which functions are available
• Paste sample data — even 3–5 rows of dummy data makes the AI's formula significantly more accurate
• State your goal, not just your method — "I want to find customers who haven't ordered in 90 days" is better than "I need a date comparison formula"
• Ask for an explanation — add "and explain what each part does" to any formula prompt; this is how you learn rather than becoming permanently dependent on AI

One more thing: if the first response isn't quite right, don't start a new conversation. Stay in the same thread and say "That's close, but [describe what's wrong]. Here's a sample where it fails: [paste data]." The AI has full context from your earlier messages and will converge on the right answer faster than starting over.

For a practical walkthrough of how to structure these conversations for formulas specifically — including the best follow-up questions — the ChatGPT Excel guide and the Claude Excel formulas guide both go deep on the conversation structure. The prompts above are designed to work with either.

Related Posts

• How to Use ChatGPT for Excel Formulas and Analysis
• How to Use Claude AI to Write Excel Formulas Instantly
• Using Claude AI to Generate Excel Macros and VBA
• Using Claude AI to Debug and Fix Excel Formulas
• How to Use Microsoft Copilot for Data Analysis in Excel
• How to Use Google Gemini for Excel Formulas and Data
• 15 Advanced Excel Formulas Every Professional Should Know
• Power Query in Excel — The Complete Beginner's Guide