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, organised 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. If your team works in Google Sheets, I also made a separate 60 AI prompts for Google Sheets library with Gemini, QUERY, ARRAYFORMULA, and Apps Script examples. And if you'd rather learn to write your own prompts than copy a list, my guide to how to write AI prompts for Excel breaks down the prompt anatomy behind this whole library — and why Copilot, Claude, and ChatGPT each need a slightly different approach.
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.
Where You Run the Prompt Now Matters
When I first published this list, almost everyone pasted a prompt into a chat window and copied the answer back into Excel. That still works, and every prompt below is written for it. But there are now two places where the AI can already see your workbook, and each one changes how much of the prompt you need to type.
- Copilot in Excel (edit, plan and chat modes). What Microsoft launched as Agent Mode, generally available in Excel for the web since December 2025 and on Windows and Mac since 27 January 2026, is now the default edit mode of the Copilot pane, which opens from the Copilot button in the lower-right corner of the sheet. It needs a Microsoft 365 Copilot licence at work, or a Microsoft 365 Personal, Family or Premium subscription at home. Because it reads the open sheet, drop the layout description and name the table instead: "In the Orders table, add a column that flags every customer with no order in the last 90 days." Edit mode builds and restructures the workbook itself; chat mode answers and suggests without touching your cells.
- Claude for Excel. Anthropic's add-in has been generally available since 7 May 2026 on the Claude Pro, Max, Team and Enterprise plans, installed from Microsoft AppSource. It answers with cell-level citations, changes values without breaking formula chains, and traces an error back to its source. The debugging prompts in this list work especially well here, because you can say "the #N/A in H15" instead of pasting the formula.
- ChatGPT, Claude or Gemini in a browser. Still the right choice on a locked-down work machine, on Excel 2019 or 2021, or when you want a second opinion on a formula Copilot wrote. These tools cannot see your sheet, so they need the full layout description, which is exactly what the prompts below give you.
One thing to stop looking for: the =COPILOT() worksheet function. Microsoft retired it on 14 September 2026, and any cell that still contains it returns #NAME? when it recalculates. The Copilot pane does the same summarising and classifying work, and the data-cleaning and analysis prompts below cover that ground.
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 sheet where column A has [category names], column B has [sub-category names] and column C has [numeric values], with headers in row 1 and data from row 2 to about row [500]. Write a formula for cell D2 that sums column C where column A equals [value1] AND column B equals [value2]. Take the two criteria from cells F1 and F2 rather than hard-coding them, so I can change them later. I'm on Microsoft 365. Give me the formula, then one sentence on what each argument does."
The AI will give you SUMIFS pointed at F1 and F2. Turn those two cells into dropdowns with Data Validation and you have the core of a dynamic dashboard.
2. Lookup with a Fallback
"Sheet2 is a master list: column A has [unique IDs] and column B has [names], with headers in row 1. Sheet1 column A has IDs, some of which may not exist in Sheet2 and some of which have trailing spaces. Write a formula for Sheet1 cell B2 that returns the matching name, shows 'Not Found' when there is no match, and does not break if I insert a column in Sheet2 later. I'm on [Microsoft 365 / Excel 2019]. Tell me which lookup function you chose and why."
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]', separated by a space, a hyphen and a space. Write a formula for cell B2 that extracts only the [first/second/third] part. Some cells have only two parts and some are blank: return an empty string in those cases rather than an error. I'm using [Excel version]. Show me the result for these three sample values: [paste three examples]."
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 real Excel dates, from row 2 down. Write a formula for cell C2 that returns the number of working days between them, counting both the start and the end day, excluding Saturdays and Sundays but not public holidays. If the end date is blank, return blank instead of a huge negative number. Give me the formula and tell me how to add a holiday list later."
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 A has names and column B has performance scores for [number] people, headers in row 1. Write a formula for cell C2 that ranks each person from highest to lowest and gives tied scores the same rank, with the next rank skipped: if two people tie for 3rd, the following person is 5th, not 4th. Blank scores should return blank, not rank as zero. Then give me a second version that breaks ties using column D ([tie-breaker, e.g. tenure]) so every rank is unique."
6. Nested IF Replacement
"I have this formula that is hard to read: =IF(A2>200,'Very High',IF(A2>100,'High',IF(A2>50,'Medium','Low'))). Rewrite it using IFS, and separately using SWITCH if that fits, keeping exactly the same results at the boundary values 200, 100 and 50. Explain the difference between the two approaches in two sentences and tell me which one you would use here and why."
7. Dynamic Unique List
"Column A has [number] rows of [data type] with many duplicates, plus some blank cells and some entries with trailing spaces. Write a single formula for cell C2 that produces a unique, alphabetically sorted list that ignores blanks, treats 'Apple' and 'Apple ' as the same value, and updates automatically when I add rows. I'm on Microsoft 365. Tell me what happens to the spilled list if a cell below C2 already contains data."
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, with dates in column A. Write a formula for cell C2 that shows the running total, the cumulative sum up to and including that row, which I can copy down without editing. It must still be correct if I insert rows in the middle or add rows at the bottom. Then give me a second version that restarts the running total each month, based on the date in column A."
9. Cross-Sheet Aggregate
"I have 12 sheets named Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, in that order, and each has a monthly total in cell [B20]. Write a formula for the Summary sheet that sums that cell across all 12 sheets. Explain what breaks if I rename a sheet or drag one out of the sequence, and give me a safer alternative that lists the sheet names in column A of the Summary sheet and sums only those."
10. Text-to-Number Conversion
"Column A has numbers stored as text: they are left-aligned, some have a leading apostrophe, some have a non-breaking space after the number, and my SUM formula returns 0. Write a formula for cell B2 that converts each one to a real number and leaves the original column untouched. Return blank for cells that are genuinely empty and 'Check' for anything that still cannot be converted. Also tell me the fastest way to fix the original column in place without formulas."
11. Weighted Average
"Column B has [scores/ratings] and column C has the weight for each one, from row 2 down. The weights do not always add up to 100, and some rows have a score but a blank weight. Write a formula that returns the correct weighted average: each score multiplied by its weight, divided by the sum of the weights actually used. Rows with a blank weight should be ignored entirely. Show me the formula and a two-row worked example so I can check it by hand."
12. Conditional Formatting Formula
"Write a conditional formatting formula for the range C2:C[500] that highlights a cell red if its value is more than [percentage]% below the average of the whole column, ignoring blank cells in the average. Give me the exact formula to paste into 'Use a formula to determine which cells to format', tell me which cell it must be written relative to, and explain why the average reference needs to be absolute."
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 workshop I run.
13. Standardise Name Casing
"Column A has people's names entered inconsistently: some ALL CAPS, some all lowercase, some Mixed, some with double spaces. Write a formula for cell B2 that returns the name in Proper Case with extra spaces removed, and a second formula for cell C2 that shows 'Check' if the cleaned name is under 4 characters or contains a digit. Warn me about the names PROPER gets wrong, such as 'McDonald' or 'O'Neil', and suggest how to handle them."
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, columns C onwards have other fields, headers in row 1. Give me step-by-step instructions to keep only the most recent record for each ID and remove the older duplicates, without a macro. Offer two routes: a formula-based flag column I can filter and delete on, and a Power Query version. Before you start, ask me anything you need to know about ties, where two records share the same date."
15. Split a Full Name Column
"Column A has names as 'FirstName LastName', but some rows are 'FirstName MiddleName LastName' and a few end with a suffix such as 'Jr'. Write formulas for column B (first name) and column C (last name) that handle two-, three- and four-word names, leaving middle names out of both. I'm on [Excel version]. Show me the output for these sample rows: [paste five names]. If my examples do not cover a case you need to decide on, ask me before writing the formulas."
16. Strip Non-Numeric Characters from Phone Numbers
"Column A has phone numbers in inconsistent formats: some with dashes, some with spaces, some with a +91 country code, some with brackets, some with 'ext.' at the end. Write a formula for cell B2 that returns only the digits. Then give me a second formula that also drops a leading 91 when the remaining number is 10 digits, so every entry becomes a plain 10-digit Indian mobile number. 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 to [letter], headers in row 1. The required columns are [list them, e.g. A, C and F]; the others are optional. Write a formula for column [next letter] that shows 'Incomplete' if any required cell in that row is blank or contains only spaces, and 'Complete' otherwise. Leave the result blank for rows where nothing at all has been entered yet."
18. Diagnose Why VLOOKUP Is Returning Wrong Results
"My VLOOKUP is returning #N/A or wrong values. The formula is =VLOOKUP(A2, Sheet2!A:C, 2, FALSE). The IDs look identical on both sheets. List every possible cause in order of likelihood, and for each one give me a short formula I can put in a spare column to test whether that cause applies, for example checking length, data type or hidden characters. Here are three IDs that fail: [paste them] and three that work: [paste them]."
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 typed by users. Write a formula for cell B2 that returns 'Valid' or 'Invalid' using only these checks: exactly one @, at least one dot after the @, no spaces, and nothing missing before the @ or after the last dot. It should not reject unusual but legitimate addresses. Trim leading and trailing spaces before checking. I'm on [Excel version]."
20. Convert Text Dates to Real Dates
"Column A has dates stored as text in the format [DD/MM/YYYY, MM-DD-YYYY, or describe the mix], and Excel is not recognising them as dates. My Windows regional setting is [UK / US / India]. Write a formula for cell B2 that returns a real Excel date I can format and use in calculations, and returns blank for empty cells. Explain why DATEVALUE alone may swap the day and month on my system. If you need to know which format dominates, ask me first."
21. Merge Updated Data from Two Sheets
"Sheet1 has customers with Name in column A and Email in column B. Sheet2 has the same customers with an updated [phone number/address/status] in column [letter]. Write a formula for a new column in Sheet1 that pulls the updated value from Sheet2, matching on email rather than name because names are not unique, and returns 'No update' when the customer is missing from Sheet2. Warn me about any risk in this matching approach, and ask me if you need to know whether either sheet contains duplicate emails."
22. Flag Rows with Outlier Values
"Column C has [number] numeric values from row 2 down, with some blanks. Write a formula for cell D2 that shows 'Outlier' when the value is more than 2 standard deviations from the mean of the column and blank otherwise, ignoring blank cells in both calculations. Put the mean and standard deviation in cells F1 and F2 with their own formulas, and the threshold in F3, so I can show them on a summary row and change the threshold without editing every formula. Tell me whether you used the sample or population standard deviation and why."
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 row 1 with a dark blue fill and white text, auto-fit all column widths, freeze row 1 and set the zoom to 90% on every sheet, not just the active one. Tell me exactly where to paste it (ThisWorkbook or a module), avoid Select and Activate, and add a line I can comment out to switch it off while testing."
24. Export Sheet to PDF
"Write a VBA macro that exports the active sheet as a PDF to the same folder as the workbook, named as the sheet name plus today's date in YYYYMMDD format. Use the sheet's existing print area and page setup, fit it to one page wide, and overwrite an existing file of the same name without a prompt. Show a message box with the full path when done, and handle the case where the workbook has never been saved and so has no folder."
25. Loop Through Every Sheet
"Write a VBA macro that loops through every worksheet in the workbook except one called 'Summary', and clears the contents of column [letter] from row 2 down to the last used row on each sheet, leaving row 1 and all formatting intact. Skip hidden sheets and show a message at the end listing which sheets were cleared. Make it safe to run twice."
26. Create a Values-Only Snapshot
"Write a macro that copies the range [A1:G500] on Sheet1 and pastes it as values and number formats only (no formulas) onto a new sheet named 'Snapshot YYYY-MM-DD' using today's date. If a sheet with that name already exists, delete it first without the confirmation prompt, then recreate it. Leave me on the original sheet when it finishes, and avoid using the clipboard if there is a cleaner way."
27. Send an Email via Outlook
"Write a VBA macro that sends an email through desktop Outlook using values from the active sheet: recipient from B2, subject from B3, body from B4, and attaches a copy of the current workbook. Show a Yes/No confirmation listing the recipient and subject before sending. Add error handling for Outlook not being open, and tell me what changes if I want to display the email for review instead of sending it. Ask me before you write it if you need to know whether I use the new Outlook or classic Outlook."
28. Delete Rows Matching a Condition
"Write a macro that scans column A from row 2 to the last used row and deletes every row where the cell is blank or contains exactly the text 'DELETE', case-insensitive. Loop from the bottom up so no rows are skipped, turn off screen updating while it runs, and show a count of deleted rows at the end. Include a confirmation prompt first, because this cannot be undone."
29. Auto-Generate a Summary Sheet
"I have a sheet called 'Raw Data' with the headers [Date], [Region], [Salesperson], [Product] and [Revenue] in row 1. Write a macro that builds a sheet called 'Summary' showing total Revenue by [Region], sorted from highest to lowest, with a grand total row. If 'Summary' exists, clear and rebuild it. Find the columns by header name rather than letter so it survives column reordering. Before writing it, ask me if you need to know how many rows to expect or whether a pivot table would suit me better than formulas."
30. Protect All Sheets with a Password
"Write two VBA macros in the same module: ProtectAll, which protects every sheet with the password '[your password]' while still allowing users to select cells, use filters and sort, and UnprotectAll, which removes protection with the same password. Store the password in one constant at the top so I change it in one place, and tell me honestly how strong sheet protection is against someone who wants to bypass it."
31. Highlight Duplicates in a Column
"Write a VBA macro that scans column [letter] from row 2 to the last used row and fills every cell whose value appears more than once in yellow, leaving unique values unfilled. Treat 'abc' and 'ABC ' as the same value by ignoring case and trailing spaces. Clear any previous yellow fill first so re-running it stays accurate, and show a count of duplicate cells at the end."
32. Import a CSV File Chosen by the User
"Write a macro that opens a file dialog filtered to CSV files, imports the chosen file into a new sheet named after the file without the .csv extension, and asks whether to replace or cancel if that sheet already exists. Keep leading zeros in text columns such as IDs, treat the file as UTF-8, and do not leave an external data connection behind. Ask me if you need to know the delimiter or whether the file has a header row."
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: column A has month labels as real dates (the first of each month) and column B has values, for the past [number] months. Write the formulas for: the overall trend direction using SLOPE, the best and worst months by name, and a flag in column C that shows 'Drop' where the value fell more than [percentage]% from the previous month. Tell me what to change if my months are text such as 'Jan 2026' rather than dates."
34. Year-over-Year Growth
"Column A has daily dates and column B has [revenue/units/calls], from row 2 down. Write formulas for: total for the current calendar year, total for the prior calendar year, and year-over-year growth as a percentage. Use TODAY() so the year rolls over automatically, and also give me a like-for-like version that compares the prior year only up to the same day of the year, because a full-year comparison is unfair in March."
35. Pivot Table Setup Advice
"My dataset has these columns: [list each column name and what it contains]. I want to answer this question: [state the business question]. Tell me exactly how to set up a pivot table to answer it: which fields go in Rows, Columns, Values and Filters, which aggregation to use, and whether I need a helper column first. If the question is ambiguous or one column could be read two ways, ask me before answering."
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. weekly advertising spend] and column C has [variable 2, e.g. weekly sales] for [number] periods. Write the formula for the Pearson correlation coefficient and the formula for R squared. Then explain what a result of [e.g. 0.73] means in plain language, what I should and should not conclude from it, and one check I can do in Excel to see whether a few extreme weeks are driving the result."
37. Revenue Forecast
"Column A has month-start dates from [start date] to [end date] and column B has actual revenue. Write a FORECAST.ETS formula that projects the next [number] months, plus the FORECAST.ETS.CONFINT formula for a 95% band. Explain what the seasonality argument controls, what value to use for monthly data with a yearly pattern, and what to do about the two months where I have gaps in the data."
38. Pareto Analysis (80/20 Rule)
"I have [customers/products/issues] in column A and their [revenue/frequency/cost] in column B, unsorted. Walk me through the exact steps: sort descending, add cumulative total and cumulative percentage columns with their formulas, flag the items that make up the first 80%, and build a combo chart with bars for the values and a line on a secondary axis for the cumulative percentage. Tell me whether Excel's built-in Pareto chart would be simpler for my case, and ask me if you need to know whether the list changes size each month."
39. Two-Variable Sensitivity Table
"In my model, cell [B5] holds [input 1, e.g. selling price], cell [B6] holds [input 2, e.g. unit cost] and cell [B10] calculates [output, e.g. gross margin]. Walk me through setting up a two-variable Data Table showing the output for [input 1] from [min] to [max] in steps of [step] across the top and [input 2] from [min] to [max] down the side. Include where the reference to B10 goes, which cell is the row input and which the column input, and why the table shows the same number everywhere if calculation is set to 'Automatic except for data tables'."
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 and one sample row]. Suggest the 5 KPIs that matter most for this audience. For each one give me the formula against my column names, what it measures, the chart type that suits it, and what a good or bad value looks like. Ask me first if you need to know the time period, the targets, or who will read the dashboard."
41. Cohort Retention Setup
"I have a transactions table with columns CustomerID, PurchaseDate and Amount, one row per purchase. Explain how to structure a monthly cohort retention table in Excel: how to derive each customer's cohort month, what the rows and columns of the output represent, and the formulas for each cell, using COUNTIFS or a pivot table. Tell me which approach scales better past 100,000 rows, and ask me if you need to know whether a customer with two purchases in the same month counts once or twice."
42. Segment Performance Comparison
"My dataset has [number] rows with a [Region/Department/Category] column in column [letter] and a numeric [metric] column in column [letter]. I want the [mean/median/sum] of the metric for each segment side by side, plus each segment's share of the total. Give me both a formula approach (GROUPBY if I'm on Microsoft 365, otherwise AVERAGEIFS or a MEDIAN array) and a pivot table approach, and tell me which to use given that [my specific goal, e.g. the segments change every month]."
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
"My summary table has [Category] in column A and [Values] in column B, headers in row 1, and I add a new row each month. Give me two ways to make a column chart pick up new rows without editing its data range: converting the range to an Excel Table, and named ranges built with OFFSET or INDEX. Show the exact named-range formulas, and tell me why INDEX is usually preferred over OFFSET."
44. Conditional Bar Chart Colours
"I have a clustered bar chart of monthly [actual vs target], with months in column A, actual in B and target in C. I want the actual bars to be green where they beat target and red where they miss. Walk me through the helper-column method without VBA: the formulas for an 'Above' column and a 'Below' column, how to plot them on one chart, and how to set the series overlap to 100% so it still looks like a single set of bars."
45. Sparklines for a Summary Table
"Column A has [product/region/person names] and columns B to M have monthly values from Jan to Dec, headers in row 1. Give me step-by-step instructions to add line sparklines in column N for each row, mark the high and low points, and set the vertical axis to the same scale for every sparkline so the rows can be compared honestly. Tell me how to make the sparklines extend automatically when I add rows."
46. Interactive Dropdown-Driven Chart
"I want a dashboard where a dropdown in cell [B2] selects a [region/product/team] and a chart underneath updates to show only that selection's monthly data from a table on the [Data] sheet with columns [Month, Region, Value]. Walk me through the Data Validation dropdown, the FILTER formula that builds the chart's data (Microsoft 365) or the INDEX-MATCH alternative, and how to point the chart at a spilled range so it does not break when the number of rows changes. Ask me if you need to know how the data is laid out before you start."
47. Waterfall Chart for P&L
"I have a P&L with line items in column A and values in column B: revenue at the top, cost items as negative numbers, and net profit at the bottom. Give me step-by-step instructions to build a waterfall chart with positive contributions in green, negatives in red and the final total in a third colour, using Excel's built-in waterfall chart type. Explain how to mark the last bar as a total so it starts from zero, and how to handle a subtotal such as gross profit in the middle."
48. Gauge Chart Without Add-ins
"I want to show a single KPI, [72] out of [100], as a gauge or speedometer chart using only built-in chart types. Walk me through building it from a doughnut chart layered with a pie chart for the needle: the exact helper cells I need, including the invisible bottom half, how to rotate the chart so the gauge sits on top, and how to make the needle move when the KPI cell changes."
49. Colour-Scale Heatmap
"I have a grid with [row labels] in column A, [column labels] in row 1 and numeric values in between, from B2 to [M40]. Walk me through applying a colour-scale heatmap in one conditional formatting rule: highest value darkest green, lowest white, blanks left uncoloured. Then tell me how to fix the midpoint at a specific value such as [target] so the colours mean the same thing every month rather than shifting with the data."
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]. Suggest a layout: what goes top-left where eyes land first, which chart type suits each metric, how many charts is too many, and what to avoid so it reads clearly on a projector and when printed on A4 landscape. Ask me about the decisions this audience makes, or the time period, if that would change your answer."
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 returns a [#VALUE! / #REF! / #N/A / #DIV/0!] error: [paste your formula], entered in cell [cell reference]. My layout is: column A has [describe], column B has [describe]. Here are three rows where it fails and one where it works: [paste data]. Tell me the specific cause of this error in my case, not the general list of causes, give me the corrected formula, and say whether the fix hides a data problem I should sort out at source instead."
52. Find a Circular Reference
"Excel is warning me about a circular reference but I cannot find it. The workbook has [number] sheets and roughly [number] formulas, and the warning appears when I open the file. Give me the systematic steps to locate it, including Formulas > Error Checking > Circular References and the status bar, what to do when the status bar shows no cell address, and the most common accidental causes in large workbooks. Ask me whether iterative calculation is switched on if that changes your advice."
53. SUMIFS Returning Zero on Some Rows
"My SUMIFS works on most rows but returns 0 on some, even though I can see matching data. The formula is [paste formula]. Here are three rows where it fails and three where it works, with the criteria cells and the matching source cells: [paste data]. List the possible causes in order of likelihood, then give me one diagnostic formula per cause, such as comparing LEN or checking ISNUMBER, so I can prove which one applies rather than guessing."
54. Workbook Running Slowly
"My Excel file has [number] rows and [number] columns across [number] sheets, and it freezes for [seconds] every time I enter data. I use [describe formulas, e.g. VLOOKUP over whole columns in 15 columns, several SUMIFS, volatile functions like OFFSET or INDIRECT]. Give me a systematic way to find the bottleneck, then at least five specific changes ranked by likely impact, and tell me which ones change results or behaviour and which are safe. Ask me about calculation mode or external links if you need to."
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 returns #SPILL!. The formula is [paste formula], entered in cell [cell reference], and the cells below and to the right look empty. Go through every cause of #SPILL! (something in the spill range, a merged cell, a formula inside an Excel Table, an out-of-memory spill, an unknown range size) and tell me how to check each one, including the 'Select Obstructing Cells' option. Then give me a version of the formula that would work if I must keep it inside a Table."
56. Formula Works in One Cell But Not When Copied Down
"The formula in cell [cell reference] gives the right result: [paste formula]. When I copy it down, the rows below return wrong values or errors. Here is what I see versus what I expect for the next three rows: [paste comparison]. Diagnose which references should be absolute, mixed or relative, give me the corrected formula, and explain the rule so I can spot it myself next time."
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 the workbook, with only the input cells changing: [paste formula]. Convert it into a LAMBDA with a descriptive name and named parameters so I can call it like a built-in function. Show me how to define it in Name Manager, how to test it in a cell before saving it, how to call it, and how to add a comment describing the parameters. I'm on Microsoft 365. Ask me which parts of the formula should become parameters if it is not obvious."
58. Rewrite a Complex Formula Using LET
"This formula is too long to understand at a glance: [paste complex formula]. Rewrite it with LET so each repeated calculation has a meaningful name, keeping exactly the same result. Show me the before and after, explain what each named part represents, and tell me whether any part is calculated more than once in the original, since that is where LET also makes it faster."
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 vary between files: sometimes '[Header A]', sometimes '[header_a]', sometimes '[Header A ]' with a trailing space. Write the Power Query M step that standardises every column name to lower case with underscores and no surrounding spaces, then a step that fails loudly if an expected column is missing rather than silently continuing. Tell me where to paste the M code in the Advanced Editor. Ask me if you need the full list of expected columns first."
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 one formula that returns the unique values from column A where the matching value in column B is greater than [threshold], sorted alphabetically and spilling down from cell D2. Write it with SORT, FILTER and UNIQUE for Microsoft 365, ignore blank cells in column A, and make it return 'No matches' instead of #CALC! when nothing qualifies. Then show the variant that reads the threshold from cell F1 so I can change it without editing the formula."
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"
- Invite questions on the ambiguous ones — several prompts above end by telling the AI to ask before it answers. Do that whenever the result depends on something you have not described, such as ties, duplicates or a date format: one clarifying question costs ten seconds, a confident wrong answer costs an afternoon
- 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.
Frequently Asked Questions
What are the best AI prompts for Excel? The most effective AI prompts for Excel include your data context: column headers, sample data, and desired output. For example: "My data has columns Date, Region, Product, Revenue in A1:D500. Write a formula to find the top 3 products by total revenue per region." Context-rich prompts produce accurate formulas most of the time, while vague prompts like "write a sales formula" often produce unusable results.
How should I prompt ChatGPT or Claude for Excel formulas? Follow this structure: describe your data layout with column letters and headers, specify the cell where the formula should go, explain the expected output clearly, and mention any constraints such as avoiding VBA or needing backward compatibility. Specific prompts with column references produce far better results than generic requests.
Can AI write VLOOKUP and XLOOKUP formulas for me? Yes. ChatGPT, Claude, Gemini and Copilot can all write VLOOKUP and XLOOKUP formulas accurately. They can also suggest which lookup function is best for your use case, convert VLOOKUP formulas to XLOOKUP, and handle complex multi-criteria lookups. Always test the formula with your actual data before applying it widely.
Do these prompts work in Copilot Agent Mode and Claude for Excel? Yes. Both tools read the open workbook, so drop the layout description at the start of each prompt and refer to your table or column names directly. Keep the goal, the constraints and the request for an explanation. Copilot's edit mode builds and restructures the workbook itself, so review its changes before you save; Claude for Excel edits values while keeping formula relationships intact and cites the cells it used.
Related Posts
- How to Prompt Copilot, Claude and ChatGPT in Excel (Each Needs a Different Prompt)
- 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
- 60 AI Prompts for Google Sheets That Actually Work
- 15 Advanced Excel Formulas Every Professional Should Know
- Power Query in Excel — The Complete Beginner's Guide