ChatGPT Prompts for Spreadsheets (Excel + Sheets Universal)
20 copy-paste ChatGPT prompts for spreadsheets: data structures, formula patterns, cleaning, analysis, dashboards, and the workflows that work in Excel + Google Sheets equally.
In short: This page contains 16 copy-paste ready prompts, organized into 4 categories with a description and pro tip for each. The first 5 prompts are free instantly, no signup needed. Hand-curated and tested by the AI Academy team.
Data Structures
4 promptsSpreadsheet Schema Design
1/16✨ What it does
ChatGPT designs a spreadsheet structure for [use case] with sheet purposes, columns, naming, primary keys, and relationships between sheets so you structure it before you have to rebuild. Fill the use case, then create the sheets and keys before you paste data.
Design spreadsheet structure for [use case]. Output: tabs/sheets purpose, columns per sheet, naming convention, primary keys, relationships between sheets. Structure upfront > restructure later.
Pro tip: Default spreadsheet = ad-hoc structure. Designed schema (separate sheets per entity, consistent columns, relationships clear) = sustainable. 30 min upfront saves hours.
Database vs Spreadsheet Decision
2/16✨ What it does
ChatGPT helps you decide spreadsheet versus database for [use case]: spreadsheet if you have under 10K rows, one user, and ad-hoc work; a database for high volume, multi-user, or complex queries. Describe the use case, then pick the store that matches your volume.
Decide: spreadsheet vs database for [use case]. Output: spreadsheet OK for: <10K rows, single user, ad-hoc. Database for: high-volume, multi-user, complex queries. Most cases are intermediate.
Pro tip: Spreadsheets break at scale. 50K rows + 10 users + complex queries = database needed. Forced spreadsheet at scale = chaos + corruption.
Tidy Data Principles
3/16✨ What it does
ChatGPT restructures [paste messy data] into tidy form (each variable a column, each observation a row, each type on its own sheet) and lists the transformation steps. Paste the messy block, then apply the layout before you run any analysis.
Restructure [paste messy data] into tidy data. Tidy = each variable a column, each observation a row, each type of observation a sheet. Output: restructured layout, transformation steps. Tidy data = analyzable.
Pro tip: Messy data (multi-header, repeated columns, mixed types) = hard to analyze. Tidy data = pivot-able + filter-able + analyze-able. Standard form = the unlock.
Wide vs Long Format
4/16✨ What it does
ChatGPT recommends wide versus long format for [data], when wide works for input and long for analysis, and how to convert programmatically versus by hand. Name the data, then convert to long before you pivot or chart it.
Wide vs long format for [data]. Output: per use case, which works (wide for input, long for analysis), conversion methods. Convert programmatically vs manually.
Pro tip: Wide format = readable to humans. Long format = analyzable by tools. Often: input wide, transform to long for analysis. Tools (Power Query, pivot) handle transforms.
Prompts get you started. Tutorials level you up.
A growing library of 300+ hands-on AI tutorials. New tutorials added every week.
Formulas + Calculations
4 promptsFormula Library
5/16✨ What it does
ChatGPT builds a formula library for [use case] covering lookups, aggregations, text functions, date math, and conditionals, with examples you can copy. Fill the use case, then paste one example into a spare sheet and test it on your data.
Common formula patterns for [use case]. Output: lookup (VLOOKUP / XLOOKUP / INDEX-MATCH), aggregations (SUMIF, COUNTIF, AVERAGEIF), text manipulation (LEFT, RIGHT, MID, FIND, SUBSTITUTE), date math, conditional logic. With examples.
Pro tip: Master 10 formula patterns = 80% of spreadsheet needs. Reusable patterns + examples = build library; refer when building. Faster than reinventing.
XLOOKUP / VLOOKUP / INDEX-MATCH
6/16✨ What it does
ChatGPT picks the best lookup for [scenario]: XLOOKUP as the modern default, VLOOKUP for legacy files, and INDEX-MATCH when you need flexibility or speed, with a recommendation. Spell out the scenario, then use the recommended function in a test column first.
Best lookup formula for [scenario]. Output: XLOOKUP (modern, simpler) vs VLOOKUP (legacy, common) vs INDEX-MATCH (flexible, faster), recommendations. XLOOKUP increasingly default.
Pro tip: XLOOKUP > VLOOKUP for new work (simpler, more flexible). VLOOKUP common; users know it. INDEX-MATCH = power-user. Choose by team familiarity + need.
Array Formulas
7/16✨ What it does
ChatGPT writes array formulas for [calculation] using dynamic arrays in modern Excel or Sheets, spill behavior, and patterns such as FILTER, SORT, UNIQUE, and SEQUENCE. Name the calculation, then enter the formula where it can spill without overwriting your cells.
Array formulas for [calculation]. Output: dynamic arrays (modern Excel/Sheets), spill behavior, common array formula patterns (FILTER, SORT, UNIQUE, SEQUENCE). Array formulas = power.
Pro tip: Dynamic arrays (Excel 365, modern Sheets) = transform spreadsheet thinking. FILTER + SORT + UNIQUE = mini queries. Underused; the productivity unlock.
Volatile Functions Audit
8/16✨ What it does
ChatGPT audits [Paste spreadsheet] for volatile functions (NOW, TODAY, RAND, OFFSET, INDIRECT) that recalculate on every change, and suggests replacements that help performance. Paste a sample, then replace the volatiles it flags on your large sheets.
[Paste spreadsheet]. Audit volatile functions: NOW, TODAY, RAND, OFFSET, INDIRECT (recalculates on every change). Replace if possible; impacts performance. Big spreadsheets = matters.
Pro tip: Volatile functions = recalc constantly = slow spreadsheet. Replace with non-volatile alternatives (XLOOKUP > INDIRECT). Performance lift on large sheets.
Cleaning + Validation
4 promptsData Cleaning Workflow
9/16✨ What it does
ChatGPT builds a cleaning workflow for [Paste messy data]: trim whitespace, standardize case, remove duplicates, format dates, fix types, and handle nulls, with steps and formulas. Paste the messy data, then run the steps in order on a copy of your sheet.
[Paste messy data]. Cleaning workflow: trim whitespace, standardize case, remove duplicates, format dates, fix data types, handle nulls. Output: step-by-step + formulas. Manual cleanup = error-prone.
Pro tip: Manual cleaning = errors + slow. Power Query (Excel) / Power Query in Sheets / formulas = repeatable + reliable. Discipline of cleaning before analysis = trust in results.
Data Validation Rules
10/16✨ What it does
ChatGPT writes data-validation rules for [columns] with type (list, number range, date range, regex, custom formula) and an error message per column so bad entries get blocked. List the columns, then add the validation on your live sheet before people type.
Data validation for [columns]. Output: per column, validation type (list, number range, date range, regex, custom formula), error message. Prevents bad data entry.
Pro tip: No validation = bad data. With validation = clean data at entry. Catches typos, wrong types, out-of-range values. Front-load validation > clean later.
Duplicate Detection
11/16✨ What it does
ChatGPT finds duplicates in [data] using Excel Remove Duplicates, a Sheets unique formula, conditional formatting, or a COUNTIF flag, plus how to clean them. Point it at the data, then delete or merge only after you review the flagged rows.
Find duplicates in [data]. Methods: built-in (Excel Remove Duplicates, Sheets unique formula), conditional formatting, COUNTIF flag. Output: approach + how to clean.
Pro tip: Duplicate detection: by single column (easy) or multiple columns (concatenate first). Always inspect before deleting; sometimes "duplicates" are different (e.g., John Smith from different cities).
Outlier Detection
12/16✨ What it does
ChatGPT detects outliers in [Data] with standard deviation, percentile, IQR, and charts, then helps you decide if each is real, an error, or something to exclude. Paste the data, then decide per flagged row before you drop it.
[Data]. Detect outliers: standard deviation method, percentile method, IQR method, visual via charts. Output: identification + decision (real outlier? data error? exclude?). Outliers can mislead.
Pro tip: Outliers can be: data error, real but extreme, or signal (the interesting case). Don't reflexively remove. Investigate; sometimes outliers ARE the story.
Like these prompts? There are full tutorials behind them.
Learn the workflows, not just the prompts. 300+ easy-to-follow tutorials inside AI Academy — and growing every week.
Analysis + Reporting
4 promptsAnalysis from Question
13/16✨ What it does
ChatGPT structures a spreadsheet analysis around question [describe] with the data needed, calculations, a visualization, a summary, and the decision it should inform. Write the question first, then build only the sheets that answer your question.
Question: [describe]. Help structure spreadsheet analysis. Output: data needed, calculations required, visualization, summary, decision. Analysis-as-question > data-dump.
Pro tip: Most analysis fails at clarity of question. "Analyze sales" = data dump. "Why did Q3 sales drop in West region?" = focused analysis. Question first.
Pivot Table Strategy
14/16✨ What it does
ChatGPT designs a pivot table for [question] with rows, columns, values, filters, and calculated fields so you can aggregate quickly. State the question, then build the pivot and check one total against the raw sheet.
Build pivot table for [question]. Output: rows, columns, values, filters, calculated fields. Pivot = aggregate analysis fast.
Pro tip: Pivots = analyst superpower. Sum/count/average across categories without writing formulas. Most spreadsheet users underuse pivots; the discipline = analytical speed.
Dashboard Layout
15/16✨ What it does
ChatGPT lays out a dashboard sheet for [audience] with 5-7 top KPIs, charts by importance, a filter cell, and drill-down navigation, simpler than a BI tool when that is enough. Name the audience, then put the KPIs at the top before you add charts.
Build dashboard sheet for [audience]. Output: top-of-page key metrics (5-7 KPIs), charts arranged by importance, filter cell, drill-down navigation. Spreadsheet dashboard = simpler than BI tool sometimes.
Pro tip: Spreadsheet dashboards work for: small data, simple metrics, frequent updates. Power BI / Tableau for: large data, governance, sharing. Match tool to need.
Variance Analysis
16/16✨ What it does
ChatGPT builds a variance analysis of [actual] versus [target/budget/prior] with dollar and percent variance per line, commentary on the top moves, and an executive summary for anything over 5%. Fill both blanks, then write a sentence on every variance past 5% in your pack.
Variance analysis: [actual] vs [target/budget/prior]. Output: variance per line ($ + %), commentary on top variances, drilldown if available, summary for executives. Variance > 5% explained.
Pro tip: Variance reports without commentary = numbers. With commentary on biggest variances = actionable. Story > data.
Free tool
Formula Genie
Describe what you need and get the exact Excel or Google Sheets formula.
Frequently Asked Questions
Prompts are the starting line. Tutorials are the finish.
A growing library of 300+ hands-on tutorials on ChatGPT, Claude, Midjourney, and 50+ AI tools. New tutorials added every week.
7-day free trial. Cancel anytime.
Related guides