Excel formula prompts for writing, fixing and explaining formulas

The best Excel formula prompts tell the model your sheet layout, a few sample rows, the result you expect and your Excel version, then ask for the formula plus a way to test it. Below are 13 Excel formula prompts for ChatGPT, Claude and other AI assistants that cover writing formulas from plain English, fixing errors, lookups, conditional sums, dynamic arrays, text cleanup and making long formulas readable.

How to use these prompts

Copy a prompt and replace the blanks in double braces, or keep the default after the colon: {{version:Microsoft 365}} means Microsoft 365 unless you change it.

Three habits make the difference:

• Always state your version. Newer functions are not everywhere. Microsoft's LAMBDA page lists Microsoft 365 and Excel 2024 only, while FILTER and LET also work in Excel 2021. A formula built on a missing function shows #NAME?.
• Describe the layout precisely. "Order dates in column B from row 2, amounts in column D, about 5,000 rows" beats "my sales data". If your data is an Excel table, give the table and column names.
• Paste sample rows and the expected result. Three rows with the right answer let you check the formula immediately.

How do you write a formula from plain English?

1. Formula from a description

The everyday prompt. The expected result is what makes it checkable.

Write an Excel formula for {{version:Microsoft 365}}.

Layout: {{layout:Sheet "Orders", headers in row 1, order date in A, region in B, amount in C, data in rows 2 to 5000}}
Goal: {{goal:total amount for the region in F1 for the month in F2}}
Where the formula goes: {{cell:F3}}

Sample rows:
{{sample_rows}}

Expected result for the sample: {{expected}}

Give the formula, explain each part in one line, and say what to change if the data grows past the last row. If there are two good approaches, show both and say when to pick each.

2. Explain a formula you inherited

Explain this Excel formula to someone who knows basic Excel but not advanced functions.

Formula (in cell {{cell:H2}}):
{{formula}}

Column headers it refers to: {{headers}}

Break it down from the innermost function outward. Then walk through what it returns for this example row: {{example_row}}. Finish with anything fragile about it, such as hardcoded ranges or values that will break when rows are added.

How do you fix Excel formula errors with AI?

3. Fix an error value

Paste the exact error. Each one points to a different kind of problem.

This Excel formula returns {{error:#N/A}} in {{version:Microsoft 365}}.

Formula: {{formula}}
What the referenced cells contain: {{cell_contents}}
What I expected: {{expected}}

List the most likely causes for this error in this formula, ranked, with how to check each one in the sheet (for example, trailing spaces, numbers stored as text, mismatched range sizes). Then give the fixed formula. Only hide the error with IFERROR if the error is expected, and say why.

4. Fix a #SPILL! error

Dynamic array formulas spill results into neighboring cells. Microsoft's #SPILL! guide lists the causes: an obstructed spill range, a result that runs off the grid, a formula inside an Excel table, merged cells and a few others.

My formula in {{cell:E2}} shows #SPILL!.

Formula: {{formula}}
What's around it: {{surroundings:there is data in E10, and the range is inside an Excel table}}

Tell me which #SPILL! cause applies here and how to fix it. If the formula references whole columns, rewrite it to reference only the data. If it's inside a table, give me a version that works there or tell me where to move it.

How do you write lookup and summary formulas?

5. Replace VLOOKUP with XLOOKUP

XLOOKUP defaults to an exact match and has an if_not_found argument, so you don't need an IFERROR wrapper. Microsoft notes it is not available in Excel 2016 and Excel 2019.

Rewrite these VLOOKUP formulas as XLOOKUP for {{version:Microsoft 365}}.

{{formulas}}

For each: show the new formula, use if_not_found to return {{not_found:"Not found"}} instead of #N/A, and point out any VLOOKUP that was using approximate match (TRUE or omitted fourth argument) so I can check whether that was intended. Note anyone on Excel 2019 or older will see errors.

6. Lookup with two or more conditions

Write an Excel formula for {{version:Microsoft 365}} that returns {{return_column:the price}} where {{conditions:product matches A2 and region matches B2 and the date is the latest one}}.

Data: {{layout}}

Give one formula that works in my version. If my version has no XLOOKUP, use INDEX and MATCH. Explain how the multiple conditions are combined, and what happens if there are two matching rows.

7. Sum or count with several conditions

SUMIFS takes the sum range first. Microsoft warns that "the sum_range argument is the first argument in SUMIFS, but it is the third argument in SUMIF," a common source of wrong totals.

Write a {{function:SUMIFS}} formula that totals {{measure:the Amount column}} where:
{{criteria:Region is "West", Date is in the month in H1, Status is not "Cancelled"}}

Layout: {{layout}}

Use cell references for the criteria instead of typing values into the formula. Explain how the date range condition works. Then give a COUNTIFS version with the same criteria so I can check the row count.

How do you use dynamic array formulas?

8. A list that updates itself

On a summary sheet, I want a list that updates itself: {{goal:all open orders for the customer in B1, newest first}}.

Source data: {{layout}}
Version: {{version:Microsoft 365}}

Use FILTER (with the if_empty argument so it never shows #CALC!) and any other dynamic array functions my version supports. Tell me how many cells below and to the right must stay empty for the result to spill, and whether to point it at an Excel table so it grows with new rows.

9. Unique values and counts

From the column {{column:Customer in Orders[Customer]}}, build a small summary for {{version:Microsoft 365}}: each unique value once, with {{metric:the count of orders and the total amount}} next to it.

Use UNIQUE for the list and formulas that refer to the spilled range so the summary updates when data changes. Tell me how to sort it by the total, largest first.

10. Clean up messy text

Column {{column:A}} has messy text like this:
{{examples}}

Write formulas for {{version:Microsoft 365}} that {{goal:split full names into first and last name, trim extra spaces and fix capitalization}}.

Only use functions my version supports; if TEXTSPLIT isn't available, use an older approach. Show the result for each example above, and point out examples the formula won't handle.

How do you make long formulas readable and reusable?

11. Rewrite a long formula with LET

LET "assigns names to calculation results," so a value used three times is calculated once and has a readable name.

Rewrite this Excel formula with LET so it's readable and doesn't repeat calculations.

{{formula}}

Use short, descriptive names. Put each name and its value on its own line. Confirm the new formula returns the same result as the old one for this row: {{example_row}}.

12. Turn a formula into a custom function

For Microsoft 365 or Excel 2024. A named LAMBDA lets anyone in the workbook call your logic by name.

Turn this calculation into a reusable LAMBDA function called {{name:TAXAMOUNT}}.

What it does: {{description}}
Current formula: {{formula}}

Give me: the LAMBDA formula to paste into Name Manager's "Refers to" box, a one-line comment describing the parameters, a test I can type in a cell to check it, and the plain formula version for people on older Excel.

13. Audit a workbook's formulas

Use it before you send a model or report to someone who will trust the numbers.

Here are the key formulas from my workbook, with the cell each lives in:
{{formulas}}

Audit them. Look for: ranges that don't cover all the data, hardcoded numbers that should be inputs, inconsistent formulas down a column, approximate-match lookups that should be exact, errors hidden with IFERROR, and totals that double count. For each finding, give the cell, the risk and the fix. Then list three checks I can add to the sheet so errors show up on their own.

Which Excel functions work in which version?

Version support decides whether a formula works for everyone who opens the file. This table is based on the "Applies to" lists on Microsoft's support pages, read on 24 October 2026.

If your file goes to people on older versions, say so in the prompt and ask for INDEX, MATCH and SUMIFS instead.

Make your Excel prompts a skill

If you build spreadsheets with Claude often, the Claude Excel skill creates and edits .xlsx files with live formulas. For a prompt you run daily, paste it into the SKILL.md generator to turn it into a skill. For analysis beyond a spreadsheet, see the data analysis prompts.

Kipt keeps prompts like these as Markdown in a private GitHub repo you own. Blanks such as {{version:Microsoft 365}} stay fillable, so your version and layout are one edit away, and ⌘K finds any prompt.