AI AI Tools Guidebook
Prompt Library

Excel / Spreadsheet Analysis Prompts

11 prompts that feed AI schemas and aggregates instead of 5,000 raw rows — clean columns, outlier hunts, pivots, narrative reports, formula explainers, sanity audits.

Published: Author: AI Productivity Guide Team 🌐 查看中文版本

AI is great at spreadsheet thinking — picking the right pivot, naming the outlier hypothesis, writing the formula — and terrible at raw 10K-row CSVs pasted into chat (token budget blows, types get guessed wrong, the model hallucinates rows it never saw). The trick is to feed it schemas, 5-row samples, and aggregate questions — never the whole sheet — and to verify every number it returns against the actual file. These prompts cover the analytical workflow start to finish: clean columns, hunt outliers, propose pivots, write defensible formulas, generate narrative summaries for your boss. Pair with Excel formula explanation prompts when an existing VLOOKUP or LAMBDA needs decoding before you change it.

Best for

  • Sales reports
  • Survey data
  • Product KPIs
  • Financial sheets

1. Schema + ask

I have a CSV with these columns: {list with types}. 5 sample rows below. Goal: {question, e.g., "find drop-off in funnel"}. Give me the analytical approach + the formulas / queries to run.

Samples:
{paste 5 rows}

2. Clean text columns

Column {col} contains messy text: {paste 20 samples}. Generate: regex / formula / Python snippet to normalize into {target format}. Show 5 before-after examples.

3. Detect outliers

For column {col} (samples below), describe a method to detect outliers and the threshold I should use given my context: {what kind of data}. Give the formula.

{samples}

4. Suggest pivot table

My data has columns: {list}. Goal: {analytical question}. Suggest 3 pivot table configurations (rows, cols, values, filters) that would answer this. Rank by usefulness.

5. Explain a row / cell anomaly

Row {N} looks anomalous: {paste}. Given the rest of the sheet has these characteristics: {summary}, what’s likely going on? Suggest 3 hypotheses + how to check each.

6. Write a formula for X

I want to compute {goal} in Excel / Google Sheets. Sample data structure: {paste}. Give me a formula. Explain what each part does so I can adapt it.

7. Compare two periods

I have monthly data for {months}. Compare {metric} between {period A} and {period B}: absolute change, % change, biggest swings by category. Output as a 5-row table.

8. Spreadsheet → narrative

Below is a pivot table summary (paste). Write a 150-word narrative I can send to my boss explaining what happened. Include 1 hypothesis for the biggest change.

{paste}

9. VLOOKUP / XLOOKUP / INDEX-MATCH chooser

I need to join {sheet A} to {sheet B} on {key}. Sample structures: {paste}. Recommend the best lookup approach for my version of Excel / Sheets, the formula, and 2 edge cases (duplicate keys, missing values) and how to handle each.

10. Sanity-audit on AI’s own answer

You just produced {paste AI's answer with numbers}. Audit it for: (a) units / scale, (b) rows that may have been dropped, (c) silent assumptions about null handling, (d) sample-bias from the 5 rows I showed. Output a checklist I can verify before sending the answer to my boss.

11. Reproducible workbook structure

For {analysis goal} on data shaped {schema}, recommend a 3-sheet workbook structure: raw data, transforms, dashboard. Spec: what goes on each sheet, which formulas live where, what's a named range, what's a query. End with the 1 cell my boss should look at first.

Common mistakes

  • Pasting 5,000 rows into chat — token budget blows and the model invents rows
  • No schema given — AI silently guesses column types and propagates the error
  • Trusting AI’s numbers without spot-checking against the actual file
  • Using AI’s formula without understanding what each part does — un-debuggable later
  • Asking for “insights” with no specific question — output is generic and unhelpful
  • Forgetting locale (comma vs semicolon as argument separator) when applying the formula

Tags: #Prompt #Productivity #Excel #Data analysis

Related Articles