Use Case
AI Excel & Sheets Formulas with Claude Cowork
Stop Googling spreadsheet syntax. Describe what you need in plain English and Claude writes the formula in seconds — with an explanation if you want one.
25 min
→
90 sec
to build a complex formula
The Problem
Every team has someone who's "good with spreadsheets." That person ends up being interrupted constantly — "can you help with a formula?" — or the rest of the team loses 20 minutes Googling syntax and copy-pasting StackOverflow answers that don't quite work.
The hidden cost is huge. A finance team can lose 5+ hours a week to formula-debugging. An ops team burns hours stitching together VLOOKUPs that should be INDEX/MATCH. Marketing teams build pivot tables three times because the first two had the wrong fields.
Claude Cowork removes the friction completely. You describe what you want in plain English — "I need to count rows where column B is 'Active' AND column D is greater than 100" — and Claude writes the formula. Want to know how it works? Ask for an explanation. Want a Google Sheets version? Just say so. The whole loop takes 30 seconds.
What Claude Does Here
- Writes any formula in plain English — VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, all of it
- Explains what each part of a formula does so you actually learn
- Debugs broken formulas and tells you why they failed
- Translates between Excel and Google Sheets syntax instantly
- Designs pivot tables and Power Query M code from a description
- Suggests cleaner alternatives to your existing nested formulas
How to Set It Up
1
Open Claude alongside your spreadsheet
This works best with Claude in a side window. No setup, no integration — just two windows.
2
Describe your data structure
Tell Claude what columns you have, what the data looks like, and which platform (Excel or Google Sheets). 30 seconds.
3
Ask for the formula in plain English
"Count rows where status is Active and amount is over 100." "Look up the manager name from sheet 2 based on employee ID." That's all the input Claude needs.
4
Paste and adapt
Claude returns the formula with placeholders for your specific cell ranges. Swap them in, hit enter, done.
5
Ask "why does this work?"
If you want to understand the formula (instead of just using it), ask Claude to walk through each part. Over a few weeks you'll find you barely need to ask — you'll know the patterns.
Example Prompt
Prompt
I have a Google Sheets with the following columns:
- Column A: Customer Name
- Column B: Industry
- Column C: Deal Value (in pounds)
- Column D: Status (one of: Open, Won, Lost)
- Column E: Close Date
I need a formula that:
1. Counts the number of "Won" deals where Industry is "Recruitment" AND Close Date is in 2026
2. Returns the total value of those deals as a separate formula
Give me both formulas, then briefly explain how each one works.
What Claude Returns
Claude's Output
FORMULA 1: Count of Won Recruitment deals in 2026
=COUNTIFS(B:B, "Recruitment", D:D, "Won", E:E, ">="&DATE(2026,1,1), E:E, "<="&DATE(2026,12,31))
FORMULA 2: Total value of those deals
=SUMIFS(C:C, B:B, "Recruitment", D:D, "Won", E:E, ">="&DATE(2026,1,1), E:E, "<="&DATE(2026,12,31))
HOW THEY WORK:
COUNTIFS counts rows that match ALL of the criteria you give it. We're saying: count rows where column B is "Recruitment", AND column D is "Won", AND column E is on or after 1 Jan 2026, AND column E is on or before 31 Dec 2026.
The ">="&DATE() syntax is how you compare dates in COUNTIFS — the & concatenates the operator with the date value.
SUMIFS works the same way, but instead of counting matching rows, it sums up the values in column C for those rows. Same criteria, different aggregation.
Real UK small business formula scenarios
Three worked examples from UK sectors we work with — each with a plain-English prompt you can copy, and the formula Claude typically returns. All tested in both Excel and Google Sheets.
1. Accountancy — VAT reconciliation across sales & purchase ledgers
The bane of quarter-end. You’ve got a sales ledger, a purchase ledger, and VAT records that don’t quite tie back. Claude writes the reconciliation formula that flags mismatches by supplier and period.
Prompt
I have two sheets in Excel:
- Sheet "Sales": columns A (invoice date), B (customer), C (net amount), D (VAT amount), E (VAT rate)
- Sheet "VAT": columns A (period), B (customer), C (VAT collected)
Write a SUMIFS formula that goes in Sheet "VAT" column D. It should sum the VAT amounts from Sheet "Sales" that match the current row’s customer AND fall within the current row’s VAT period (Q1 = Jan-Mar, Q2 = Apr-Jun, etc.).
Explain what to change if a period isn’t a standard calendar quarter.
Typical output: a working SUMIFS using date bounds with EOMONTH to handle quarter-end boundaries, plus the exact substitution to use for non-calendar quarters (e.g. April year-end).
2. Property & letting — yield calculation from a portfolio spreadsheet
UK letting agents and landlords calculate yield differently at gross vs net levels, and need to account for UK-specific costs (Section 24, agency fees, letting fees). Claude handles the correct formula given whatever cost columns you actually have.
Prompt
I have a Google Sheet tracking rental properties:
- Column A: property address
- Column B: purchase price
- Column C: monthly rent
- Column D: annual management fees
- Column E: annual maintenance budget
- Column F: annual insurance
- Column G: mortgage interest per year
Write two formulas that go in columns H and I:
- Column H: gross yield (%) — annual rent divided by purchase price
- Column I: net yield (%) — annual rent minus all costs (D+E+F+G), divided by purchase price
Format both as percentages to 2 decimal places. Then write a conditional formatting rule that highlights any property with net yield below 4% in red.
3. Recruitment agency — commission calculation with tiered rates
UK recruitment commission structures often have tiered rates (e.g. 15% up to £50k billing, 20% from £50k-100k, 25% above £100k). Writing this by hand with nested IF statements is painful and error-prone. Claude writes it in one go and can convert to IFS if you’re on a modern Excel or Sheets version.
Prompt
I need an Excel formula for tiered commission calculation.
Consultant total billings for the year are in cell B2. Commission structure:
- 15% on billings up to £50,000
- 20% on billings between £50,000 and £100,000
- 25% on billings above £100,000
The commission should be cumulative — so someone billing £120,000 gets 15% on the first £50k, 20% on the next £50k, then 25% on the remaining £20k.
Write the formula. Then write a simpler version using IFS() for Excel 2019+ users, and explain when to use which.
The complex nested version handles legacy Excel; the IFS version is cleaner if your team is on Microsoft 365. Both give the identical answer — Claude will show you both and note the difference.
★ Free 5-Day Challenge
Try this for yourself in 10 minutes
The free 5-Day Claude Challenge teaches you exactly how to set up workflows like this — emails, reports, data extraction, and your own automation playbook. No card, no fluff.
Frequently Asked Questions
Does this work for both Excel and Google Sheets?
Yes. Claude knows the syntax for both, including the small differences (Google Sheets uses ARRAYFORMULA, QUERY, IMPORTRANGE; Excel uses XLOOKUP, FILTER, dynamic arrays). Tell Claude which platform you're using and it writes the right syntax. It also handles older Excel versions if you specify.
Can it explain why a formula works?
Absolutely — and this is one of its best features. Ask Claude to break down each part of a formula and it will walk you through what every function and argument does. You'll actually learn the formula, not just copy and paste.
What about complex pivot tables and Power Query?
Claude handles both. For pivot tables, you describe the data and the analysis you want, and Claude tells you exactly which fields to put in rows, columns, values, and filters. For Power Query, it can write M code to handle data transformations that would take 30 minutes manually.
Can Claude debug a formula that's broken?
Yes — paste the formula and the error you're getting (or the wrong output) and Claude will identify the issue. Common fixes include missing absolute references, wrong column counts in VLOOKUP, mismatched data types, and circular references. Saves hours of head-scratching.
Will this make me reliant on AI for spreadsheets?
Only if you let it. The smarter use is to have Claude explain each formula it writes — over time you'll learn the syntax patterns and only need Claude for the genuinely complex ones. Most teams find their core formula skills improve, not decline.
Does Claude know UK-specific finance formats like £, DD/MM/YYYY dates, and UK tax logic?
Yes. Reference “UK format” explicitly in your prompt and Claude uses £, DD/MM/YYYY dates, and UK financial year references (April-March by default). For tax logic, Claude knows the current UK VAT rates and basic tax bands, but always verify the specific rate against HMRC guidance for anything client-facing.
Can it help with the specific quirks of UK accounting software exports (Xero, QuickBooks, Sage)?
Yes — particularly for cleaning up messy CSV exports. Xero’s date columns, QuickBooks’ multi-currency exports, and Sage’s legacy CSV format all have known quirks that Claude can write formulas or Power Query steps to fix. Describe the export format and what you need — Claude handles the transformation.
Related Use Cases