Claude Prompt Library

Excel Problems Claude Breaks Down Step-by-Step

20 copy-paste prompts

20 Claude prompts for formulas, VBA macros, data transformation pipelines, and complex spreadsheet problems where Claude's structured reasoning shines.

Formulas

5 prompts

Complex Formula Builder

1/20

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

Builds complex Excel formulas using LET with version-aware alternatives and performance notes.

💡

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

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

Debugs Excel formulas with root cause analysis, corrected formula, and bulletproofing suggestions.

💡

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

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

Refactors complex formulas using LET with meaningful variable names for maintainability.

💡

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

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

Writes dynamic array formulas using modern Excel functions with fallback options.

💡

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

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

Converts array formulas to reusable LAMBDA functions saved in Name Manager.

💡

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.

XML tags are just the start. Learn the full Claude workflow.

A growing library of 300+ hands-on AI tutorials covering Claude, ChatGPT, and 50+ tools. New tutorials added every week.

Start 7-Day Free Trial

VBA & Macros

5 prompts

VBA Macro Generator

6/20

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

Writes VBA macros with error handling, performance optimizations, and install/trigger instructions.

💡

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

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

Debugs VBA code with root cause, corrections, error handling, and performance improvements.

💡

Pro tip: VBA bugs usually come from implicit activation, unqualified references, or missing .Value. Claude catches these patterns reliably.

UserForm Designer

8/20

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

Designs UserForms with controls, initialization, validation, and submit/cancel logic.

💡

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

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

Converts VBA to Office Scripts with API differences and Power Automate integration paths.

💡

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

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

Reviews VBA for portability, security, and packaging before distribution.

💡

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 prompts

Data Cleaning Pipeline

11/20

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

Builds data cleaning pipelines with method selection and validation between steps.

💡

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

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

Designs pivot tables with field placement, value settings, and slicer dashboard integration.

💡

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

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

Detects outliers with method selection based on distribution and visualization setup.

💡

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

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

Sets up data validation including dependent dropdowns, error messages, and protection.

💡

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

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

Optimizes slow Excel workbooks with formula replacement and platform migration recommendations.

💡

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.

Dashboards & Reporting

5 prompts

Dashboard Blueprint

16/20

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

Designs executive dashboards with KPI selection, chart choice, and narrative flow.

💡

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

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

Builds recurring monthly reports with auto-update, variance analysis, and fast refresh checklist.

💡

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

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

Cleans up Excel charts with default-removal, color highlighting, and annotation strategy.

💡

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

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

Builds what-if scenario models with scenario tables, sensitivity analysis, and visual summaries.

💡

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

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

Designs KPI tracking with validation, thresholds, sparklines, and historical archiving.

💡

Pro tip: KPIs shape behavior. Track vanity metrics, chase vanity. Track leading indicators, build value. Pick KPIs that drive the right behavior.

Frequently Asked Questions

Claude's step-by-step reasoning excels at complex multi-condition formulas, nested logic, and debugging. ChatGPT is faster for simple formula lookup. For formulas with 5+ conditions or intricate array logic, Claude's explanation of each part helps you understand and adapt.
Yes, reliably — including error handling, screen updating toggles, and qualified references. Always test in a copy of your workbook first. Claude writes defensive VBA by default, which is better than most online examples.
Yes — LAMBDA, LET, FILTER, SORT, UNIQUE, SEQUENCE, XLOOKUP. Specify your Excel version in the prompt; Claude will offer modern formulas when available and older alternatives when needed.
Power Query for recurring data transformations (clean same way each month). Excel formulas for ad-hoc analysis and real-time calculations. If you find yourself cleaning the same way repeatedly, Power Query will save hours monthly.
Claude can suggest optimization strategies but can't process large data directly. Describe your dataset size and symptoms; Claude recommends formula replacements, Power Query conversions, or migration to BigQuery Connected Sheets.

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 TrialExplore AI for Productivity courses

7-day free trial. Cancel anytime.

Browse All Claude Prompt CollectionsBrowse All Prompt Collections