When you can’t recall an Excel function or want to automate a repetitive task, you can describe what you want in plain words and Claude will produce a formula or VBA macro. This guide covers how to build and refine Excel formulas and VBA without any add-in, right in a regular claude.ai chat. (Formulas and code from Claude must be verified on your real data, and the result is your responsibility.)
1. Building Excel formulas
The most common use is to describe the calculation you want and get the matching function — you don’t need to know the function name.
- “Make a formula that sums column C where column A is a date in the current month and column B is ‘Done’.” → a conditional sum (e.g., SUMIFS)
- “Give me a formula that pulls a price from another sheet by product code.” → a lookup function (e.g., VLOOKUP, INDEX/MATCH, XLOOKUP)
- “Can you simplify this formula?” → cleaning up nested formulas
The key is to describe your data layout concretely: which column holds what, and example values. That gets you much closer to a formula you can use as-is.
2. Automating repetitive work with VBA macros
Tasks you repeat by hand can be automated with a VBA macro. Describe the task and Claude writes the code.
- “Write a macro that merges data from several sheets into one.”
- “I need a macro that deletes every row where a certain column is empty.”
- “Give me code that combines the Excel files in this folder into one.”
To run the code, open the VBA editor in Excel with Alt+F11 and paste it into a module.
⚠️ Before running a macro: VBA changes files directly, so back up the original first, run it, and read through what the code does line by line. Don’t run macros from unknown sources.
3. Finding error causes and reading formulas
It’s also useful when an existing formula breaks, or you need to understand someone else’s complex formula.
- “This formula throws a #REF! error — what’s the cause? [paste the formula]”
- “Why do #VALUE! and #N/A errors usually happen?”
- “Explain what this long formula does, step by step.”
Tips for better results
- Be specific about the data layout: say things like “dates in B2:B100, amounts in column C.”
- State your Excel version: functions like XLOOKUP and FILTER only work in Microsoft 365 / Excel 2021 or later. Mention an older version up front to get an alternative.
- Give an example of the desired result: showing “this is what it should look like” improves accuracy.
Things to watch for
- Verification is required: there’s no guarantee a formula or code is always correct. Run it on real data and check. Macros that change data are safest tested after a backup.
- Adjust cell references: the ranges in a suggested formula may need to match your own sheet.
- Version differences: some functions aren’t supported in older Excel.
- Mind sensitive data: rather than pasting real confidential or personal data, describe the structure or use example values.
FAQ
Q. The formula doesn’t work.
Check the cell references, data layout, and Excel version. Pasting the exact error message back to Claude helps pin down the cause.
Q. Are VBA macros safe?
They’re safe if you review the code and back up the original file before running. If you don’t know what the code does, don’t run it.
Q. How is this different from the “Claude in Excel” add-in?
The add-in has Claude work directly inside the Excel app; this method has you get code and formulas in the free chat and paste them in yourself. To work right inside Excel, see the Claude in Excel guide; for spreadsheet and slide automation, see the Claude in Excel & PowerPoint guide.