Prompt Library

ChatGPT Prompts for Spreadsheets (Excel + Sheets Universal)

20 copy-paste prompts

20 copy-paste ChatGPT prompts for spreadsheets: data structures, formula patterns, cleaning, analysis, dashboards, and the workflows that work in Excel + Google Sheets equally.

Data Structures

4 prompts

Spreadsheet Schema Design

1/20

Design spreadsheet structure for [use case]. Output: tabs/sheets purpose, columns per sheet, naming convention, primary keys, relationships between sheets. Structure upfront > restructure later.

Designs spreadsheet schemas.

💡

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/20

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.

Decides spreadsheet vs database.

💡

Pro tip: Spreadsheets break at scale. 50K rows + 10 users + complex queries = database needed. Forced spreadsheet at scale = chaos + corruption.

Tidy Data Principles

3/20

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.

Tidies data structures.

💡

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/20

Wide vs long format for [data]. Output: per use case, which works (wide for input, long for analysis), conversion methods. Convert programmatically vs manually.

Decides wide vs long format.

💡

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.

Start 7-Day Free Trial

Formulas + Calculations

4 prompts

Formula Library

5/20

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.

Builds formula library.

💡

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/20

Best lookup formula for [scenario]. Output: XLOOKUP (modern, simpler) vs VLOOKUP (legacy, common) vs INDEX-MATCH (flexible, faster), recommendations. XLOOKUP increasingly default.

Selects lookup formulas.

💡

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/20

Array formulas for [calculation]. Output: dynamic arrays (modern Excel/Sheets), spill behavior, common array formula patterns (FILTER, SORT, UNIQUE, SEQUENCE). Array formulas = power.

Uses array formulas.

💡

Pro tip: Dynamic arrays (Excel 365, modern Sheets) = transform spreadsheet thinking. FILTER + SORT + UNIQUE = mini queries. Underused; the productivity unlock.

Volatile Functions Audit

8/20

[Paste spreadsheet]. Audit volatile functions: NOW, TODAY, RAND, OFFSET, INDIRECT (recalculates on every change). Replace if possible; impacts performance. Big spreadsheets = matters.

Audits volatile functions.

💡

Pro tip: Volatile functions = recalc constantly = slow spreadsheet. Replace with non-volatile alternatives (XLOOKUP > INDIRECT). Performance lift on large sheets.

Cleaning + Validation

4 prompts

Data Cleaning Workflow

9/20

[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.

Plans data cleaning.

💡

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/20

Data validation for [columns]. Output: per column, validation type (list, number range, date range, regex, custom formula), error message. Prevents bad data entry.

Sets up validation.

💡

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/20

Find duplicates in [data]. Methods: built-in (Excel Remove Duplicates, Sheets unique formula), conditional formatting, COUNTIF flag. Output: approach + how to clean.

Detects duplicates.

💡

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/20

[Data]. Detect outliers: standard deviation method, percentile method, IQR method, visual via charts. Output: identification + decision (real outlier? data error? exclude?). Outliers can mislead.

Detects outliers.

💡

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.

Try AI Academy Free

Analysis + Reporting

4 prompts

Analysis from Question

13/20

Question: [describe]. Help structure spreadsheet analysis. Output: data needed, calculations required, visualization, summary, decision. Analysis-as-question > data-dump.

Structures spreadsheet analysis.

💡

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/20

Build pivot table for [question]. Output: rows, columns, values, filters, calculated fields. Pivot = aggregate analysis fast.

Builds pivot tables.

💡

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/20

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.

Builds spreadsheet dashboards.

💡

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/20

Variance analysis: [actual] vs [target/budget/prior]. Output: variance per line ($ + %), commentary on top variances, drilldown if available, summary for executives. Variance > 5% explained.

Builds variance analyses.

💡

Pro tip: Variance reports without commentary = numbers. With commentary on biggest variances = actionable. Story > data.

Frequently Asked Questions

Excel: deeper features (Power Query, Power Pivot, more formulas, faster on large data), enterprise standard. Sheets: real-time collab, browser-based, simpler. Most analysts use both; depends on team standard.

Often yes; sometimes hallucinates function names. Always test in spreadsheet before relying. AI as drafting tool; you verify in cell.

Tidy data principles. Most users build "wide" data with multi-headers + merged cells. Tidy = analyze-able. The discipline of clean structure pays compounding analysis value.

>50K rows, multi-user complex updates, frequent corruption from concurrent edits, performance unacceptable. Move to: Airtable (simple), database (real scale), BI tool (analytics). Spreadsheets break at scale.

Mostly. Most formulas work both. Differences: array behavior, some unique functions per platform (Sheets QUERY, Excel LET). 95% portable; 5% diverges.

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.

Start 7-Day Free Trial Explore AI for Data Analysts

7-day free trial. Cancel anytime.

Browse All Prompt Collections