HomeCasa Create Excel or Google Sheets formulas
Describe what you need to calculate and get the exact Excel or Google Sheets formula, explained and with alternatives. Designed for ChatGPT, Claude, Gemini and Microsoft Copilot. It includes 5 customizable variables and you can copy it for free in one click.
Beginner · Text · Pending editorial review
Prompt
Act as a spreadsheet expert.
Tool: {{tool}} (function language: {{function_language}})
My data structure: {{structure}}
What I need to calculate: {{need}}
1. Write the exact formula, stating which cell to put it in.
2. Explain each part of the formula.
3. Offer an alternative (e.g. with more modern or more compatible functions) and when to use each one.
4. Cases that can cause errors (empty cells, text instead of numbers, dates) and how to guard against them.
5. If useful, suggest how to do it with a pivot table instead of formulas.
Use “{{separator}}” as the argument separator.How to use it
Specify your sheet's language: function names change by locale (SUMAR.SI vs SUMIF) and some locales use “;” as separator.
Usage example
Need: sum each salesperson's sales for the current month only.
Sample output
1. Formula
In E2, the list of salespeople: =UNIQUE(B2:B500)
In F2 (drag down), the current month's total: =SUMIFS($C$2:$C$500, $B$2:$B$500, E2, $A$2:$A$500, ">="&EOMONTH(TODAY(),-1)+1, $A$2:$A$500, "<="&EOMONTH(TODAY(),0))
2. What each part does
$C$2:$C$500: the amounts to add up.$B$2:$B$500, E2: only rows for that salesperson.EOMONTH(TODAY(),-1)+1: the 1st of the current month.EOMONTH(TODAY(),0): the last day of the current month.
3. Alternative
Everything in one formula with QUERY (handy for a self-updating summary table): =QUERY(A1:C500, "select B, sum(C) where A >= date '"&TEXT(EOMONTH(TODAY(),-1)+1,"yyyy-mm-dd")&"' group by B label sum(C) 'Total'", 1)
Use SUMIFS if the file will be shared with Excel users; QUERY only works in Google Sheets.
4. Common pitfalls
- Dates stored as text: they won't match the criteria; check with
=ISNUMBER(A2). - Amounts with a typed "$": treated as text and ignored by the sum…
Key facts
- Category
- Data management · Formulas
- Output type
- Text
- Level
- Beginner
- Designed for
- ChatGPT, Claude, Gemini and Microsoft Copilot
- Verified in
- Pending editorial verification
- Variables
- Tool, Function language, Data structure, What to calculate and Separator
- Language
- English
- Price
- Free, no sign-up
- Updated
- Nov 7, 2026
Frequently asked questions
Why do I need to state the function language and separator?
Function names and the argument separator depend on the spreadsheet's language and regional settings (comma or semicolon, for example). Getting them right means the formula works on the first paste.
What if the formula returns an error?
Paste the exact error message and a few sample rows back to the AI and ask it to diagnose the cause.
What is this prompt for?
Describe what you need to calculate and get the exact Excel or Google Sheets formula, explained and with alternatives.
Which AI tools does it work with?
It is designed for ChatGPT, Claude, Gemini and Microsoft Copilot. We have not verified it in these tools yet, so results may vary between them.
How do I customize the prompt?
Click “Customize” and fill in the variables (Tool, Function language, Data structure, What to calculate and Separator). The text updates instantly and you can then copy it in one click. Values are stored only in your browser.
Do I need experience to use it?
No. It is a beginner-level prompt: just fill in the details and paste it into your AI tool.
Is this prompt free?
Yes. Every HomeCasa Prompts prompt is free, with no sign-up or subscription. You can also use them in professional projects.
Can I trust the AI output?
Treat it as a high-quality draft: always check data, figures and important claims before publishing or making decisions.