Spreadsheets quietly run more of the world than any other piece of software. Quotas, budgets, payroll, marketing attribution, inventory, sales pipelines, donor lists — somewhere behind every one of them is a .xlsx file or a Google Sheet that someone is scared to touch. The fear is rational: spreadsheets combine real analytical power with the worst UX of any tool a working professional touches.

What changed in the last two years is that AI is now genuinely good at spreadsheets. ChatGPT, Claude, and Gemini can write formulas, clean messy data, build pivots, design dashboards, and explain a chart in plain English — but only when you give them a structured prompt that tells them what you have, what you want, and how you want it back. The same vague "help me with this spreadsheet" prompt that gave you a useless answer in 2023 still gives you a useless answer in 2026. The structured prompts in this guide do not.

Below are 20+ AI prompts for Excel and Google Sheets, organized by the jobs that eat the most time: writing and debugging formulas, cleaning messy data, building dashboards, summarizing and analyzing data, generating synthetic test data, and writing the VBA or Apps Script automation you keep telling yourself you will learn. Each prompt is a template you paste in, swap in your specifics, and use.

H2 Why AI Changes Spreadsheets

A quick framing note, because the marketing around AI-for-spreadsheets is loud and not always honest.

AI does not yet reliably "operate" inside your workbook the way a spreadsheet add-in does (though that landscape is changing fast). What it is excellent at, today, is:

Writing and explaining formulas, including the gnarly ones (INDEX/MATCH, XLOOKUP, array formulas, LAMBDA, custom number formats) that most spreadsheet users Google every single time.
Cleaning and reshaping data before it goes into Excel — splitting, merging, standardizing, deduplicating.
Designing dashboards — choosing the right chart, building out the cell layout, writing the formulas that drive it.
Explaining data and producing analysis once you tell it the structure of your sheet.
Writing VBA and Google Apps Script for the automation that nobody on your team has the time to maintain.

Every one of those saves a real spreadsheet user hours per week. None of them require trusting the AI to "go touch your data" unsupervised. The prompts below are built for a human-in-the-loop workflow, which is exactly how it should be.

How to Use These Prompts

A few conventions:

Replace [bracketed] text with your own. Brackets like [column_C] mean "the actual column header or letter you have."
Where a prompt asks for column headers, paste the actual headers from row 1 of your sheet — exact spelling and punctuation matter.
When a prompt asks you to "paste sample rows" — paste 5 to 10 anonymized rows so the model can see the shape of your data without your real numbers.
For Excel-specific prompts, specify "Excel" and the formula will use Excel conventions. For Sheets, specify "Google Sheets." Most formulas are identical but a few (XLOOKUP behavior, QUERY, REGEXMATCH) differ.

1. Formula Writing and Debug Prompts

Prompt 1.1 — Write a formula from a plain-English description

You are an expert spreadsheet builder. Write a formula for [Excel / Google Sheets] that does the following:

PURPOSE: [plain English description, e.g., "sum the values in column D where column B contains 'Q3' and column C is blank"]

MY DATA:
- Sheet name: [Sales]
- Column layout (first row headers): [A=Date, B=Region, C=Rep, D=Amount, E=Status]

CONSTRAINTS:
- Return ONLY the formula, no commentary.
- If the formula needs an array or dynamic range in [Excel / Google Sheets], make it spill/spill-aware correctly.
- Use structured (table-style) references if my data is in an Excel Table named [TableName]; otherwise use A1-style.

Prompt 1.2 — Explain a formula you don't understand

Explain this [Excel / Google Sheets] formula line by line to someone who has used spreadsheets for 5+ years but is not a formula expert. For each function or operator say (a) what it does and (b) what it returns in this specific formula. End with a one-sentence plain-English summary of what the whole thing computes.

Formula: [paste formula]

Prompt 1.3 — Debug a formula that returns an error

I'm getting a [#N/A, #VALUE!, #REF!, #NAME?, #DIV/0!, blank] error in [Excel / Google Sheets].

FORMULA: [paste formula]
EXPECTED RESULT: [what I think it should return]
WHAT IT ACTUALLY RETURNS: [paste]
MY DATA (first 5 rows): [paste headers + 5 anonymized rows]

Tell me:
1. Why this error is happening.
2. The corrected formula.
3. A note on edge cases — what input would break it again, and how to guard against it (e.g., IFERROR wrapper, IFNA, or TRIM on lookups).

Prompt 1.4 — Translate a VLOOKUP to XLOOKUP or INDEX/MATCH

Modernize this VLOOKUP formula into a [Google Sheets XLOOKUP / Excel XLOOKUP] equivalent, then also give me an INDEX/MATCH version as a fallback. Explain in one sentence why the modernized version is better (error handling, default value, two-way lookup, approximate match behavior).

=VLOOKUP(A2, CustomerList!A:D, 4, FALSE)

Prompt 1.5 — Build a nested IF the right way

Write a formula for cell [E2] in [Excel / Google Sheets] that assigns a tier label based on the value in [D2]:

- Below 100: "D"
- 100 to 499: "C"
- 500 to 999: "B"
- 1000 or above: "A"

Return the formula using the cleanest modern approach for [Excel / Google Sheets] — prefer IFS, or SWITCH, or a single XLOOKUP with approximate match, rather than nested IFs. Give me the formula on one line, then a one-sentence note on which approach you chose and why.

Prompt 1.6 — Array formula to summarize one sheet onto another

Build a summary sheet in [Excel / Google Sheets] that, for each unique value in column [B] of the [Transactions] sheet, sums column [F] and counts rows where column [G] equals "Paid".

Do not use a helper column. Do not require manual refresh beyond a normal recalc. Prefer [Excel's dynamic arrays: UNIQUE, SUMIFS, FILTER / Google Sheets' QUERY function] and explain which approach you chose.

Return:
1. The formulas to put in each cell of the summary sheet.
2. One sentence on where to put each formula.
3. A note on what breaks if a user types into the spilled range.

2. Data Cleaning Prompts

Data cleaning is where AI pays for itself instantly. Most agents' and analysts' "bad data" problems are actually "shape problems" — and AI is exceptionally good at reshaping.

Prompt 2.1 — Standardize and split a column

I have a [Google Sheets] column [A] titled "Full Name" that contains values like:

[paste 8-10 sample values, e.g., "John Smith", "Smith, John", "JOHN SMITH", "Dr. Jane Doe", "mary o'brien"]

GOAL: produce three new columns — First Name, Last Name, Honorific — all in proper case, with the honorific removed from the First Name column.

Return:
1. Three formulas I can paste in row 2 of columns [B], [C], [D].
2. A version that handles empty cells without producing errors.
3. A note on edge cases I should spot-check (initials, multiple last names, missing commas).

Prompt 2.2 — Deduplicate with fuzzy matching

I have ~5,000 rows in column [A:C] of a sheet titled [CustomerList]. Columns are [Name, Email, Phone]. Many duplicates exist but are not exact matches — phone is written as "555-123-4567" vs "(555) 123-4567", emails differ in capitalization, names contain typos (e.g., "Maree Smith" vs "Mary Smith").

What I want from you:
1. A formula-only deduplication approach using [QUERY + regex normalization / Excel UNIQUE with helper columns] that flags likely duplicates.
2. A clear step-by-step plan for me to deduplicate manually if the formula approach can't safely auto-delete.
3. A column formula that scores "similarity to the prior row" so I can sort and review.

Do NOT propose a script — keep it formula-based so I can audit it myself.

Prompt 2.3 — Pivot long data into wide with one formula

I have raw transactions in long format in [Sales] sheet: A=Date, B=Region, C=Product, D=Revenue. I want a wide summary where rows are unique regions and columns are unique products, with revenue at the intersection.

Give me a single dynamic-array formula in Excel (no PivotTable — I want it to update automatically as source data grows). Use whatever combination of UNIQUE, SUMIFS, FILTER, MAKEARRAY, or LET you need. Then give me a second formula approach in Google Sheets using QUERY that produces the same wide output.

Explain the spill behavior. Show me how to handle the "source data grew" case without re-editing the formula.

Prompt 2.4 — Clean up a messy text column

In my sheet, column [B] called "Address" contains entries like:

[paste 10 sample messy addresses, with inconsistent capitalization, stray periods, "St" vs "Street", "Apt" vs "#", trailing spaces]

Goal: a cleaned, normalized version in column [C]. Output should:
- Be in proper title case.
- Abbreviate consistently (e.g., "Street" all become "St", "Avenue" all become "Ave").
- Replace "Apt", "#", and "Unit" interchangeably with a single "Unit " prefix before the unit number.
- Trim trailing/leading spaces.
- Collapse multiple internal spaces.

Return two parallel formula columns I can paste: Column [C] (the cleaner) and Column [D] (a confidence flag showing "manual review" when the original couldn't be normalized confidently).

3. Dashboard Design Prompts

Prompt 3.1 — Dashboard design from a raw data sheet

You are a dashboard designer. My raw data lives on a sheet called [RawData] with these columns:

[paste headers]

GOAL: design a [monthly sales / KPI / operational] dashboard on a new sheet called [Dashboard].

Return:
1. A wireframe (ASCII layout) of the dashboard, with specific cells assigned to specific KPI cards and charts.
2. The exact formulas for 4 KPI cards (e.g., Total Revenue, MTD vs Last MTD, Win Rate, Open Pipeline).
3. A recommended chart type and data range for each of 3 charts, with the rationale for each choice (e.g. "use clustered bar not pie because the categories exceed 5").
4. A list of any helper formulas that belong on a hidden [Helper] sheet.
5. A note on which charts should be PivotCharts vs formula-driven dynamic arrays, given that I want the dashboard to refresh without manual intervention.

Spec: I'm in [Excel 365 / Google Sheets]. No VBA / Apps Script unless necessary for refresh.

Prompt 3.2 — Conditional formatting rules from a rule description

I'm in [Excel / Google Sheets]. Write the exact conditional-formatting setup for the following visual rules:

1. Highlight rows where column [E] (Status) equals "Overdue" — fill [Red-tinted color], text white.
2. Highlight column [D] (Amount) cells where the value is in the top 10% of the column — bold green font.
3. Use a 3-color scale on column [F] (Days Open) from green (=0) through yellow (=median) to red (=max).
4. Add a data bar to column [G] (Pipeline Value).

Return the rule by rule, including:
- The exact range the rule applies to.
- The formula expression for the "Use a formula to determine which cells to format" option, if needed.
- The specific RGB / color name I should pick in the user interface.

Prompt 3.3 — Build a slicer / filter chip-driven report

Build a filter-driven report in [Excel] using Slicers + FILTER function, given source data on sheet [Sales] with columns [Date, Region, Product, Channel, Revenue].

Step me through:
1. Converting source data to an Excel Table named [tblSales].
2. Creating Slicers for [Region] and [Channel] that drive a FILTER-based output range on a new [Report] sheet.
3. The exact FILTER/SUMIFS formula on the [Report] sheet that responds to the slicer selection (since slicers do not auto-trigger FILTER — explain the workaround using SUBTOTAL or a hidden COUNTA helper).

Tell me where each piece goes and the order in which I create them. No VBA.

4. Analysis and Summarization Prompts

Prompt 4.1 — Summarize a sheet you can describe (not paste)

My Google Sheet "Q4 Marketing" has these columns:
- A: Date (Oct 1 through Dec 31)
- B: Campaign Name
- C: Platform (Google, Meta, LinkedIn, TikTok, Email)
- D: Spend ($)
- E: Clicks
- F: Conversions
- G: Revenue ($)

About 600 rows total. I want to summarize:
1. Spend and revenue by Platform.
2. ROI (Revenue / Spend) by Campaign, top 10 by ROI.
3. Month-over-month trend for each Platform (Oct / Nov / Dec).
4. Identify any campaign whose November ROI dropped more than 50% from October.

Write me a Google Sheets QUERY formula for each of those four outputs, plus the cell where I should place each. Then explain in 2 sentences which single output I should put on a one-page exec summary if I can only show one.

Prompt 4.2 — Find the story in the numbers

Below are 6 months of weekly sales for one product line. Tell me the "story" in this data as if you were briefing a CEO who has 90 seconds.

[paste weekly series as: Week | Units Sold | List Price | Discount % | Revenue]

Constraints:
- Do not just describe the numbers; say what is interesting.
- Identify the single biggest risk and the single biggest opportunity.
- Propose 2 follow-up analyses that would change a decision if completed.
- End with one specific "so what" recommendation.

200 words max. Plain language, no jargon.

Prompt 4.3 — Forecast next month with caveats

Given this monthly time series of revenue for the last 24 months:

[paste: Month | Revenue]

Build a forecast for months 25, 26, and 27 using [Excel's FORECAST.ETS / Google Sheets' FORECAST.LINEAR or a custom moving average]. Show me:

1. The exact formula for the smoothed 3-month moving average.
2. The exact FORECAST formula for each of the 3 future months.
3. A one-paragraph note on what the forecast assumes (trend, seasonality) and what would invalidate it.
4. A "do not use this forecast if" list of 3 conditions.

No commentary beyond what I asked for. Tone: direct, no reassurance.

5. Synthetic Test Data and Templates

Prompt 5.1 — Generate realistic test rows for a model

Generate 50 rows of synthetic but realistic test data for a [Google Sheets] customer orders sheet. Schema:

A: OrderID (sequential, e.g., ORD-10001)
B: Date (uniformly random between Jan 1, 2026 and Jun 30, 2026)
C: Customer name (mix of common English, Hispanic, and South-Asian name patterns — proportional to a U.S. customer base)
D: Region (one of: West, Midwest, South, Northeast — weighted 30/20/25/25)
E: Product (one of: Starter, Pro, Enterprise — weighted 60/30/10)
F: Quantity (1 to 20, weighted toward small orders)
G: Unit Price (Starter=$29, Pro=$99, Enterprise=$499, but random ±5% to model occasional discounts)
H: Status (Delivered / Shipped / Pending / Cancelled — weighted 70/15/10/5)

Output as a single CSV block (no header, no explanations) that I can paste directly into the sheet starting at A2. Do not include any markdown code fences.

Prompt 5.2 — Build a budget template structure

I want a personal monthly budget template in [Google Sheets]. Design the structure:

1. Tab 1 "Dashboard": shows Category | Plan | Actual | Variance | % of Income.
2. Tab 2 "Transactions": columns Date, Description, Category, Amount, Type (income/expense).
3. Tab 3 "Categories": a category list with planned monthly amounts.

Tell me:
- The exact headers and column positions for each tab.
- The formula that aggregates Transactions into the Dashboard (use SUMIFS on category).
- The data validation rule that makes the Transactions Category a dropdown sourced from the Categories tab.
- A note on how to make the dashboard's "Actual" column auto-recalculate when a new transaction is added.

No Apps Script — formulas only.

6. VBA and Google Apps Script Prompts

Prompt 6.1 — Excel macro to clean a downloaded report

Write an Excel VBA macro that takes the active sheet (which is a freshly downloaded bank statement report) and:

1. Finds the header row (search for the text "Transaction Date" and delete all rows above it).
2. Deletes columns B, D, and F (the empty bank-specific columns).
3. Trims whitespace from every cell in the used range.
4. Converts column A (Transaction Date) from text "MM/DD/YYYY" to a real date.
5. Adds an Excel Table named "tblBank", table style "Table Style Medium 2".
6. Saves the workbook to the same folder with "_cleaned" appended to the filename.
7. Shows a MsgBox with the row count and a summary of what was done.

Write clean VBA with Option Explicit and meaningful variable names. Add inline comments explaining the non-obvious steps. Include error handling that shows the error description if anything fails.

Prompt 6.2 — Google Apps Script to email a weekly summary

Write a Google Apps Script that, every Monday at 9am, sends an email summary of the previous week's sales from a sheet titled "Sales" (columns A=Date, B=Region, C=Revenue).

Email recipient: [your email]
Subject: "Weekly Sales Summary — [Week of MM/DD]"

Email body must contain:
- Total revenue for the previous 7 calendar days.
- Revenue by region as a small text table.
- Top region and that region's share of total.
- A comparison to the prior 7 days (delta % and arrow).

The script should use GmailApp and MailApp correctly, set up a time-driven trigger, and include a `setupTrigger()` function I can run once from the Apps Script editor. Include error logging via Spreadsheet App's Logger.

Write production-quality code with comments. No console.log. Provide the trigger creation steps in plain English after the code block.

Common Mistakes to Avoid

Trusting AI with your real numbers in a public-tier model. If your data is sensitive, run against an enterprise-tier model with no training retention, or paste only schema/headers and let AI return formulas you apply locally.
Asking AI for a formula without giving it column letters and sheet names. If you skip that, you get a generic =SUMIF(A:A, "Q3", B:B) that does not map to your workbook.
Letting AI "operate" on your workbook without review. Every output here is meant to be a draft you apply, not a fire-and-forget modification. Read the formula. Run it on a copy first.
Forgetting about spilled ranges. Modern Excel dynamic arrays overwrite adjacent cells. If AI tells you a formula spills, leave room around the target cell before pressing Enter.
Skipping edge cases. Empty cells, leading spaces, mixed text/case, mixed date formats — these break formulas in silent ways. Always ask the prompt explicitly how it handles them.
Choosing VLOOKUP for new work. AI will modernize to XLOOKUP if you ask. If you don't ask, it may default to familiar VLOOKUPs that are fragile.

Tool Choice Matters

ChatGPT (GPT-4 family) is the strongest at writing Excel 365 dynamic-array formulas and Google Apps Script — its modern formula knowledge is the most current.
Claude is excellent for the analysis and "find the story" prompts — it reasons better than the others about what is and is not interesting in a series of numbers.
Gemini is the fastest for "give me a single formula" asks and integrates smoothly into Google Sheets if you use Google Workspace, but double-check its Apps Script for deprecated Apps Script API names.

A practical workflow: keep one ongoing conversation thread for one workbook in ChatGPT, paste your column headers and three sample rows once at the top, then drop in any prompt from this guide as a reply. The model retains context and your output gets tighter with each follow-up.

Build a Spreadsheet Prompt Library, Not Just a Spreadsheet

The professionals who get the most out of AI for spreadsheets don't memorize 50 functions — they save a handful of prompt templates tuned to the workbooks they touch every week, and reuse them. Take three prompts from this guide (probably one formula prompt, one cleaning prompt, one dashboard prompt), run them on a real spreadsheet this week, tweak the constraints until the output is right, and save the working versions. That is the first page of your spreadsheet prompt library, and it only gets more valuable.

PromptWright is built to make this real — a shared, version-controlled prompt library you can organize by job (formulas, cleaning, dashboards), annotate with the cells and sheet names each prompt expects, and pull from a single click. Start free at promptwright.net/signup and turn the prompts above into a repeatable workflow instead of a one-off experiment.

The Best AI Prompts for Excel and Google Sheets: 20+ Formulas, Dashboards & Analysis Prompts