AI Prompts for Excel: 25 Battle-Tested Prompts for Analysts (2026)

A bad prompt gets you a #REF! error and a confidently wrong formula. A good prompt gets you a working XLOOKUP, a sensitivity table, or a draft variance commentary in under thirty seconds. The gap between the two is rarely the model — it's the prompt. After auditing thousands of analyst-AI interactions in 2026, the pattern is clear: the analysts who get real leverage from AI prompts for Excel write requests with structure, context, and constraints, not vague questions.

This is a working library of 25 prompts that financial analysts at banks, PE funds, and FP&A teams actually use. Every prompt is copy-paste ready, every category maps to a real weekly task, and every example tells you what to swap in for your own workbook.

Why AI Prompts for Excel Beat Manual Work in 2026

The honest answer: not because AI is smarter than you, but because the round-trip from "what formula do I need" to "working formula in cell C42" collapses from 8 minutes to 30 seconds. Multiply that across 40-50 weekly formula moments and you reclaim a full workday.

The catch is that AI quality scales almost linearly with prompt quality. A one-liner like "write me an XLOOKUP" returns generic syntax. A structured prompt with column references, expected output, and edge cases returns code you can paste without modification.

Task	Manual Time	Vague Prompt	Structured Prompt	Win
Build XLOOKUP across sheets	6 min	2 min + 1 revision	25 sec	14x
Debug #N/A in nested IF	10 min	4 min + 2 revisions	40 sec	15x
Draft variance commentary	25 min	8 min + heavy edit	3 min	8x
Build DCF skeleton	90 min	35 min + cleanup	12 min	7.5x
Audit broken model	4 hours	90 min	30 min	8x

💡 Pro Tip: Save your best prompts as snippets in your text expander (Raycast, TextExpander, Alfred). The analysts who get the most leverage out of AI build a personal prompt library, not a one-off habit.

What Makes a Great AI Prompt for Excel?

A great prompt for Excel work follows a four-part structure: Context (what your data looks like), Goal (what you want produced), Constraints (Excel version, no helper columns, etc.), and Format (formula only, or formula + explanation). Skip any one and you get back generic output that needs rework.

The CGCF Framework

[Context] My workbook has a Sheet "Deals" with columns A:H — Date, Deal ID,
Sponsor, Stage, EV, Equity, Debt, IRR. About 1,200 rows. Excel 365.

[Goal] I need the median IRR for deals where Stage = "Closed" and
EV > $500M, grouped by Sponsor.

[Constraints] Single dynamic-array formula, no helper columns,
no pivot table. Must update when new rows are added.

[Format] Just the formula, then 2 sentences explaining how it works.

Paste that and you'll get a clean GROUPBY or LET + FILTER + MEDIAN solution. Drop the constraints and you'll get a pivot table walkthrough instead.

ℹ️ Note: AI models in 2026 are excellent at Excel 365 formulas but inconsistent on legacy versions. Always state your Excel version when asking for formulas — especially if you can't use dynamic arrays, LAMBDA, or GROUPBY.

graph LR
    A[Context: data shape] --> E[Prompt]
    B[Goal: desired output] --> E
    C[Constraints: version, style] --> E
    D[Format: formula, code, prose] --> E
    E --> F[AI Response]
    F --> G{Works first try?}
    G -->|Yes| H[Paste & ship]
    G -->|No| I[Refine constraint]
    I --> E

Prompts for Excel Formula Generation

These are the workhorses — the prompts you'll use 5-15 times a day. Notice how each one specifies the data shape, the expected behavior on edge cases, and the formula style.

1. Lookup Across Sheets

I have two sheets. "Trades" has columns: TradeID (A), Date (B), Ticker (C),
Notional (D). "Reference" has Ticker (A), Sector (B), Country (C), Beta (D).

Write an XLOOKUP that returns the Sector for each Ticker in Trades.
Return "Unmapped" if not found. Excel 365. Should spill down automatically.

2. Multi-Criteria Aggregation

Range: A2:F10000. Columns: Region, Segment, Product, Month, Revenue, COGS.

Give me one formula that returns total Gross Profit (Revenue - COGS)
where Region = cell H2, Segment = cell H3, and Month is in the last 3 months.
Use SUMPRODUCT — I'm on Excel 2019, no dynamic arrays.

3. Convert Messy Text to Date

Column A has dates as text in mixed formats: "Jan-25", "01/2025", "2025-01",
"January 2025". I need a real date (1st of the month) in column B.

Single formula, no helper columns, must handle all four formats.
Excel 365 — TEXTBEFORE, TEXTAFTER, and IFERROR are fair game.

4. Running Total That Resets by Group

Columns: A = Customer, B = Date (sorted), C = Invoice Amount.
I want column D to show a running total of invoices per customer
that resets each time the Customer changes.

Excel 365. Use SCAN or LET if it makes the formula cleaner.

5. Bucket a Number Into Bands

Cell A2 contains a deal size. I want to return the band:
- < $25M → "Lower MM"
- $25M–$100M → "Core MM"
- $100M–$500M → "Upper MM"
- > $500M → "Large Cap"

Use SWITCH or IFS — not nested IF. Excel 365.

💡 Pro Tip: When AI returns a long nested formula, ask: "Rewrite this using LET to name the intermediate steps and add inline comments." You'll get a self-documenting version that the next analyst on your team can actually read.

Prompts for Financial Modeling

These prompts build the bones of a model. They work best when you provide structure first — "here are my assumption cells" — and let the AI fill in the mechanics.

6. DCF Skeleton From Assumptions

Build a 5-year DCF skeleton in Excel for [Company X]. Assumptions live
in cells B3:B12 (Revenue Y1, Growth Y2-Y5, EBITDA margin, D&A % of revenue,
Capex % of revenue, NWC % of revenue, Tax rate, WACC, Terminal growth,
Exit multiple).

Output: (1) Year 1-5 Revenue → EBITDA → EBIT → NOPAT → FCF rows
with the exact Excel formulas, (2) Terminal value using both perpetuity
growth and exit multiple methods, (3) PV of FCF using XNPV with explicit
cash flow dates, (4) Enterprise Value reconciliation.

Show formulas, not values. Use named ranges where they improve clarity.

The DCF structure in this prompt matches the terminal-value and enterprise-value build in our step-by-step DCF model guide — including XNPV discounting and both perpetuity and exit-multiple terminal value methods.

7. Three-Statement Linkage Check

I have an Income Statement (rows 5-35), Balance Sheet (rows 40-80),
and Cash Flow Statement (rows 85-115) on Sheet "Model".

Write a 1-line formula in cell B120 that returns "OK" if:
- Net Income on the IS matches the top line of the CFS
- Ending Cash on the CFS matches Cash on the BS
- Total Assets = Total Liabilities + Equity

Otherwise return which check failed. Tolerance: $1.

8. LBO Returns Bridge

Build a returns attribution table for an LBO model. Inputs:
- Entry EV, Entry EBITDA → Entry multiple
- Exit EV, Exit EBITDA → Exit multiple
- Net Debt at entry and exit
- Sponsor Equity at entry and exit

Decompose total equity value creation into: (1) EBITDA growth,
(2) Multiple expansion/contraction, (3) Debt paydown, (4) FCF accumulation.
Each component in $ and % of total. Excel formulas referencing
the assumption cells.

9. Sensitivity Table Generator

I have a DCF in Sheet1, cell B50 = implied share price.
Inputs: WACC in cell B10, Terminal Growth in cell B11.

Build a two-variable data table:
- WACC across the top: 8.0%, 8.5%, 9.0%, 9.5%, 10.0%
- Terminal Growth down the side: 1.5%, 2.0%, 2.5%, 3.0%, 3.5%

Walk me through exact clicks (Data → What-If → Data Table) and
give me the conditional formatting rule to flag cells where the
output is within ±10% of the base case.

10. Waterfall Distribution

PE waterfall with 4 tiers:
- Tier 1: 100% to LP until 8% pref IRR
- Tier 2: 100% to GP catch-up until GP receives 20% of profits above ROC
- Tier 3: 80/20 LP/GP split
- Tier 4 (super carry): >25% gross IRR → 70/30 LP/GP

Given LP contributions (A2:A10) and distributions (B2:B10), write the
LP and GP cash flow columns using LET-based formulas. Annual periods.

⚠️ Warning: AI models will confidently produce waterfall formulas that look right but mis-handle the catch-up tier. Always reconcile total GP + LP cash flows back to the deal-level distributions, and stress-test with a known-good example before trusting the formula.

For a complete walkthrough of GP/LP distribution mechanics — including preferred return tiers, catch-up math, and carried interest — see our PE waterfall model guide in Excel.

Prompts for FP&A and Reporting

FP&A work is where AI prompts deliver the most visible time savings — because the work is half formulas, half writing.

11. Variance Commentary Draft

Here is my Budget vs Actual table for Q2:
[paste the table — Region, Budget, Actual, Variance, Variance %]

Draft 5-7 bullet points of variance commentary for the CFO. Tone: factual,
not promotional. Highlight the 3 largest absolute variances and the 2 largest
percentage variances. Suggest 1 follow-up question per variance.

For the underlying Excel framework — including the variance formulas, conditional formatting, and monthly reporting structure — see our budget vs. actual variance analysis guide.

12. Forecast With Seasonality

Monthly revenue last 24 months in cells B2:B25 (oldest to newest).
Build a 12-month forecast using:
(1) Linear trend on the 24-month series
(2) Multiplicative seasonality factor (this month / 12-month rolling avg
    for the same month last year)

Single formula per forecast cell. Excel 365. Explain which assumption
is most likely to break.

13. KPI Dashboard Layout

Design a 1-page KPI dashboard layout in Excel for a $200M SaaS company.
Must include: ARR, NRR, GRR, Magic Number, CAC Payback, Rule of 40,
Burn Multiple. Cash runway.

For each KPI: (1) which cell range holds the input data, (2) the formula,
(3) the conditional formatting rule (red/yellow/green thresholds),
(4) a one-line "why this matters" tooltip.

Output as a markdown table I can hand to the analyst building it.

14. Reformat a Trial Balance

I have a 600-row trial balance in columns A:D — Account Code, Account Name,
Debit, Credit. I need it grouped into Income Statement and Balance Sheet
sections with subtotals (Revenue, COGS, OpEx, EBITDA, Net Income on IS;
Current Assets, Non-Current Assets, Current Liab, Long-Term Debt, Equity on BS).

Give me the Power Query M code to do this, assuming the account
code's first digit tells me the section (1xxx = Assets, 2xxx = Liabilities,
3xxx = Equity, 4xxx = Revenue, 5xxx = COGS, 6xxx-7xxx = OpEx).

15. Board-Ready Slide Bullets

Here is the EBITDA bridge from FY24 to FY25:
- FY24 EBITDA: $48M
- Volume: +$6M
- Price: +$3M
- Mix: -$1M
- Input cost inflation: -$4M
- Wage inflation: -$2M
- One-time legal: -$1.5M
- Productivity: +$3.5M
- FY25 EBITDA: $52M

Write 3 board-ready bullets (max 20 words each) summarizing the bridge.
Tone: confident but not defensive. No filler words like "approximately."

Prompts for Auditing and Debugging

This is where AI saves the most time per dollar — finding errors a human eye glides past. For a systematic workflow that applies AI to each audit phase — formula-consistency scanning, integrity check generation, and hardcode detection — see our AI financial model auditing guide for Excel.

16. Trace a #REF! Error

I have a formula returning #REF! in cell N42 of sheet "Model":

=IFERROR(VLOOKUP($A42, #REF!, MATCH(N$3, Lookups!$1:$1, 0), FALSE), 0)

The formula used to work. What likely happened, what would you check first,
and how do I rebuild it? I want this in XLOOKUP form going forward.

17. Find Hardcoded Values

Write a VBA macro that scans the active worksheet for cells that look
like they should be formulas but are hardcoded numbers. Heuristics:
(1) Cell is in a column where >80% of cells contain formulas
(2) Value > 100 or < -100 (likely not a typed-in label)
(3) Cell is not in row 1 (likely not a header)

Highlight matches in light red. Output a list of cell addresses in the
Immediate window.

18. Circular Reference Hunt

My model has a circular reference warning but iterative calc is on.
I'm not sure if the circularity is intentional (WACC ↔ debt) or a bug.

Walk me through how to: (1) find all circular references using the
Excel Formula Auditing menu, (2) document the intentional ones with
named ranges and comments, (3) test for stability by toggling
iteration off and checking which cells turn into errors.

19. Compare Two Versions of a Model

I have v7 and v8 of a model in two workbooks. I need a diff:
- Which cells changed values
- Which cells changed formulas (not just values)
- Which sheets, rows, or columns were added or deleted

Power Query approach preferred. Give me the M code and the layout
of the diff report.

20. Reasonableness Check on a Model

I'm pasting in the key outputs of a 5-year forecast. Tell me which
look implausible and why. Be specific — name the line item, the year,
and what range you'd expect for a company in [industry].

[paste the table]

💡 Pro Tip: For auditing, the prompt that returns the most value is "What would a skeptical CFO ask about this model?" — it generates a 10-question stress test that catches assumptions you've stopped noticing.

Prompts for Data Cleaning and Power Query

21. Clean Bank Transaction Data

I have a CSV of 8,000 bank transactions: Date, Description, Amount, Account.
Descriptions are messy ("AMZN MKTPLC*A1B2C3 SEA WA", "STRIPE TRANSFER REF8821"
etc.). I need a categorized table for monthly P&L reporting.

Give me a Power Query M script that:
(1) Parses the merchant from the description
(2) Maps merchants to categories using an editable lookup table on Sheet "Map"
(3) Flags transactions with no category match for manual review
(4) Outputs to a worksheet refreshable on demand

22. Pivot Long-to-Wide

My data is in long format: columns Date, Metric, Value (where Metric is
one of "Revenue", "COGS", "OpEx", "EBITDA"). 8,000 rows over 24 months.

Reshape to wide format — Date down the rows, Metrics across the columns,
Value as the cell value. Power Query preferred, but a dynamic-array formula
using GROUPBY or PIVOTBY is also fine. Show me both.

23. Merge Workbooks Into One

I have 12 monthly close workbooks in folder "C:\Close\2026". Each has
the same Sheet "TB" with columns Account, Debit, Credit.

Build a Power Query that appends them all, adds a "Period" column derived
from the file name (e.g., "TB_2026_03.xlsx" → 2026-03-01), and removes
totals rows (where Account = "Total" or contains "Total"). Refresh
should pick up new files automatically.

How Do You Get Better Results From AI Prompts in Excel?

The single biggest lever is showing the model your data — not just describing it. Paste 3-5 sample rows. Name your columns. State your Excel version. Specify edge cases. And when the first response misses, refine one constraint at a time rather than rewriting the whole prompt.

The Refinement Loop

1. First prompt — full CGCF (Context, Goal, Constraints, Format)
2. Test — paste into Excel, check edge cases (blanks, zeros, duplicates, sort order)
3. If wrong — quote the specific failure and add one constraint: "This returns 0 for blank cells. Treat blank as missing and skip it in the count."
4. Re-test — if still wrong, ask for an alternative approach, not a fix

Example: A formula returning incorrect MEDIAN for a filtered set. Refinement: "This formula counts hidden rows when I filter the table. Rewrite using SUBTOTAL or AGGREGATE so it ignores filtered rows."

What Senior Analysts Do Differently

Junior analysts treat the AI like a search engine. Senior analysts treat it like a fast-but-junior teammate: they brief it on context, they review every line, and they refuse to ship anything they don't understand. That last part matters — pasting a working formula you can't explain in a meeting is how careers stall.

graph TD
    A[Task arrives] --> B{Have I done this before?}
    B -->|Yes| C[Use saved prompt template]
    B -->|No| D[Write CGCF prompt]
    C --> E[AI returns formula]
    D --> E
    E --> F{Works on edge cases?}
    F -->|Yes| G[Document in workbook]
    F -->|No| H[Refine one constraint]
    H --> E
    G --> I[Save template for next time]

Common Mistakes When Prompting AI for Excel

Mistake 1: Asking for "The Best" Formula

There is no universally best formula. XLOOKUP beats VLOOKUP in 99% of cases — but it doesn't exist in Excel 2016. Always ground the prompt in your version and your team's standards.

Mistake 2: Pasting Sensitive Data Into a Public AI

Trial balances, deal terms, and salary data should never go into a consumer chatbot. Use an enterprise AI deployment with data-handling guarantees, or anonymize the data first (replace company names, scale all numbers by a random multiplier).

Mistake 3: Skipping the Edge Cases

The AI assumes clean data. Real data has blanks, leading spaces, mixed types, and 600-row PDFs of trial balances exported as text. State the messiness in the prompt or expect to find it later.

Mistake 4: Not Verifying the Math

AI-generated formulas can be syntactically perfect and financially wrong. A waterfall that splits 80/20 when it should be 90/10 will pass a code review and fail a deal review. Always reconcile totals.

⚠️ Warning: A formula that "looks right" and returns a plausible number is the most dangerous output AI can give you. Build a 30-second reconciliation check into every model — totals, balance sheet balance, ending cash on CFS = ending cash on BS — and run it after every AI-assisted change.

Mistake 5: One Giant Prompt for a Whole Model

Asking the AI to build a full three-statement model in one shot returns a Frankenstein. Build it in pieces: revenue, then COGS, then OpEx, then linkage. Verify each piece before stacking the next.

Where AI-Assisted Excel Is Going

The model-in-the-chatbot workflow is being replaced by AI that runs inside the workbook with full context of your tabs, named ranges, and prior edits. Tools like VeloraAI read the surrounding cells, so the same prompt produces context-aware formulas that match your model's conventions rather than generic syntax. That cuts the refinement loop from 3-4 turns to 0-1 — which is where the 10x productivity numbers actually come from.

Frequently Asked Questions

Are AI prompts reliable enough for client-facing financial models?

For draft work, yes. For the final deliverable, never trust an AI-generated formula without manual review and reconciliation. Treat AI like a junior analyst — fast, capable, occasionally wrong. The human signs the model.

What's the best AI tool for Excel formulas in 2026?

For embedded workflows: VeloraAI and Copilot run inside Excel and read your workbook context. For research and writing-heavy tasks: ChatGPT-5 and Claude 4.x produce the cleanest formulas and the best variance commentary. Many analysts use both — embedded for formulas, chat for prose.

Can I use ChatGPT prompts on confidential deal data?

Not without an enterprise tier with data residency and no-training guarantees. For sensitive data, use anonymized inputs (rename tickers, scale numbers by a random factor) or deploy an AI tool with private inference. Check your firm's IT policy before pasting anything from a deal model.

Do I need to learn prompt engineering as a financial analyst?

You don't need a course — you need a habit. The CGCF framework (Context, Goal, Constraints, Format) covers 90% of finance prompts. Build a personal library of 20-30 prompts for your most common tasks and you'll move faster than colleagues who improvise every time.

How do I prompt AI to follow my model's conventions?

Tell it explicitly: "Inputs in blue, formulas in black, cross-sheet links in green. Negative numbers in parentheses. No hardcoded numbers in calculation cells." AI models in 2026 follow style guidance well — they just need it stated. Save the style block as a snippet and prepend it to every modeling prompt.

The Next Step

Pick three prompts from this list that map to tasks you'll do this week — a formula, a variance commentary, an audit check — and save them as snippets. The first time you use them you'll save 10 minutes. The fiftieth time, you'll wonder how you ever worked without them.

If you'd rather skip the copy-paste step entirely, that's exactly what we're building at VeloraAI: an Excel add-in that runs these prompts against your live workbook with full context of your cells, formats, and named ranges — no tab-switching, no anonymizing, no rework.