Excel Formula Prompts AI: 25+ Master Templates for Sheets & Formulas (2026)

System Directive:
You are an Elite Financial Modeler, Advanced Excel Specialist, and Google Sheets Solutions Architect. I need to build a robust, error-free spreadsheet formula or data transformation pipeline from complete scratch.

Execution Protocol:
1. Do not generate a formula or script immediately.
2. Conduct an interactive discovery consultation with me by asking exactly ONE question at a time. Wait for my answer before proceeding to the subsequent question.
3. Diagnostic Interview Sequence:
   - Question 1: Are you working in Microsoft Excel (specify version: 365, 2021, desktop, or web) or Google Sheets?
   - Question 2: What are your exact column letters, row numbers, and headers where the input data lives (e.g., Column A: Transaction Date, Column B: Vendor String, Column C: Gross Amount)?
   - Question 3: Provide 2-3 realistic sample rows of raw data from your sheet, including any messy formatting, extra spaces, or missing values.
   - Question 4: What is the exact expected output, and in which specific target cell or column should the final result appear?
   - Question 5: What edge cases must be handled (e.g., `#N/A` missing lookups, duplicate rows, zero division `#DIV/0!`, blank cells)?
4. After I finish answering Question 5, synthesize my inputs into a clean, modern formula solution complete with step-by-step token explanations and fallback error-handling wrap.

Act as an Advanced Spreadsheet Logic Architect.

Platform: [Excel 365 / Google Sheets]
Business Logic to Translate:
"[INSERT EXACT LOGICAL RULES IN PLAIN ENGLISH, e.g., If the deal close date in Column B is within Q3 and the contract value in Column C is over $50,000, apply an 18% commission rate. If under $50,000, apply 12%. If the deal status in Column D is 'Pending', return 'Awaiting Close'. If lost, return $0.]"

Cell Mapping:
- Column B: Deal Close Date
- Column C: Contract Value
- Column D: Deal Status
- Target Cell: Row 2 downwards (E2)

Formula Directives:
1. Modern Syntax: Use modern functions (e.g., `LET`, `IFS`, or `SWITCH`) to avoid deeply nested `IF` statements.
2. Maintainability: Make the formula readable by structuring calculations with clear variable names if using Excel 365 `LET`.
3. Error Wrapping: Wrap the formula in an appropriate error shield (e.g., `IFERROR` or custom message).
4. Provide the exact formula ready to copy-paste into cell E2, followed by a 3-bullet breakdown explaining how each clause works.

Act as a Cross-Platform Spreadsheet Porting Specialist.

Source Platform & Formula:
- Source Platform: [Excel 365 / Google Sheets]
- Target Platform: [Google Sheets / Excel 365]
- Original Formula:
```excel
[PASTE ORIGINAL FORMULA HERE]
```

Translation Requirements:
1. Behavioral Parity: The converted formula must produce identical outputs, handle identical edge cases, and respect array spill behavior.
2. Function Equivalence:
   - If porting from Excel `TEXTBEFORE` / `TEXTAFTER` to Google Sheets, utilize `REGEXEXTRACT` or `SPLIT`.
   - If porting dynamic array formulas to Google Sheets, incorporate `ARRAYFORMULA`, `FILTER`, or `QUERY`.
   - If porting Google Sheets `QUERY` to Excel, utilize `FILTER`, `CHOOSECOLS`, or `SORT`.
3. Provide the converted formula ready to paste into the target platform with an explanation of any syntax discrepancies.

Act as an Advanced Data Lookup Engineer.

Lookup Objective:
I need to find a specific value based on MULTIPLE matching criteria across different columns.

Sheet Parameters:
- Lookup Criteria 1: Customer ID in Cell F2 matching Range A2:A500
- Lookup Criteria 2: Product SKU in Cell G2 matching Range B2:B500
- Lookup Criteria 3: Year in Cell H2 matching Range C2:C500
- Return Column: Unit Price in Range D2:D500
- Target Platform: [Excel 365 / Google Sheets]

Formula Requirements:
1. Multi-Criteria Matching: Use boolean logic `(Range1=Crit1)*(Range2=Crit2)*(Range3=Crit3)` inside `XLOOKUP`.
2. Missing Record Fallback: If no match is found, return "Record Not Found" instead of standard `#N/A`.
3. Search Order: Specify search mode (first-to-last or last-to-first for latest historical entry).
4. Output the exact formula for target cell I2 with an explanation of how the boolean multiplication matrix functions.

You are an Enterprise Financial Analyst and Spreadsheet Architect.

Matrix Lookup Challenge:
I have a two-dimensional grid of pricing or financial data:
- Row Headers (Products): Cells A2:A50
- Column Headers (Regional Markets / Quarters): Cells B1:G1
- Data Grid (Prices / Revenue): Cells B2:G50

Input Parameters to Query:
- Selected Product (Row Target): Cell I2 (e.g., "Enterprise License")
- Selected Region (Column Target): Cell J2 (e.g., "EMEA")

Build a Dynamic Two-Way Lookup:
1. Two-Way Matrix Formula: Write a formula using `INDEX`, `MATCH`, and `XMATCH` that dynamically finds the intersection cell where Product matches the row and Region matches the column header.
2. Robustness: The formula must remain functional even if users re-order, insert, or delete columns between B and G.
3. Fallback Handling: Return "Invalid Product/Region" if either lookup target is misspelled or missing.

Act as a Business Intelligence Spreadsheet Specialist.

Source Data Range:
- Master Transactions Table: Sheet 'Transactions', Range A2:E2000
  - Col A: Date | Col B: Sales Rep | Col C: Region | Col D: Deal Size | Col E: Status ('Closed Won', 'Lost', 'Pending')

Reporting Objective:
On Sheet 'Dashboard', I want a dynamic, automatically updating report:
1. Filter Condition: Extract all deals where Status = "Closed Won" AND Deal Size >= $25,000.
2. Sorting: Automatically sort the results by Deal Size in descending order (largest deals at the top).
3. Column Selection: Return only Columns A (Date), B (Rep), and D (Deal Size), excluding the other columns.
4. Empty Result Fallback: If no deals match, display "No High-Value Deals".

Deliverable:
Write a single modern dynamic array formula using `SORT`, `FILTER`, and `CHOOSECOLS` (or Google Sheets `QUERY`) to paste into cell A4 of the Dashboard sheet.

Act as a Data Sanitation and Regex Spreadsheet Engineer.

Raw Data Sample (Column A):
"2026-08-14 - TXN#94821 - Acme Global Logistics ($14,250.00) [PAID via Stripe]"
"2026-08-15 - TXN#94822 - Stark Industries ($3,400.50) [REFUNDED]"

Platform: [Excel 365 / Google Sheets]

Build 4 Individual Extraction Formulas:
1. Formula 1 (Clean Date): Extract the ISO date into Column B formatted as a true date serial number.
2. Formula 2 (Transaction ID): Extract only the numeric digits of the transaction code (e.g., "94821") into Column C.
3. Formula 3 (Client Name): Extract only the company name (e.g., "Acme Global Logistics") into Column D, stripping leading/trailing dashes and spaces.
4. Formula 4 (Numeric Transaction Value): Extract the currency value into Column E as a true numeric decimal (e.g., 14250.00), removing dollar signs, commas, and parentheses so it can be summed.
Provide formulas for both Excel 365 (`TEXTBEFORE`, `TEXTAFTER`, `REGEXEXTRACT`) and Google Sheets.

Act as a Data Quality Auditor and Spreadsheet Specialist.

Data Challenge:
I have a list of 1,200 company names in Column A that contains extra spaces, inconsistent capitalization, and near-duplicate variations:
Examples: "Acme Corp", " acme corp ", "ACME CORP", "Acme Corporation".

Build a 3-Step Data Cleansing Formula Suite:
1. Normalization Formula (Column B): Clean leading/trailing spaces, multiple inner spaces, and standardize to Proper Case using `PROPER(TRIM(A2))`.
2. Exact Duplicate Flag Formula (Column C): Count how many times the normalized string appears in the dataset. If count > 1, output "Duplicate [N] of [Total]", otherwise output "Unique".
3. Unique Canonical List (Column E): Write a single dynamic formula in cell E2 that outputs an alphabetized list of only the unique normalized names using `SORT(UNIQUE(...))`.

You are a Spreadsheet Date & Time Telemetry Specialist.

Raw Date Problem:
Column A contains mixed, messy date entries imported from third-party systems:
- Format Type 1: Unix epoch milliseconds (e.g., "1757241600000")
- Format Type 2: Text string "2026.09.08 14:30:00"
- Format Type 3: European date text "08/09/2026" (September 8th, not August 9th)

Provide Formulas to Normalize:
1. Unix-to-Date Formula: Convert 13-digit Unix millisecond timestamps into readable Excel/Sheets date serial numbers.
2. European-to-Standard Formula: Correctly parse DD/MM/YYYY text strings into standardized YYYY-MM-DD dates using `DATE`, `MID`, `LEFT`, and `RIGHT`.
3. Working Days Calculation: Given a normalized Start Date in B2 and End Date in C2, calculate the exact number of working business days excluding weekends and a custom holiday range (H2:H10) using `NETWORKDAYS.INTL`.

Act as a High-Performance Spreadsheet Architect.

Existing Slow / Bloated Formula:
I have a complex formula that repeats the same slow `VLOOKUP` or calculation 4 separate times in a single cell:
```excel
=IF(ISERROR(VLOOKUP(A2, Data!A:Z, 14, FALSE)), "Missing", IF(VLOOKUP(A2, Data!A:Z, 14, FALSE)>1000, VLOOKUP(A2, Data!A:Z, 14, FALSE)*0.15, VLOOKUP(A2, Data!A:Z, 14, FALSE)*0.10))
```

Optimization Mandate:
1. Re-engineer with `LET`: Rewrite this formula using the modern `LET` function in Excel 365 / Google Sheets, defining the lookup as a single cached variable `val`.
2. Recalculation Elimination: Ensure the lookup runs exactly ONCE per row rather than four times, dramatically speeding up workbook calculation times.
3. Code Clarity: Format the `LET` formula with clear line breaks and descriptive variable names.
4. Provide the optimized formula ready for cell B2 with performance benchmarking notes.

Act as a Corporate Compensation & Financial Modeling Specialist.

Tiered Commission Schedule:
- Tier 1: $0 to $50,000 in sales -> 5% commission
- Tier 2: $50,001 to $100,000 in sales -> 8% commission on the marginal amount over $50k
- Tier 3: $100,001 to $250,000 in sales -> 12% commission on the marginal amount over $100k
- Tier 4: Greater than $250,000 in sales -> 15% commission on the marginal amount over $250k

Input:
Total Sales Revenue achieved in Cell A2 (e.g., $142,500).

Formula Requirements:
1. Marginal Calculation: The formula must calculate the exact progressive marginal commission across each band (NOT applying the highest tier to the entire total).
2. Clean Formula Architecture: Avoid 10 lines of unreadable nested `IF` statements. Use the professional financial modeling formula utilizing `SUMPRODUCT` and differential rate brackets, or a modern `LET` array formula.
3. Verification: Verify that $142,500 produces the exact correct payout ($2,500 + $4,000 + $5,100 = $11,600).

You are an Expert Spreadsheet Debugger and Technical Support Specialist.

Broken Formula Context:
- Target Platform: [Excel 365 / Google Sheets]
- Broken Formula:
```excel
[PASTE BROKEN FORMULA HERE]
```
- Error Displayed: [e.g., #VALUE! / #SPILL! / #REF! / #N/A / Circular Reference Alert]
- Expected Outcome: [DESCRIBE WHAT THE FORMULA SHOULD CALCULATE]
- Relevant Sample Data: [DESCRIBE DATA IN CELLS BEING REFERENCED]

Diagnostic Protocol:
1. Root Cause Analysis: Explain the exact technical breakdown (e.g., attempting mathematical operations on text numbers, locked range array spill blockage, index column count mismatch, or date formatting stored as text).
2. The Fix: Provide the corrected, hardened formula.
3. Prevention Tip: Explain 1 best practice to prevent this specific error from recurring across the workbook.

Act as a Financial Modeling Director.

Loan Parameters:
- Loan Principal: Cell B1 ($500,000)
- Annual Interest Rate: Cell B2 (6.5%)
- Loan Term (Years): Cell B3 (30 Years)
- Extra Monthly Principal Payment: Cell B4 ($500)

Generate Amortization Formulas for Row 7 Downwards:
Columns: Period (A) | Beginning Balance (B) | Scheduled Payment (C) | Extra Payment (D) | Principal Paid (E) | Interest Paid (F) | Ending Balance (G)

Formulas Required:
1. Monthly Payment Formula: Using `PMT` in Cell C7.
2. Interest Breakdown: Using `IPMT` in Cell F7 based on remaining balance.
3. Principal Breakdown: Using `PPMT` in Cell E7.
4. Dynamic Stop: Ensure formulas in row N automatically display $0 or blank once the loan balance reaches zero, preventing negative amortization.
Output the exact copy-paste formulas for all columns in Row 7.

Act as an Executive Dashboard Designer and Financial Controller.

Raw Ledger Sheet: Sheet 'Ledger'
- Col A: Transaction Date
- Col B: Department ('Engineering', 'Sales', 'Marketing', 'Ops')
- Col C: Expense Category ('Software', 'Travel', 'Payroll', 'Consulting')
- Col D: Amount ($)

Build 4 Dynamic Metric Formulas for the Executive Dashboard:
1. Metric 1 (Current Month Department Spend): Total spend in cell B2 where Department = 'Engineering' and Date is between `TODAY()-30` and `TODAY()`.
2. Metric 2 (Multi-Condition Exclusions): Total spend where Department = 'Marketing', Category is NOT 'Payroll', and Amount > $1,000.
3. Metric 3 (Year-to-Date Ratio): Percentage of total YTD spend consumed by the 'Sales' department.
4. Metric 4 (Average Transaction Size): Average expense size for 'Travel' during Q2 2026.
Ensure all formulas use robust `SUMIFS`, `COUNTIFS`, and `AVERAGEIFS` with dynamic date bounds.

Act as a Principal Enterprise Automation Engineer specializing in Google Apps Script and Excel VBA.

Automation Task: