Excel Formula Expert
The Excel Formula Expert translates plain English descriptions of spreadsheet problems into working formulas with clear, step-by-step explanations. Instead of searching through documentation or forum threads, you describe what you want and get a formula you can paste directly into your spreadsheet.
Business analysts, accountants, project managers, and anyone who works with spreadsheets daily use this template when they need a formula that goes beyond basic SUM and AVERAGE. It handles VLOOKUP, INDEX/MATCH, nested IFs, array formulas, conditional aggregations, date calculations, text manipulation, and dynamic ranges.
The prompt produces better results than a simple "write me a formula" request because it requires you to describe your actual data layout and expected output. This eliminates the back-and-forth that happens when the AI assumes a different spreadsheet structure than yours. Each response includes the formula, a breakdown of how each part works, an example with sample data, and common error scenarios so you can troubleshoot if something goes wrong.
This prompt is just the starting point
Score it with AI, optimize it with one click, track versions, and build your prompt library.
The Prompt
Generate an Excel / Google Sheets formula for the following task: **Spreadsheet Application**: [Excel / Google Sheets / either] **What I Want to Calculate**: [DESCRIBE IN PLAIN ENGLISH, e.g., "Find the total sales amount for each product category, but only count orders from the last 90 days where the status is 'completed'"] **My Data Layout**: ``` [DESCRIBE YOUR COLUMNS AND SAMPLE DATA, e.g., Column A: Order Date (e.g., 2025-01-15) Column B: Product Category (e.g., "Electronics", "Clothing", "Home") Column C: Order Amount (e.g., 149.99) Column D: Status (e.g., "completed", "pending", "cancelled") Data starts in row 2 (row 1 is headers), approximately 5,000 rows] ``` **Where I Want the Result**: [WHERE THE FORMULA GOES, e.g., "In cell F2, next to the category name in E2"] **Any Constraints**: [OPTIONAL: version limitations, preference for simpler formulas, etc.] Provide: 1. **The Formula**: The complete, ready-to-paste formula. If it is long, format it across multiple lines with comments explaining each part. 2. **How It Works**: Break down the formula into its components. Explain what each function does and why it is needed. Use simple language, not documentation jargon. 3. **Example**: Show a mini table (5-6 rows) of sample data and demonstrate what the formula returns for that data, step by step. 4. **Drag/Copy Behavior**: Explain which cell references are absolute ($A$1) versus relative (A1) and what happens when I copy the formula down or across. 5. **Common Errors**: List 2-3 errors I might encounter (#N/A, #VALUE!, #REF!) and how to fix each one. 6. **Simpler Alternative (if applicable)**: If there is a newer or simpler function that achieves the same result (e.g., XLOOKUP instead of INDEX/MATCH, FILTER instead of array formulas), mention it and note which versions support it.
Usage Tips
- Describe your data layout precisely: Specify which column holds what, where headers are, and approximately how many rows you have. A VLOOKUP scanning 100 rows differs from one scanning 1 million rows.
- Mention your spreadsheet application: Excel and Google Sheets share most functions, but some (XLOOKUP, UNIQUE, FILTER, LAMBDA) differ in availability. Specifying which app you use avoids getting a formula that does not work.
- Include your desired output location: Whether the formula sits in a summary table, in a new column next to your data, or in a dashboard cell changes how references should be structured.
- Ask for the array formula version: If you need the formula to spill results across multiple cells (dynamic arrays), mention this. The response will use functions like FILTER, SORT, or UNIQUE that return ranges instead of single values.
Get more from this prompt
Save it, score it with AI, optimize it, and track every version. Free to start.