Claude for Excel: Two Ways to Clean Up a Messy Export

Every few weeks someone in class shows me the same file. It came out of an accounting system or a CRM, it has four thousand rows, and about a third of them are junk — header rows repeated every fifty lines, dates stored as text, account names with trailing spaces, and a “Notes” column somebody used as a diary. Before they can start the analysis they were actually asked for, they burn two hours cleaning. Using Claude for Excel is the fastest way I know to turn those two hours into about ten minutes.

But I want to be specific, because most people get disappointing results the first time and give up. They open a chat window, type “help me clean up my Excel data,” and get back a generic lecture about Power Query. That is not the tool’s fault. It is a prompting problem, and it comes down to two habits.

Claude for Excel starts with the actual file, not a description of it

The single biggest upgrade is to stop describing your data and start handing it over. Save your sheet as .xlsx or .csv, drag it straight into the chat, and then ask something concrete. Here is wording I use in class almost verbatim:

• “This is a raw export. Column A is a date stored as text in M/D/YYYY format, column D is an account name with inconsistent spacing and capitalization, and rows containing the word TOTAL are repeated subtotal rows I do not want.”
• “Tell me every data quality problem you can find, listed worst first, with the row numbers.”
• “Then give me a clean version of the sheet with the subtotal rows removed, the dates converted to real dates, and the account names in proper case.”

What comes back is not advice. It is a list of the actual defects in your file — including the ones you did not know about, like eleven rows where a customer number got stored as a number in one place and text in another — and then a cleaned file you can download and open. That diagnostic pass is the part people skip, and it is the part that saves the afternoon.

The gotcha: always ask for the row numbers, and spot-check three or four of them against the original before you trust the cleaned file. I have never seen the tool invent a row, but I have seen it interpret an ambiguous column the way a reasonable person would and not the way your company means it. “Date” might be the invoice date to you and the posting date to the system. Two minutes of checking protects you from a report that is confidently wrong.

Ask for the formula and the reasoning in the same breath

The second habit is about formulas. Most people ask “what formula do I need?”, paste the answer into the cell, and move on. That works right up until it breaks, and then nobody can fix it — least of all six months later.

Ask for two things instead. Try this shape:

Write an Excel formula for cell F2 that returns the commission rate from the Rates sheet by matching the rep name in D2 and the region in B2. Use XLOOKUP, not VLOOKUP. Then explain in plain English what each argument is doing, and tell me what the formula returns if the rep is not found.

You get back a working formula, a line-by-line translation you can paste into a comment, and an honest answer about the failure case — usually a suggestion to add an if_not_found argument rather than letting #N/A spread through the column. Naming the function you want matters, by the way. Ask vaguely and you often get nested IF statements, because that is what most of the world writes. Say “use XLOOKUP” or “use SUMIFS” and you get the modern version.

The gotcha here: the formula comes back using commas as argument separators. If your regional settings use semicolons, Excel will reject it outright and you will think the formula is wrong when it is only punctuated for a different country. Say so up front — “I am on European regional settings, use semicolons” — and the problem disappears.

Both of these take under ten minutes to try on a file you already have sitting on your desktop. You do not need to attend anything to use them.

Plenty more on the calendar

If you want to see this done live on real files, I am running Claude AI for Excel on Thursday, September 24. We go well past the two habits above: cleaning and reshaping exports, building and auditing formulas, summarizing a sheet into something you can actually send to a manager, and the places where you should still do it yourself in Excel rather than hand it off.

The full current schedule lives on the front page of PCWebinars.com, with new dates going up as they are confirmed. Register for this one, or have a look at what else is coming — there is usually something on Excel, Power BI, Access, Copilot, or Claude within the next week or two.

About your trainer

I’m Tom Fragale. I have been a computer trainer and consultant for more than 30 years, and I have 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: Claude, ChatGPT, and Microsoft Copilot. My background is applied, not theoretical. Alongside teaching, I build real databases and reporting systems for clients in Excel, Access, and Power BI, so the examples I use in class are the problems I ran into last week, not textbook exercises. I also publish free tutorials on YouTube and longer courses online.

If any of that sounds useful, come see the full list of upcoming live sessions and recorded courses at PCWebinars.com.