Excel & Google Sheets Formula Prompt Builder

Why this formula prompt works

Spreadsheet formulas fail for boring reasons: the wrong column, a function your version does not have, dates stored as text, or a comma where your locale expects a semicolon. When you ask an AI for "a formula to sum sales by region", it has to guess all of these, and the result often returns an error the moment you paste it.

This builder gives the model the facts it needs. The app and version decide which functions are allowed: XLOOKUP, FILTER and LET exist in Excel 365 and Google Sheets but not in Excel 2016. The column layout with sheet names and row ranges makes the formula paste-ready, and a few sample rows reveal the data types, such as whether dates are real dates. The separator option switches to semicolons for European settings.

The prompt asks for the formula in a code block, a plain-language explanation of each part and a short list of edge cases, like blanks, duplicates or text in number columns. It also allows the AI to suggest a pivot table or built-in feature when that is simpler than a formula, which it often is.

Worked example

Say you keep orders in one sheet and want a monthly total per region on a summary sheet. These are the inputs:

Spreadsheet app

Microsoft Excel 365

What do you want to calculate or do?

Total sales per region for the current calendar month only

Your columns and ranges

Sheet "Orders", headers in row 1, data from row 2 to row 5000; A: Order date (real dates); B: Region (North, South, East, West); C: Amount in EUR

A few sample rows

02/10/2026 | North | 120.50; 03/10/2026 | South | 89.00; 28/09/2026 | North | 45.00

Where should the result go?

Sheet "Summary": regions in A2:A5, totals in B2:B5

What do you want back?

one formula with an explanation

Empty cells and errors

handle blanks and errors gracefully

My spreadsheet uses semicolons as separators (e.g. European settings)

No

The builder turns these inputs into the following prompt. Load it into the form above with the Monthly totals example chip and change anything you like.

You are a spreadsheet expert who writes correct, efficient formulas and explains them clearly.

## Task
Total sales per region for the current calendar month only

## My spreadsheet
- App: Microsoft Excel 365
- Result goes in: Sheet "Summary": regions in A2:A5, totals in B2:B5

## Columns and ranges
Sheet "Orders", headers in row 1, data from row 2 to row 5000
A: Order date (real dates)
B: Region (North, South, East, West)
C: Amount in EUR

## Sample rows
02/10/2026 | North | 120.50
03/10/2026 | South | 89.00
28/09/2026 | North | 45.00

## Requirements
- Use only functions available in Microsoft Excel 365. If a newer function would be better, mention it separately.
- Use my exact sheet names, columns and ranges so the result can be pasted directly.
- Use commas (,) as argument separators.
- Empty cells and errors: handle blanks and errors gracefully.
- If my description is ambiguous, state your assumption before the answer.

## Output format
Give me one formula with an explanation. Put each formula in its own code block. Then list edge cases to check (for example blanks, text in number columns, duplicates) and, if a built-in feature such as a pivot table would be simpler, say so.

This page shows only the prompt. The answer depends on the AI tool you paste it into, so always review the result before you use it.

Tips to improve your results

• Give column letters and the sheet name exactly as they appear in your file.
• Include two or three sample rows, especially for dates, so the AI knows the data type.
• Test the formula on a copy of the sheet first, and check a few results by hand.
• If a formula returns #NAME?, your version probably lacks that function. Tell the AI your version and ask again.
• Never paste confidential data. Replace real names and numbers in the samples with made-up ones.