ChatGPT Prompts for Spreadsheet Formulas
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.
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.
Lookup Formulas
4 promptsXLOOKUP from Description
1/16✨ What it does
ChatGPT writes an XLOOKUP for [scenario] with syntax, what each parameter does, when it beats VLOOKUP, a default for errors, and a multiple-criteria variant. Describe the scenario, then paste the formula into your sheet and test a missing-key row.
XLOOKUP for [scenario]. Output: formula syntax, parameter explanation, when XLOOKUP > VLOOKUP, error handling (default value), multiple-criteria variation. Modern lookup default.
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/16✨ What it does
ChatGPT writes INDEX-MATCH for [scenario], explains why it beats VLOOKUP (left lookup, speed, flexibility), and nests it for multiple criteria. Fill the scenario, then try a lookup to the left of the key to confirm it works on your data.
INDEX-MATCH for [scenario]. Output: formula, why use over VLOOKUP (left-to-right lookup, faster, more flexible), nested for multiple criteria. Power-user lookup.
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/16✨ What it does
ChatGPT compares multi-criteria lookups (XLOOKUP with a concatenated key, INDEX-MATCH-MATCH, SUMIFS, FILTER) and picks the best for your situation. Choose the method it recommends, then test it on two criteria you already know.
Lookup with multiple criteria. Methods: XLOOKUP with concatenated key, INDEX-MATCH-MATCH, SUMIFS, FILTER. Output: best per situation. Multi-criteria = common need.
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/16✨ What it does
ChatGPT builds an approximate-match lookup for [scenario] such as score to grade or revenue to tier, with VLOOKUP or XLOOKUP approximate mode, a sorted reference table, and edge cases. Fill the scenario, then sort your tier table before you rely on the match.
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.
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.
Aggregations
4 promptsSUMIF + SUMIFS
5/16✨ What it does
ChatGPT writes SUMIF and SUMIFS for [scenario], covering one criterion, several criteria, wildcards, and comparison operators. State the scenario, then check the total against a filtered sum on your sheet.
SUMIF / SUMIFS for [scenario]. Output: single criteria (SUMIF), multiple criteria (SUMIFS), with wildcards, with comparison operators. Most-used aggregation.
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/16✨ What it does
ChatGPT writes COUNTIF and COUNTIFS for [scenario], including conditions, asterisk and question-mark wildcards, and date ranges. Describe what you are counting, then compare the result to a manual count on a sample.
COUNTIF / COUNTIFS for [scenario]. Output: counting matching cells, with conditions, with wildcards (* and ?), with date ranges. Counting = analytical baseline.
Pro tip: COUNTIFS for multi-criteria. Wildcards: * (any chars), ? (single char). Date ranges via comparison operators (>=, <=). Master COUNTIFS = baseline analytical skill.
AVERAGEIF + AVERAGEIFS
7/16✨ What it does
ChatGPT writes AVERAGEIF and AVERAGEIFS for [scenario], including ignoring zeros and a weighted average via SUMPRODUCT. Name the scenario, then decide whether zeros should be ignored before you paste the formula.
AVERAGEIF / AVERAGEIFS for [scenario]. Output: averages with conditions, ignoring zeros, weighted average via SUMPRODUCT. Conditional averages everywhere.
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/16✨ What it does
ChatGPT writes SUMPRODUCT for [scenario] for weighted sums, conditional sums in older Excel, and array math. Fill the scenario, then compare the result to a SUMIFS on a modern sheet if you have one.
SUMPRODUCT for [scenario]. Output: weighted sums, conditional sums (alternative to SUMIFS in old Excel), array math. Underused power formula.
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 promptsText Manipulation
9/16✨ What it does
ChatGPT writes text formulas for [scenario] using LEFT, RIGHT, MID, FIND, SEARCH, SUBSTITUTE, CONCAT, TEXTJOIN, TRIM, and UPPER, LOWER, or PROPER. Describe the split or cleanup, then apply the formula to a copy of your column.
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.
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/16✨ What it does
ChatGPT shows how to split text into columns and join columns into text with TEXTSPLIT, TEXTJOIN, TEXTAFTER, TEXTBEFORE, and older-Excel fallbacks. Pick the modern function if you have it, then keep a fallback for anyone on an older build.
Split text into columns + join columns into text. Output: TEXTSPLIT (modern Excel), TEXTJOIN, TEXTAFTER/BEFORE, alternatives in older versions. Common transformation.
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/16✨ What it does
ChatGPT writes date math for [scenario] such as days between, age, business days, or fiscal year, using DATEDIF, NETWORKDAYS, EOMONTH, EDATE, and fiscal-year formulas. Fill the scenario, then verify one result against your calendar.
Date math: [scenario — days between, age, business days, fiscal year]. Output: DATEDIF (legacy), NETWORKDAYS, EOMONTH, EDATE, formulas for fiscal year. Date arithmetic common.
Pro tip: NETWORKDAYS = business days excluding weekends + holidays. EOMONTH/EDATE = move to month boundaries. Date arithmetic everywhere; master these patterns.
Date Formatting
12/16✨ What it does
ChatGPT formats dates for [need] with TEXT custom strings such as yyyy-mm-dd or MMM dd, and explains display versus the underlying value. Name the need, then check that your sorting still uses the real date, not the text.
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.
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.
Logical + Array
4 promptsIF + Nested IF + IFS
13/16✨ What it does
ChatGPT writes conditional logic with simple IF, nested IF, IFS for many conditions, and SWITCH for value matching, and tells you which to use. Pick IFS if you have more than two branches, then test every branch with a sample.
Conditional logic. Output: simple IF, nested IF, IFS (cleaner for many conditions), SWITCH for value matching. Match tool to complexity.
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/16✨ What it does
ChatGPT wraps errors with IFERROR versus IFNA: IFERROR catches every error, IFNA catches only #N/A, so lookups stay clean without hiding every problem. Use IFNA on lookups, then leave other errors visible so you can fix them.
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.
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/16✨ What it does
ChatGPT writes modern array patterns with FILTER, SORT, UNIQUE, SEQUENCE, BYROW, BYCOL, MAP, REDUCE, and LAMBDA, plus when each is useful. Start with FILTER or UNIQUE, then add LAMBDA only if a simpler function cannot do your job.
Array formula patterns: FILTER, SORT, UNIQUE, SEQUENCE, BYROW, BYCOL, MAP, REDUCE, LAMBDA. Output: examples per function, when each useful. Modern arrays = power.
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/16✨ What it does
ChatGPT rewrites [complex formula] with LET so you define each name once, reuse it, and calculate it once for a more readable, faster formula. Paste the long formula, then keep the LET version only after a few cells match the old result.
LET function for [complex formula]. Output: define variables once, reuse, more readable, faster (calculate once). LET = better complex formulas.
Pro tip: LET names intermediate calculations. Like JS variables in formula. Long formulas with LET = readable + faster. Modern Excel/Sheets feature; underused.
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.