30 Claude Prompts That Write Excel Formulas

You are an Excel logic-formula expert. <context> I need to assign a label based on which range a number falls in (like a grade, priority, or status), written cleanly instead of deeply nested IFs. </context> <inputs> - Cell to evaluate: [E.G. score in A2] - The bands and labels: [E.G. >=90 "A", >=80 "B", >=70 "C", ELSE "F"] - Tool: [EXCEL 365 / EXCEL 2019 / GOOGLE SHEETS] </inputs> <task> Write the formula using IFS with a final TRUE catch-all (or nested IF if the tool lacks IFS). Order the conditions correctly so higher bands are tested first. </task> <constraints> - Include a default/else result. - Valid syntax for the named tool; readable structure. </constraints> <format> Return the formula in a code block, then explain why the condition order matters and how the catch-all works. </format>

You are an Excel expert who writes precise conditional sums. <context> I need to add up a column but only for rows that meet several conditions at once, including a date range. </context> <inputs> - Column to sum: [E.G. Amount in Data!D:D] - Condition 1 (column + test): [E.G. Region col A = cell G1] - Condition 2 (column + test): [E.G. Status col C = "Paid"] - Date filter (column + range): [E.G. Date col B between H1 and H2] - Tool: [EXCEL 365 / EXCEL 2019 / GOOGLE SHEETS] </inputs> <task> Write one SUMIFS formula covering all conditions. Express the date range as two criteria (">="&start and "<="&end). Reference cells for the criteria so they are editable. </task> <constraints> - Criteria ranges must be the same size as the sum range. - Valid syntax for the named tool; use cell references, not hard-coded values, where I gave a cell. </constraints> <format> Return the formula in a code block, then explain the criteria pairing and the ">="&cell date-range trick. </format>

You are an Excel expert who builds dashboard count metrics. <context> I need to count how many rows meet a set of conditions — a KPI like "open tickets this week from tier-1 accounts". </context> <inputs> - Condition 1 (column + test): [E.G. Status col C = "Open"] - Condition 2 (column + test): [E.G. Tier col D = 1] - Optional date/number range (column + bounds): [E.G. Created col B >= this Monday] - Tool: [EXCEL 365 / EXCEL 2019 / GOOGLE SHEETS] </inputs> <task> Write one COUNTIFS formula for all conditions. Show how to express a "blank", "not blank", or "contains text" criterion if I need one. </task> <constraints> - All criteria ranges the same length; reference cells for editable thresholds. - Valid syntax for the named tool. </constraints> <format> Return the formula in a code block, then list the criteria-operator syntax ("<>", "*text*", ">="&cell) I can reuse. </format>

You are an Excel expert who writes accurate conditional averages. <context> I need an average of a column filtered by conditions, and I want to exclude blanks and zeros so they do not drag the result down. </context> <inputs> - Column to average: [E.G. Score in Data!E:E] - Condition(s): [E.G. Team col A = cell G1] - Exclude zeros?: [YES / NO] - Exclude blanks?: [YES / NO] - Tool: [EXCEL 365 / EXCEL 2019 / GOOGLE SHEETS] </inputs> <task> Write one AVERAGEIFS formula that applies the conditions plus "<>0" and/or "<>" criteria on the value column as requested. Ensure it returns a clean result, not a divide-by-zero error, when nothing matches. </task> <constraints> - Wrap in IFERROR only for the no-match case, returning a chosen value. - Valid syntax for the named tool. </constraints> <format> Return the formula in a code block, then explain how the "<>0" criterion is applied to the average range itself. </format>

You are an Excel expert in SUMPRODUCT for weighted and multi-condition math. <context> I need a calculation that SUMIFS cannot do easily — like a weighted average, or a conditional sum where I multiply two columns together, filtered by criteria. </context> <inputs> - What I am computing: [E.G. WEIGHTED AVG SCORE, OR REVENUE = QTY * PRICE FOR A REGION] - Column A (e.g. quantity/values): [E.G. Data!C:C] - Column B (e.g. weights/price): [E.G. Data!D:D] - Condition(s): [E.G. Region col A = cell G1] - Tool: [EXCEL 365 / EXCEL 2019 / GOOGLE SHEETS] </inputs> <task> Write one SUMPRODUCT formula that multiplies the columns, applies the conditions as boolean arrays (col=criteria), and — for a weighted average — divides by the summed weights. </task> <constraints> - All arrays the same length; no full-column ranges that would slow it down (use bounded ranges). - Valid syntax for the named tool. </constraints> <format> Return the formula in a code block, then explain how the (condition) boolean arrays act as filters inside SUMPRODUCT. </format>

You are an Excel 365 and Google Sheets dynamic-array expert. <context> I want one formula that returns all rows from a table matching my conditions, spilling into a live range that updates automatically. </context> <inputs> - Data range to return: [E.G. Data!A2:E5000] - Condition 1: [E.G. Status col C = "Active"] - Condition 2 (optional): [E.G. Amount col D > 1000] - Sort the result by (optional): [E.G. Amount descending] - What to show if no matches: [E.G. "None"] - Tool: [EXCEL 365 / GOOGLE SHEETS] </inputs> <task> Write a FILTER formula combining the conditions with * (AND) or + (OR), the if-empty argument, and wrap in SORT if a sort is requested. Return only the columns I need if I specify. </task> <constraints> - Single spilling formula; no helper columns. - Valid syntax for the named tool; make clear the cell must have room to spill. </constraints> <format> Return the formula in a code block, then explain how * and + combine conditions and how the spill range behaves. </format>

You are a dynamic-array formula expert. <context> I need a de-duplicated list of values from a column, ideally with how many times each appears, as a live spilling result. </context> <inputs> - Source column: [E.G. Data!B2:B5000] - Want counts next to each unique value?: [YES / NO] - Sort order: [BY COUNT DESC / ALPHABETICAL / AS-IS] - Ignore blanks?: [YES / NO] - Tool: [EXCEL 365 / GOOGLE SHEETS] </inputs> <task> Write the formula. Use UNIQUE (wrapped in SORT if needed) for the list. If counts are wanted, produce a two-column spill using UNIQUE plus a COUNTIF mapped over the unique values (or a SORTBY on the pair). </task> <constraints> - Single spilling formula where possible. - Valid syntax for the named tool; handle blanks per my choice. </constraints> <format> Return the formula in a code block, then explain how the count column stays aligned with the unique list. </format>

You are a dynamic-array formula expert. <context> I want the top N items from a table by some value — like the 10 biggest deals — returned as a live, ranked, spilling range. </context> <inputs> - Data range: [E.G. Data!A2:D5000] - Rank by which column: [E.G. Amount col D, descending] - How many to return (N): [E.G. 10, or a cell] - Columns to show: [E.G. Name and Amount] - Optional pre-filter: [E.G. only Region = "EMEA" OR NONE] - Tool: [EXCEL 365 / GOOGLE SHEETS] </inputs> <task> Write the formula using SORT (or SORTBY) on the data, optionally wrapped in FILTER for the pre-filter, then TAKE the first N rows (Excel 365) or use an equivalent for Sheets. Return the requested columns. </task> <constraints> - Single spilling formula; ties should not drop rows unexpectedly. - Valid syntax for the named tool. </constraints> <format> Return the formula in a code block, then explain the SORT-then-TAKE chain and how to make N a cell reference. </format>

You are an Excel 365 and Google Sheets dynamic-array expert. <context> I have a column where each cell holds several values joined by a delimiter (like "red;green;blue") and I want them split across columns as a spilling result. </context> <inputs> - Cell / column to split: [E.G. A2 or A2:A100] - Delimiter(s): [E.G. ";" OR ", " OR MULTIPLE] - Split across columns, down rows, or both?: [COLUMNS / ROWS / BOTH] - Trim spaces around each piece?: [YES / NO] - Tool: [EXCEL 365 / GOOGLE SHEETS] </inputs> <task> Write the formula using TEXTSPLIT (Excel 365) with the column and/or row delimiter arguments, or SPLIT (Google Sheets). Handle multiple delimiters and empty segments as I specify. </task> <constraints> - Single spilling formula; make room to spill. - Valid syntax for the named tool. </constraints> <format> Return the formula in a code block, note the Sheets vs Excel function difference, then explain the delimiter arguments. </format>

You are an Excel expert who builds cumulative and running-total formulas. <context> I need a running total down a column (each row = sum of everything up to it), either as one spilling formula or a fill-down formula. </context> <inputs> - Value column: [E.G. B2:B500] - Running total starts at row: [E.G. row 2] - Reset the total by a group? (optional): [E.G. reset each new month in col A / NO] - Prefer one spilling formula or a drag-down one?: [SPILL / DRAG-DOWN] - Tool: [EXCEL 365 / EXCEL 2019 / GOOGLE SHEETS] </inputs> <task> Write the formula. For a spill in Excel 365 use SCAN or a SUMIF over an expanding range; for drag-down use SUM($B$2:B2) anchoring. If a group reset is requested, use SUMIFS keyed to the group. </task> <constraints> - The anchor must be absolute so the range expands correctly when filled. - Valid syntax for the named tool. </constraints> <format> Return the formula in a code block, then explain the expanding-range anchor or the SCAN accumulator. </format>

You are a financial-modeling expert who writes clean loan formulas. <context> I need to calculate the periodic payment on a loan, and optionally the interest and principal portions of a given payment, with formulas. </context> <inputs> - Annual interest rate: [E.G. cell B1 = 6%] - Loan term: [E.G. 5 years] - Payment frequency: [MONTHLY / QUARTERLY / ANNUAL] - Loan amount (principal): [E.G. cell B3 = 30000] - Also split interest vs principal for a period?: [YES / NO] - Tool: [EXCEL / GOOGLE SHEETS] </inputs> <task> Write the PMT formula with the rate and nper converted to the payment frequency (rate/12, term*12 for monthly). If requested, add IPMT and PPMT for a specific period number. Show payments as a positive number if I want. </task> <constraints> - Convert annual rate and term to the period consistently. - Valid syntax for the named tool; note the sign convention. </constraints> <format> Return each formula in a code block, then explain the rate/nper conversion and the sign convention. </format>

You are a corporate-finance modeling expert. <context> I have a series of cash flows and need to evaluate an investment with net present value and internal rate of return using formulas. </context> <inputs> - Initial investment (period 0): [E.G. cell B2 = -50000] - Future cash flows range: [E.G. B3:B10] - Discount rate: [E.G. cell B1 = 10%] - Are cash flows on regular periods or specific dates?: [REGULAR / DATED] - Tool: [EXCEL / GOOGLE SHEETS] </inputs> <task> Write NPV correctly (NPV discounts from period 1, so add the period-0 outflow outside the function) and IRR. If cash flows are dated, use XNPV and XIRR with the date range instead. </task> <constraints> - Do not include the period-0 flow inside NPV. - Valid syntax for the named tool. </constraints> <format> Return each formula in a code block, then explain the period-0 handling and when to use XNPV/XIRR. </format>

You are a personal-finance and investing formula expert. <context> I want to project what recurring contributions will grow to over time at a given return, with one formula. </context> <inputs> - Expected annual return: [E.G. cell B1 = 7%] - Contribution amount and frequency: [E.G. 500 monthly] - Number of years: [E.G. 20] - Starting balance (optional): [E.G. 5000 OR 0] - Contribute at start or end of period?: [BEGINNING / END] - Tool: [EXCEL / GOOGLE SHEETS] </inputs> <task> Write the FV formula with rate and nper converted to the contribution frequency, the payment as a negative outflow, the optional present value, and the correct type argument (0 or 1) for timing. </task> <constraints> - Convert rate and periods to match the contribution frequency. - Valid syntax for the named tool; explain the sign convention. </constraints> <format> Return the formula in a code block, then explain the type argument and how beginning-of-period timing changes the result. </format>

You are a financial-analysis formula expert. <context> I need the compound annual growth rate between a starting value and an ending value over a number of periods, plus optionally a simple period-over-period growth formula. </context> <inputs> - Starting value cell: [E.G. B2] - Ending value cell: [E.G. B10] - Number of years/periods: [E.G. 8, or count via dates] - Also want simple % change between two cells?: [YES / NO] - Output as percentage?: [YES] - Tool: [EXCEL / GOOGLE SHEETS] </inputs> <task> Write the CAGR formula ((end/start)^(1/periods)-1). If dates define the span, derive the year count with YEARFRAC. Add the simple growth formula (new-old)/old if requested. </task> <constraints> - Guard against a zero or negative starting value (wrap in IFERROR or note the limitation). - Valid syntax for the named tool; format as a percent. </constraints> <format> Return each formula in a code block, then explain the exponent (1/periods) and why CAGR differs from a simple average. </format>

You are an accounting-formula expert. <context> I need formulas to calculate asset depreciation — straight-line for a flat annual amount, or declining-balance for an accelerated schedule. </context> <inputs> - Asset cost cell: [E.G. B2] - Salvage value cell: [E.G. B3] - Useful life (years): [E.G. B4] - Method: [STRAIGHT-LINE / DECLINING BALANCE / DOUBLE-DECLINING] - Period to calculate (for DB): [E.G. year in C2] - Tool: [EXCEL / GOOGLE SHEETS] </inputs> <task> Write the formula for the chosen method: SLN for straight-line, DB or DDB for declining balance for the given period. Make it fill-down friendly so I can build a full schedule keyed to the period column. </task> <constraints> - Reference cost/salvage/life as absolute cells; period as relative. - Valid syntax for the named tool. </constraints> <format> Return the formula(s) in a code block, then explain how to fill it down across periods to form the full schedule. </format>