Claude Prompts for Excel and Spreadsheets

Ever inherited a spreadsheet with 40 tabs, three different naming conventions, and a departed colleague who was the only person who understood the VLOOKUP chain running through it? An operations analyst at a regional logistics company faced exactly that last quarter, and claude prompts for excel turned a two-day reverse-engineering job into an afternoon.

The Problem This Analyst Faced

The spreadsheet tracked shipment routing across 12 warehouses, with formulas referencing named ranges that had been renamed twice without updating the formulas that used the old names. Nothing was broken, exactly — but nobody trusted the numbers anymore, and the analyst needed to document how it actually worked before she could safely change anything.

The Wrong Approach

Her first move was pasting one broken formula and asking:

Why isn't this formula working?

Claude gave a reasonable generic answer about common VLOOKUP errors — but without seeing the actual named ranges, sheet structure, or surrounding cells, it was guessing at the same rate she was.

The Correct Prompt

Here is a formula from cell [reference]: [paste formula]

Here is the structure of the workbook: [list sheet names and what
each tracks, in plain language]

Named ranges used in this formula: [list names and what they
currently point to]

Explain step by step what this formula is trying to calculate,
flag any reference that looks like it's pointing to the wrong range
given what I've described, and suggest a corrected version.

What this does: Giving Claude the workbook's actual structure — not just the broken formula in isolation — lets it reason about whether a reference makes sense in context, which is exactly the kind of cross-referencing that's tedious to do by hand across 40 tabs.

Results and What Changed

Claude correctly flagged that two of the formula's named-range references pointed to a range that had been renamed during a previous reorganization — exactly the bug she suspected but hadn't confirmed. She fixed it in under 20 minutes once she knew where to look, instead of spending an afternoon tracing dependencies manually.

⚡ Pro tip: Ask Claude to also generate a plain-language "data dictionary" for the workbook once you've fixed the formulas — one sentence per named range describing what it represents. This is the documentation step everyone skips and everyone regrets skipping six months later.

⚠️ Common mistake: Pasting only the broken cell without the surrounding context. Formula debugging in isolation is genuinely hard even for a person; Claude does much better with the workbook's structure described alongside the formula.

How to Apply This to Your Situation

For cleaning messy exported data:

Here's a sample of 20 rows from a CSV export: [paste sample]

Identify inconsistent formatting (dates, phone numbers, capitalization)
across these rows and write an Excel formula (not a script) that
would standardize [specific field] into [target format].

What this does: Asking specifically for an Excel formula rather than a script keeps the fix inside the tool your team already uses daily — useful when the person maintaining the sheet later isn't a programmer.

For pivot table and dashboard planning:

I have a dataset with these columns: [list columns]. I want a
dashboard that shows [business question, e.g. "which region has
the highest return rate by month"]. Suggest a pivot table structure
(rows, columns, values, filters) and one chart type that would
answer this question clearly.

Scenario: Small business owner tracking inventory across two locations A small retail business owner used Claude to build a formula that flagged inventory items low at one location but overstocked at another, a comparison she'd been doing manually by eyeballing two separate tabs every week.

Scenario: HR coordinator standardizing a headcount tracker An HR coordinator inherited a headcount tracker with dates entered in four different formats across departments and used Claude's formula suggestion to standardize them without a manual find-and-replace pass on hundreds of rows.

Scenario: Finance analyst auditing a budget variance model A finance analyst used the workbook-context prompt structure to audit a budget variance model built by a contractor, catching a sign error in a formula that was silently understating one department's overspend by describing the sheet structure and asking Claude to sanity-check the formula's logic against it.

Next Steps

The pattern that made this work — describing the workbook's structure, not just the broken cell — applies to almost every Excel problem you'll throw at Claude. Once you've got a prompt structure that reliably gets useful formula help, save it in PromptABCD so the next messy inherited spreadsheet takes an afternoon, not two days.