Prompt Library

ChatGPT Prompts for Spreadsheet Formulas

20 copy-paste prompts

20 copy-paste ChatGPT prompts for spreadsheet formulas: lookups, aggregations, text manipulation, date math, logical conditions, array formulas. Examples that work in Excel + Sheets equally.

Lookup Formulas

4 prompts

XLOOKUP from Description

1/20

XLOOKUP for [scenario]. Output: formula syntax, parameter explanation, when XLOOKUP > VLOOKUP, error handling (default value), multiple-criteria variation. Modern lookup default.

Writes XLOOKUP formulas.

💡

Pro tip: XLOOKUP = modern. Simpler than VLOOKUP, more powerful. Built-in default value (vs IFERROR wrap), can return arrays, exact match by default. Use for new work.

INDEX-MATCH

2/20

INDEX-MATCH for [scenario]. Output: formula, why use over VLOOKUP (left-to-right lookup, faster, more flexible), nested for multiple criteria. Power-user lookup.

Writes INDEX-MATCH formulas.

💡

Pro tip: INDEX-MATCH = flexible (look any direction), faster than VLOOKUP on large data. Common power-user pattern. Less needed in Excel 365 (XLOOKUP), still useful in old Excel + Sheets.

Multi-Criteria Lookup

3/20

Lookup with multiple criteria. Methods: XLOOKUP with concatenated key, INDEX-MATCH-MATCH, SUMIFS, FILTER. Output: best per situation. Multi-criteria = common need.

Writes multi-criteria lookups.

💡

Pro tip: Multi-criteria via concatenated key = simple but ugly. INDEX-MATCH-MATCH = elegant. SUMIFS = if returning a sum. FILTER = if multiple results possible.

Approximate Match

4/20

Approximate match lookup for [scenario — e.g., score → grade, revenue → tier]. Output: VLOOKUP/XLOOKUP with approximate, sorted reference table requirement, edge cases. Tier lookups common.

Builds approximate match lookups.

💡

Pro tip: Approximate match = falls between values. Reference table sorted ascending. Common for: tier assignments, grade brackets, tax brackets. Wrong sort = wrong answer.

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

Aggregations

4 prompts

SUMIF + SUMIFS

5/20

SUMIF / SUMIFS for [scenario]. Output: single criteria (SUMIF), multiple criteria (SUMIFS), with wildcards, with comparison operators. Most-used aggregation.

Writes SUMIF formulas.

💡

Pro tip: SUMIFS = most common conditional sum. Pattern: SUMIFS(sum_range, criteria_range1, criteria1, ...). Up to 127 criteria pairs. Always SUMIFS for multi-criteria; never nest IF inside SUM.

COUNTIF + COUNTIFS

6/20

COUNTIF / COUNTIFS for [scenario]. Output: counting matching cells, with conditions, with wildcards (* and ?), with date ranges. Counting = analytical baseline.

Writes COUNTIF formulas.

💡

Pro tip: COUNTIFS for multi-criteria. Wildcards: * (any chars), ? (single char). Date ranges via comparison operators (>=, <=). Master COUNTIFS = baseline analytical skill.

AVERAGEIF + AVERAGEIFS

7/20

AVERAGEIF / AVERAGEIFS for [scenario]. Output: averages with conditions, ignoring zeros, weighted average via SUMPRODUCT. Conditional averages everywhere.

Writes AVERAGEIF formulas.

💡

Pro tip: AVERAGEIFS for multi-criteria. To ignore zeros: AVERAGEIFS(range, range, "<>0"). Weighted average = SUMPRODUCT(values, weights) / SUM(weights). Different math.

SUMPRODUCT Power

8/20

SUMPRODUCT for [scenario]. Output: weighted sums, conditional sums (alternative to SUMIFS in old Excel), array math. Underused power formula.

Uses SUMPRODUCT.

💡

Pro tip: SUMPRODUCT = workhorse. Multiplies arrays then sums. Used for: weighted averages, conditional sums (legacy Excel), complex array math. Modern array formulas often replace.

Text + Date

4 prompts

Text Manipulation

9/20

Text manipulation: [scenario]. Output: LEFT/RIGHT/MID for parts, FIND/SEARCH for position, SUBSTITUTE for replace, CONCAT/TEXTJOIN for combining, TRIM for whitespace, UPPER/LOWER/PROPER for case.

Manipulates text.

💡

Pro tip: Text functions = data cleaning toolkit. TRIM removes leading/trailing whitespace (most data has it). PROPER for title case. SUBSTITUTE for specific replacements. Combine for transformation pipelines.

Split + Join Strings

10/20

Split text into columns + join columns into text. Output: TEXTSPLIT (modern Excel), TEXTJOIN, TEXTAFTER/BEFORE, alternatives in older versions. Common transformation.

Splits + joins strings.

💡

Pro tip: Modern Excel TEXTSPLIT = elegant. Old versions: combination of FIND + LEFT/MID/RIGHT (ugly). TEXTJOIN = combine with delimiter, ignore blanks option. Both extremely useful.

Date Math

11/20

Date math: [scenario — days between, age, business days, fiscal year]. Output: DATEDIF (legacy), NETWORKDAYS, EOMONTH, EDATE, formulas for fiscal year. Date arithmetic common.

Writes date math.

💡

Pro tip: NETWORKDAYS = business days excluding weekends + holidays. EOMONTH/EDATE = move to month boundaries. Date arithmetic everywhere; master these patterns.

Date Formatting

12/20

Format dates for [need]. Output: TEXT function for custom format strings, common patterns ("yyyy-mm-dd", "MMM dd"), display vs underlying value distinction. Format != value.

Formats dates.

💡

Pro tip: Date display ≠ underlying value. Format changes display only. TEXT function returns formatted string (different from formatted cell). Easy confusion.

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

Logical + Array

4 prompts

IF + Nested IF + IFS

13/20

Conditional logic. Output: simple IF, nested IF, IFS (cleaner for many conditions), SWITCH for value matching. Match tool to complexity.

Writes conditional logic.

💡

Pro tip: Simple IF for binary. Nested IF for 2-3 conditions. IFS for many conditions (cleaner than nested). SWITCH for "if X = a, return X; if X = b, return Y" pattern.

IFERROR + IFNA

14/20

Error handling: IFERROR vs IFNA. Output: IFERROR catches all errors (#N/A, #DIV/0!, etc.), IFNA only #N/A (more specific). Wrap lookups for clean output.

Handles formula errors.

💡

Pro tip: IFERROR catches everything (sometimes hides real errors). IFNA only catches #N/A (more specific). For lookups, IFNA preferred (don't hide #DIV/0! bugs).

Array Formulas (Modern)

15/20

Array formula patterns: FILTER, SORT, UNIQUE, SEQUENCE, BYROW, BYCOL, MAP, REDUCE, LAMBDA. Output: examples per function, when each useful. Modern arrays = power.

Uses modern arrays.

💡

Pro tip: FILTER + SORT + UNIQUE = mini-queries inside spreadsheet. LAMBDA + MAP/REDUCE = define reusable functions. Modern Excel/Sheets array tools = transformative.

LET for Reusability

16/20

LET function for [complex formula]. Output: define variables once, reuse, more readable, faster (calculate once). LET = better complex formulas.

Uses LET function.

💡

Pro tip: LET names intermediate calculations. Like JS variables in formula. Long formulas with LET = readable + faster. Modern Excel/Sheets feature; underused.

Frequently Asked Questions

XLOOKUP (modern Excel/Sheets) or VLOOKUP (older). Lookups = #1 used formula category. Master one lookup function = 30% of typical formula needs covered.

Common: wrong cell references (use $ for absolute), text vs number mismatch (LEN reveals), trailing whitespace (TRIM), date stored as text. Most "formula bugs" = data quality issues.

Often yes; sometimes hallucinates. Always test in spreadsheet. Function name + parameter count = check. AI as drafting; spreadsheet as truth.

Use LET to name intermediate calculations. Break into helper columns vs one mega-formula. Comment in adjacent cell. Future-you debugging thanks past-you.

Formula for: simple per-row calculations, references in dashboards. Power Query for: data transformation pipelines, repeatable cleanup, large data. Both valuable; different tools.

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