Excel Problems Claude Breaks Down Step-by-Step
20 Claude prompts for formulas, VBA macros, data transformation pipelines, and complex spreadsheet problems where Claude's structured reasoning shines.
In short: This page contains 20 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. Drafted with AI writing assistance and checked against our editorial policy.
Formulas
5 promptsComplex Formula Builder
1/20✨ What it does
Claude writes an Excel formula for [describe calculation] using LET, with a step-by-step read, edge-case handling, and a version note for [365/2021/older]. Describe the columns, then paste the formula and test it on a blank and an error row in your sheet.
<task>Write an Excel formula for [describe calculation]</task> <data_structure>[describe columns, sample rows]</data_structure> <edge_cases>[describe blanks, errors, conditions]</edge_cases> <excel_version>[365/2021/older]</excel_version> <output> 1. Formula with LET for readability 2. Step-by-step explanation 3. Edge case handling 4. Older Excel alternative if needed 5. Performance notes for large data </output>
Pro tip: Claude's step-by-step reasoning excels at multi-condition formula logic. For nested IFs, lookup chains, and array formulas, Claude's explanation of each part helps you debug and adapt.
Formula Debugger
2/20✨ What it does
Claude debugs the formula you [paste], naming the root cause, a corrected version, why it failed, a simpler option, and how to harden it. Add [what should happen] and [paste rows], then replace the broken cell once your sample matches.
<task>Debug this Excel formula</task> <formula>[paste]</formula> <error>[describe]</error> <expected>[what should happen]</expected> <sample_data>[paste rows]</sample_data> <output> 1. Root cause of error 2. Corrected formula 3. Why original failed 4. Simpler alternative if applicable 5. How to bulletproof </output>
Pro tip: Excel errors usually stem from type mismatches or missing lookup values. Claude is patient at walking through formulas cell-by-cell to find the issue.
LET Refactor
3/20✨ What it does
Claude refactors the formula you [paste] into a LET version with named parts, a plain-English read, and any speed trade-off. Drop it in a spare cell first, then keep it if a teammate can follow your names.
<task>Refactor this formula using LET for readability</task> <original>[paste]</original> <output> - LET formula with meaningful variable names - Plain-English explanation - Which parts now maintainable vs before - Any performance trade-offs </output>
Pro tip: LET transforms unreadable formulas into self-documenting code. Once you use LET, you'll never go back to nested parentheses hell.
Dynamic Array Formula
4/20✨ What it does
Claude writes a dynamic array formula for [describe shape and content] using FILTER, SORT, UNIQUE, SEQUENCE, LET, or LAMBDA, plus a non-array fallback. Map [describe ranges], then watch how it spills before you copy it across the book.
<task>Write dynamic array formula (Excel 365+)</task> <desired_output>[describe shape and content]</desired_output> <inputs>[describe ranges]</inputs> <output> 1. Formula using FILTER/SORT/UNIQUE/SEQUENCE/LET/LAMBDA 2. How it spills 3. Gotchas 4. Non-array fallback for older Excel </output>
Pro tip: Dynamic arrays are the biggest Excel upgrade in 20 years. FILTER + SORT + UNIQUE replaces most VLOOKUP + helper column combos. If you're on 365, stop using VLOOKUP.
Array Formula to LAMBDA
5/20✨ What it does
Claude converts the array formula you [paste] into a reusable LAMBDA with named parameters, Name Manager steps, and usage examples. Save it under a clear name, then call it from a cell instead of your long original.
<task>Convert array formula to reusable LAMBDA</task> <original>[paste]</original> <output> 1. LAMBDA with named parameters 2. How to save to Name Manager 3. Usage examples 4. When LAMBDA beats helper columns </output>
Pro tip: LAMBDA lets you build custom reusable functions in Excel — game-changer for power users. Claude can convert complex formulas into clean LAMBDAs that your whole team can reuse.
VBA & Macros
5 promptsVBA Macro Generator
6/20✨ What it does
Claude writes a VBA macro that does what you [describe], with comments, On Error handling, ScreenUpdating, qualified ranges, and install steps. Describe the workbook, then run the test scenario on a copy before your live file.
<task>Write a VBA macro that [describe]</task> <workbook>[describe structure]</workbook> <output> 1. Complete VBA with comments 2. Error handling (On Error) 3. Application.ScreenUpdating off/on 4. Qualified references (Sheets("X").Range) 5. Install + trigger instructions 6. Test scenario </output>
Pro tip: Claude writes defensive VBA by default — error handling, screen updating, qualified references. It treats VBA like production code, which prevents the "works on my machine" failures.
VBA Debugger
7/20✨ What it does
Claude debugs the VBA you [paste], with the root cause, corrected code, why it failed, error handling, and speed fixes. Recreate the [describe] error on a copy, then run the corrected macro there before your production book.
<task>Debug this VBA</task> <code>[paste]</code> <error>[describe]</error> <output> 1. Root cause 2. Corrected code with comments 3. Why original failed 4. Added error handling 5. Performance improvements </output>
Pro tip: VBA bugs usually come from implicit activation, unqualified references, or missing .Value. Claude catches these patterns reliably.
UserForm Designer
8/20✨ What it does
Claude designs a UserForm for [input need] with controls, initialize code, per-field validation, submit, and cancel from the fields you [list]. Build the form in the VBA editor, then click Cancel once to confirm your close path.
<task>Design a UserForm for [input need]</task> <fields>[list]</fields> <output> 1. Control list (type + name) 2. Initialize code 3. Validation per field 4. Submit button code 5. Cancel close code </output>
Pro tip: UserForms transform Excel from spreadsheet into app. 30-minute UserForm saves hours of data-quality cleanup every month.
VBA to Office Scripts
9/20✨ What it does
Claude converts the VBA you [paste] into an Office Script in TypeScript, noting API gaps, what will not translate, and a Power Automate path. Run the script on a test workbook, then automate only the parts that translated for your flow.
<task>Convert VBA to Office Scripts</task> <vba>[paste]</vba> <output> 1. Equivalent TypeScript Office Script 2. API differences to know 3. What won't translate 1:1 4. Power Automate integration </output>
Pro tip: Office Scripts are Excel's future for web/Teams automation. VBA still wins for complex UserForms, but for cross-platform, Office Scripts win. Plan your migration now.
Macro Portability Check
10/20✨ What it does
Claude reviews the macro you [paste] for hardcoded paths, version locks, risky calls, packaging, and an end-user README. Parameterize the paths it flags, then package it before you send the file around.
<task>Review macro for portability and security</task> <code>[paste]</code> <output> 1. Hardcoded paths/sheets to parameterize 2. Version dependencies 3. Security concerns (SendKeys, Shell, file writes) 4. Packaging recommendation 5. README for end-users </output>
Pro tip: Macros that "work on my machine" often fail elsewhere. 10 minutes of portability review before distribution saves 10 support emails later.
Data Transformation
5 promptsData Cleaning Pipeline
11/20✨ What it does
Claude builds a cleaning pipeline for [describe or sample] with ordered steps, formula versus Power Query versus VBA, checks between steps, and a monthly refresh. Start with [describe quality problems], then refresh once on last month's file on your drive.
<task>Build a data cleaning pipeline</task> <raw_data>[describe or sample]</raw_data> <issues>[describe quality problems]</issues> <output> 1. Cleaning steps in order 2. Method per step (formula / Power Query / VBA) 3. Validation between steps 4. Reusable monthly refresh </output>
Pro tip: Clean data once, analyze many times. If you re-clean the same way every month, move to Power Query — 1-click refresh beats 15-step manual cleanup.
Pivot Table Designer
12/20✨ What it does
Claude designs a pivot for [analysis] with field placement, value settings, calculated fields, formatting, and slicers from the source you [describe]. Build the pivot on a copy, then add only the slicers your insight needs.
<task>Design pivot table for [analysis]</task> <source_data>[describe]</source_data> <insight_needed>[describe]</insight_needed> <output> 1. Field placement (Rows, Columns, Values, Filters) 2. Value field settings (Sum, %, Running Total, etc.) 3. Calculated fields if needed 4. Conditional formatting rules 5. Slicer dashboard setup </output>
Pro tip: "Show Values As" options (% of column, Running Total, Difference From) unlock analysis that usually requires formulas. 5 minutes learning these unlocks huge leverage.
Outlier Detection
13/20✨ What it does
Claude picks IQR, Z-score, or MAD for the columns you [describe columns], writes flagging formulas, and suggests investigate, remove, or winsorize plus a chart. Flag the outliers first, then decide a fate only after you look at the rows.
<task>Detect outliers in this data</task> <data>[describe columns]</data> <output> 1. Recommended method (IQR / Z-score / MAD) based on distribution 2. Formulas to flag outliers 3. Action plan (investigate / remove / winsorize) 4. Visualization setup </output>
Pro tip: Outliers aren't always errors. Always investigate before removing — a "weird" customer might be your biggest opportunity hiding in the noise.
Data Validation Setup
14/20✨ What it does
Claude sets validation on [describe what each should contain], with custom messages, dependent dropdowns, a cleanup for bad existing cells, and sheet protection. Apply the rules to a copy, then protect the sheet after your cleanup.
<task>Set up data validation rules</task> <columns>[describe what each should contain]</columns> <output> 1. Validation settings per column 2. Custom input + error messages 3. Dependent drop-downs if needed 4. Cleanup for existing bad data 5. Protection strategy </output>
Pro tip: Data validation prevents 80% of quality issues before they happen. 15 minutes of setup saves hours of cleanup. Bad data in = bad analysis out.
Large Dataset Performance
15/20✨ What it does
Claude diagnoses a slow workbook of [rows × cols] and ranks volatile replacements, array versus drag, Power Query, and when to leave Excel. Trigger the [describe] symptoms again, then apply one change at a time and retime your file.
<task>Optimize slow Excel workbook</task> <size>[rows × cols]</size> <symptoms>[describe]</symptoms> <output> 1. Slowest elements identified 2. Volatile formula replacements 3. ARRAYFORMULA vs dragged 4. When to move to Power Query 5. When to move to BigQuery Connected </output>
Pro tip: Excel's practical limit is around 5M cells. Past that, fighting is pointless — move to Power Query or BigQuery Connected Sheets. Keep UX; gain database performance.
These prompts give you the what. Tutorials give you the why.
Learn when to use extended thinking, how to build Claude Projects, and workflows that compound. The library keeps growing.
Dashboards & Reporting
5 promptsDashboard Blueprint
16/20✨ What it does
Claude designs a one-screen executive dashboard from the purpose and data you [describe], with 5-7 KPIs, charts, slicers, and a reading order. Build the top KPI first, then stop adding charts once your story is clear.
<task>Design an executive dashboard</task> <purpose>[describe]</purpose> <data_source>[describe]</data_source> <output> 1. Top 5-7 KPIs 2. Chart per KPI 3. Layout (single screen) 4. Slicer/filter design 5. Color + hierarchy 6. "Reading" narrative flow </output>
Pro tip: Great dashboards answer "is everything okay?" in 5 seconds. If someone has to hunt, the dashboard failed. Start with the decision it enables, work backward.
Monthly Report Builder
17/20✨ What it does
Claude builds a monthly report from the data and audience you [describe], with auto-updating sections, variances, an exec summary, and a sub-10-minute refresh list. Run the checklist on last month, then send the summary page to your reader.
<task>Build recurring monthly report</task> <data>[describe]</data> <audience>[describe]</audience> <output> 1. Structure + sections 2. Auto-updating formulas / pivots 3. Variance analysis (vs last month, target, YoY) 4. Executive summary template 5. Sub-10-min monthly refresh checklist </output>
Pro tip: Monthly reports should take 10 min to refresh, not 3 hours. If you're retyping numbers, you've built a report, not a system. Automate once, benefit forever.
Chart Cleanup Guide
18/20✨ What it does
Claude designs charts for [data + insight] with the best type, which Excel defaults to strip, one-color highlighting, and annotations. Build the recommended chart, then gray every series that is not your point.
<task>Design charts for [data + insight]</task> <data>[describe]</data> <output> 1. Best chart type + reasoning 2. Elements to remove from Excel defaults 3. Color highlighting strategy (one series color, rest gray) 4. Annotations for key insights 5. Alternative chart types considered </output>
Pro tip: Excel defaults are visual noise. Delete gridlines, declutter, pick one highlight color, gray everything else. 10 min of cleanup beats fancy chart types.
What-If Scenario Model
19/20✨ What it does
Claude builds a what-if model for the decision you [describe] using [list with ranges], with inputs, logic, a scenario table, sensitivity, and a visual. Change one input at a time, then take the visual into your decision meeting.
<task>Build a what-if scenario model</task> <decision>[describe]</decision> <variables>[list with ranges]</variables> <output> 1. Structured input section 2. Calculation logic 3. Scenario / Data Table 4. Sensitivity analysis 5. Visual summary </output>
Pro tip: Sensitivity analysis reveals which variables actually matter. Most scenarios test only 3-4 cases — sensitivity analysis tests all of them and shows where to focus.
KPI Tracking System
20/20✨ What it does
Claude designs a KPI tracker from the metrics you [list], with a validated input sheet, red/yellow/green thresholds, sparklines, a summary, archive, and alerts. Enter this week's numbers, then archive last period before you overwrite it.
<task>Design a KPI tracking system</task> <kpis>[list]</kpis> <output> 1. Input worksheet with validation 2. Target + threshold logic (red/yellow/green) 3. Trend sparklines 4. Summary dashboard 5. Historical archive 6. Threshold alerts </output>
Pro tip: KPIs shape behavior. Track vanity metrics, chase vanity. Track leading indicators, build value. Pick KPIs that drive the right behavior.
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 hands-on tutorials on ChatGPT, Claude, Midjourney, and many other AI tools. New tutorials are added regularly.
7-day free trial. Cancel anytime.