Why Claude changes the spreadsheet game
Most operators build trackers the slow way: Google the formula, try it, get a #VALUE error, Google again, find a forum post from 2014, try that version, still wrong. An hour later you have something that mostly works. You move on before you've added the conditional formatting.
Claude knows Excel formulas deeply — SUMIF, INDEX/MATCH, IFERROR, array formulas, dynamic ranges, conditional formatting rules, data validation. More importantly, it can reason about your specific structure and write formulas that fit your actual data, not a generic example.
The workflow shifts: instead of building a tracker from scratch, you describe what you need and Claude produces the structure, the formulas, and the logic in one pass. You paste it in, verify it, and refine from there. A tracker that used to take 2–3 hours gets built in 20 minutes.
You're no longer limited by what formulas you happen to know. If you can describe the logic — "flag any line that's more than 5% below target, three weeks in a row" — Claude can write it. Your constraint shifts from Excel knowledge to knowing what you actually need the tracker to do. That's a much better constraint to have.
The two things Claude does best with spreadsheets
- Structure design. Column layout, tab organization, header naming, summary rows. Claude thinks through what a working tracker needs before writing a single formula.
- Formula writing. Complex logic written correctly the first time — with IFERROR wrappers, absolute references where needed, and comments explaining what each formula does.
The Claude-Excel workflow — how it actually works
Before we build anything, understand how Claude and Excel interact. Claude doesn't open files or click cells. It produces text — formula sets, column structures, conditional formatting instructions — that you paste into Excel. Once you know the workflow, it becomes seamless.
-
1
Describe your data structure first
Tell Claude what columns you have, what rows represent, and how data flows. "Column A is the production line name. Column B is weekly target units. Columns C–F are actual units for each of the four shifts." Claude can't see your spreadsheet — you have to describe it.
-
2
Ask for the structure, then the formulas separately
For complex trackers, ask Claude to propose the column layout first. Review it. Then ask for the formulas. This two-step approach catches structural problems before you've invested in building formulas around the wrong layout.
-
3
Paste and verify — don't trust blindly
Paste Claude's formulas into Excel and test them against known values before you fill down. Check one row by hand. If the formula gives you the right answer on a row you can verify manually, it'll be right everywhere.
-
4
Refine in the same conversation
"The variance formula is correct but I want it to show red when negative and green when positive — can you give me the conditional formatting rule for that?" Stay in the conversation. Claude remembers the structure it built.
-
5
Upload your existing tracker for revisions
If you're improving an existing spreadsheet, you can upload it. Tell Claude what you have and what you want changed. "Here's my current tracker — I need to add a rolling 4-week average column and a data validation dropdown for the downtime reason codes."
Claude writes formulas based on the structure you describe. If your description is slightly off — if column C is actually column D in your real sheet — the formula will be wrong. Verify before you fill down 52 rows. One check on a known row takes 30 seconds and saves an hour of cleanup.
The three tracker types operators use most
Most operational spreadsheet work falls into three categories. Each has a standard structure that Claude can build on. Know which type you need before you start prompting.
In practice, these three types often live together: a Production Tracker on Tab 1, a KPI Dashboard on Tab 2 that pulls from Tab 1 with summary formulas, and a Variance Report on Tab 3 for weekly review. Claude can design the whole workbook structure — just ask for it that way.
Building a production tracker — full walkthrough
Here's a complete prompt-to-output cycle. This is how you'd actually run a session to build a production tracker from scratch. Read through it once before you run your own.
Step 1: The structure prompt
ROLE: You are an Excel specialist building operational tracking tools for a manufacturing consultant. CONTEXT: I need a weekly production tracker for a packaging plant with 3 production lines (Line A, Line B, Line C). Each line runs 2 shifts (Day, Night). Data is entered weekly. I track output units, targets, downtime hours, and OEE. The tracker will be filled in by the production supervisor and reviewed by the plant manager on Mondays. TASK: First, propose the column structure for this tracker — tab layout, column names, and what each column does. Don't write formulas yet. Just the structure. STANDARD: Propose something a production supervisor can fill in quickly. No unnecessary complexity. Column headers should be plain English, not abbreviations.
Claude will return a proposed layout. Review it. Ask for any changes before moving to formulas. Once the structure is right, run the second prompt:
Step 2: The formula prompt
The structure looks good. Now write the formulas. Assume the layout is: - Column A: Week (date) - Column B: Line - Column C: Shift - Column D: Target Units - Column E: Actual Units - Column F: Variance (Actual minus Target) - Column G: Variance % (Variance divided by Target) - Column H: Downtime Hours - Column I: Available Hours (assume 10 hours per shift) - Column J: OEE % (Actual Units divided by theoretical max, where max = Target × (Available Hours minus Downtime Hours) / Available Hours) - Column K: Notes Write the formulas for columns F, G, and J. Include IFERROR wrappers so blank rows don't show errors. Also write the conditional formatting rule to highlight Variance % red when below -5% and green when above +5%.
What Claude produces
Here's representative output — the kind of formula set Claude returns for a prompt like this:
| Column | Header | Formula (Row 2) | Notes |
|---|---|---|---|
| F2 | Variance | =IFERROR(E2-D2,"") | Blank if no data entered |
| G2 | Variance % | =IFERROR((E2-D2)/D2,"") | Format column as % with 1 decimal |
| J2 | OEE % | =IFERROR(E2/(D2*((I2-H2)/I2)),"") | Blanks if hours not entered |
| Conditional formatting: Apply to G2:G100 → Red fill if < -5%, Green fill if > 5% | |||
Paste each formula into the correct cell, verify on one row of real data, then fill down. The conditional formatting rule you apply manually via Format → Conditional Formatting using the rule Claude describes.
Step 3: The summary row
Once your tracker has a week or two of data, ask Claude to write a summary row — weekly totals, averages, and a period-to-date roll-up:
Now add a summary section at the top of the sheet (rows 1–5, above the data which starts at row 7). The summary should show: - Total actual units this month (sum of column E where the week date falls in the current month) - Total target units this month (same logic, column D) - Overall variance % for the month - Average OEE across all lines and shifts this month - A "Month status" cell that shows "ON TRACK" if overall variance % is above -2%, "AT RISK" if between -2% and -5%, and "OFF TRACK" if below -5%. Use SUMIFS and AVERAGEIFS. Assume dates are in column A and the data range is rows 7 through 200.
Excel features worth asking Claude for
Most operators underuse Excel because they don't know the formulas exist. Here are the features that make the biggest difference in operational trackers — and how to ask Claude for each one.
Conditional Formatting
Color-code cells automatically based on value — red/amber/green status without manual formatting.
"Write a conditional formatting rule for column G (Variance %) that applies red fill for values below -5%, yellow for -5% to 0%, and green for anything above 0%."
Data Validation Dropdowns
Lock a column to a preset list — downtime reason codes, department names, status values — so supervisors can't enter free text.
"Write a data validation rule for column K that restricts entries to this list: Equipment Failure, Changeover, Scheduled Maintenance, Material Shortage, Operator Absence, Other."
SUMIFS / AVERAGEIFS
Sum or average a column based on multiple conditions — e.g., actual units for Line A only, in the current month only.
"Write a SUMIFS formula that totals column E (Actual Units) only where column B equals 'Line A' and column A falls within the current month."
Rolling Averages
A 4-week or 12-week rolling average that updates automatically as new data is added.
"Write a formula for a rolling 4-week average of OEE (column J) that always looks at the last 4 rows of data, regardless of how many weeks have been entered."
Named Ranges
Name a range of cells so your formulas read like plain English instead of =SUMIFS(E7:E200,B7:B200,"Line A").
"Rewrite the formulas using named ranges. Suggest names for each key data range and show me where to define them in Excel's Name Manager."
Dashboard Pull Formulas
Summary formulas on a Dashboard tab that pull from a Data tab — so your dashboard always reflects the latest data.
"I have a Data tab and a Dashboard tab. Write SUMIFS formulas for the Dashboard that pull this month's totals from the Data tab. The data tab is named 'Data' and weekly rows start at row 2."
Ask Claude to explain any formula it writes. "Explain what this formula does step by step." Understanding the formula means you can modify it yourself later — and you can spot errors when something doesn't look right. Don't just paste and trust. Know what you're putting in the sheet.
Building a KPI dashboard
A KPI dashboard is a summary view — usually one tab — that gives a plant manager or owner the numbers they need in 30 seconds. It pulls from your production data and formats it for at-a-glance reading.
The key to prompting Claude for a dashboard is leading with the audience's questions, not the data structure. What does the reader need to know? Answer that question, then ask Claude to build the formulas that surface those answers.
Dashboard prompt structure
ROLE: Excel dashboard specialist building a KPI summary for an operations consultant. CONTEXT: My plant manager reviews this dashboard every Monday morning. She needs to know three things in under 30 seconds: 1. Are we on track for the month? 2. Which line or shift is causing problems? 3. Is OEE trending up or down over the last 4 weeks? Data lives on a tab called "Weekly Data" — weekly rows, columns A through K as described in our previous session. TASK: Design a KPI dashboard tab layout and write the formulas that answer those three questions. Include: - A "Month Status" header section: MTD actual vs. target, variance %, and a status indicator - A line-by-line breakdown: each of the 3 lines with their MTD actual, target, and variance % - A 4-week OEE trend: one number per week, with an arrow or label indicating direction (up/down/flat) STANDARD: Keep the layout clean — this is printed and posted in the supervisor's office. No more than 20 rows. Use conditional formatting so the status indicators are visually obvious without needing a legend.
If the dashboard gets printed — and in most plants, it does — ask Claude to include print layout guidance. "Fit to one page landscape. Suggest column widths and font sizes that print cleanly." Claude will give you the specific Excel settings to apply.
Iterating on spreadsheet work
Once you have a working tracker, most of the ongoing work is refinement — adding columns, adjusting formulas, responding to what the team actually needs when they start using it. Claude is particularly good at this kind of targeted iteration because it holds the context of what it already built.
High-value iteration prompts
| What you need | How to ask for it |
|---|---|
| Add a column | "Add a Scrap Rate % column after column J. Scrap Rate = (Target minus Actual) / Target, but only show it when variance is negative. Otherwise leave blank." |
| Fix a formula that's giving errors | "Column G is returning #DIV/0! when column D is blank. Rewrite the formula so blank rows show nothing instead of an error." |
| Change the OEE definition | "My client defines OEE differently — it's Availability × Performance × Quality. Availability is (Available Hours minus Downtime) / Available Hours. Performance is Actual Units / (Target Units × Availability). Quality is Good Units / Actual Units. Rewrite the OEE formula with those three components. I'll add Good Units as a new column L." |
| Add a new shift | "We added a third shift (Midnight). Update the summary formulas so they include all three shifts and the monthly totals remain accurate." |
| Convert to a table | "Convert this layout to an Excel Table (Ctrl+T) format. Rewrite the formulas using structured table references instead of cell addresses so they extend automatically when new rows are added." |
Once a tracker has been built and modified a few times, the easiest way to iterate is to upload the current Excel file directly to Claude. "Here's the current version. I need to add a rolling 4-week variance trend column and fix the OEE formula in column J — it's giving wrong results when downtime exceeds 2 hours." Claude can read the file, understand the current structure, and write the corrections without you re-describing the whole layout.
Your Production Tracker
Build a working tracker for your actual operation. Not a practice exercise — something you'll use next week. Follow the walkthrough from Section 4, customized to your lines, shifts, and metrics.
What to build
What lines or shifts are you tracking? What metrics matter — units, OEE, downtime, scrap? What time period does one row represent? Write this down before you open Claude.
Structure prompt first — review and approve the layout. Formula prompt second — get the formulas, paste them in, verify on one row of real data.
Conditional formatting for variance status, a data validation dropdown for a reason code column, or a summary row that aggregates to the period. Pick one and ask Claude to build it.
Populate the tracker with actual numbers — even if you have to estimate. A tracker with real data reveals problems a blank one doesn't. Note anything that doesn't work and bring it back to Claude for a fix.
Your structure prompt and formula prompt — once refined — are reusable. Save them to your prompt library from Module 02. The next tracker for a new client starts from this template.