Claude in Excel: Two Ways to Fix a Messy Data Export

Somebody in class always has the same file. It came out of the accounting system or the CRM, it has four thousand rows, and every one of them is wrong in some small way. Names in ALL CAPS. Dates stored as text so they will not sort. A column where half the entries have a trailing space you cannot see but Excel can. They have been fixing it by hand, one find-and-replace at a time, for two years. That file is where Claude in Excel earns its keep.

Not because AI magically cleans data, because it does not. The hard part was never the typing. It was knowing which function to reach for, in what order, and what that function quietly fails to handle. That is the part Claude is actually good at.

Here are two things I show people that pay off the same afternoon. Neither one requires you to attend anything first.

Paste a Sample Into Claude in Excel and Ask for the Rule, Not the Answer

The common mistake is dumping the whole column in and saying “clean this up.” You get back a cleaned column, paste it into the sheet, and now you have data you cannot explain and cannot reproduce next month. Ask for the formula instead. Then you own it.

Copy ten or fifteen representative rows, the ugly ones included, and type something close to this:

Here are 15 rows from a customer name column exported from our system. They are inconsistent: some are ALL CAPS, some have extra spaces, some include a middle initial. Give me one Excel formula that normalizes them to proper case with single spaces. Explain what each part does, and tell me which cases it will NOT handle.

What comes back is usually =TRIM(PROPER(A2)), a plain-English walkthrough of why PROPER runs first and TRIM second, and an honest list of what breaks: McDonald becomes Mcdonald, IBM becomes Ibm. That last part is the whole reason to ask the question this way. You find out about the exceptions before your boss does.

The gotcha that bites almost everyone: if the export came out of a web page, a PDF, or certain accounting systems, those invisible trailing spaces are not real spaces. They are non-breaking spaces, character 160, and TRIM will not touch them. Your formula looks right, the cells still will not match, and you lose an hour. Tell Claude that and you get this instead:

=TRIM(PROPER(SUBSTITUTE(A2,CHAR(160)," ")))

Two more habits worth building:

• Send a sample, not the whole file. Fifteen rows is plenty to infer the pattern, and it keeps your full customer list out of a chat window.
• Always ask “what will this not handle.” Phrase it exactly that way. It changes the answer from a confident one-liner into something you can actually check.

If your Claude plan includes the Claude for Excel add-in, it can work against the open workbook directly and you can skip the copying. The prompt above works either way, including in a plain browser tab.

Have It Write the Script for the Report You Rebuild Every Month

Everybody has a report they rebuild from scratch every month: same import, same six steps, same pivot, same formatting. Forty minutes, twelve times a year.

Describe those steps and ask for an Office Script. Be specific about names, because Claude cannot see your workbook and will invent them if you do not:

Write an Office Script for Excel on the web. The sheet is named RawData. Column A is Date stored as text in MM/DD/YYYY format, column D is Region, column G is Amount. Delete any row where Amount is blank, convert column A to real dates, then create a new sheet named Summary with total Amount by Region, sorted largest first. Add comments explaining each step.

You paste that into Automate, Code Editor, Run. Forty minutes becomes a button. And because you asked for comments, you can read what it does and change the region grouping yourself in six months when the territories get redrawn.

Two gotchas here. First, Office Scripts lives in Excel on the web and needs a Microsoft 365 business plan. If you are on desktop Excel with a personal account, ask for VBA instead and say so in the prompt, and remember the file then has to be saved as .xlsm. Second, and I mean this one: run it on a copy the first time. A script that deletes rows does not ask twice, and there is no undo once it has run through four thousand of them.

Plenty more on the calendar

If you want to watch this done live on real files instead of reading about it, Using Claude to Be More Productive in Excel runs on Thursday, October 22. We go through the cleanup prompts above, the scripting workflow, and the part people always want next: getting Claude to build the pivot and write the summary paragraph that goes on top of it.

The full current schedule is on the front page of PCWebinars.com, with new dates going up as they are confirmed. Register for this session or go browse the others and find the one that matches the file sitting on your desktop right now.

About your trainer

I’m Tom Fragale. I’ve been a computer trainer and consultant for more than 30 years, and I’ve taught well over 30,000 people how to get more out of the software they already own: Excel, Access, Power BI, Word, Outlook, and now the AI tools that sit on top of all of it, including Claude, ChatGPT, and Microsoft Copilot. My background is applied, not theoretical. Alongside the teaching I build real databases and reporting systems for clients in Excel, Access, and Power BI, so the examples I bring to class are the problems I ran into last week, not textbook exercises. I also publish free tutorials on YouTube and longer courses online.

You can find the full schedule, the recordings, and the custom training options at PCWebinars.com.