Merge Excel Files with AI: Combine Spreadsheets, No VLOOKUP

Three spreadsheets, one report to build. Orders in one file, payments in another, a customer list in a third. The keys don't quite match, the dates come in three formats, and an hour into VLOOKUP territory half your rows say #N/A.

This is a job you can hand to Claude. Upload the files, describe in plain words what should be joined with what, and get a merged Excel file back. No formulas, no code on your side: Claude writes and runs the code itself, inside the chat. Here is the step-by-step, including the part most guides skip: how to verify the result.

What you need

• A Claude account. The web app at claude.ai and Claude Desktop both work.
• Your Excel files. CSV is fine too.
• 20-30 minutes the first time. Once you have a prompt that works, the same merge takes a couple of minutes.

Fair warning: the first run is not instant. Most of the time goes into deciding, yourself, what links to what and what to do with rows that have no match. But those are the same questions VLOOKUP would have forced on you anyway.

Step 1: Prepare the files

Claude copes with messy exports surprisingly well, but five minutes of cleanup saves half an hour of confusion:

1. Remove clutter from the sheets: logos, side notes, subtotal rows sitting in the middle of the data.
2. Make sure headers live in a single row. A three-row header full of merged cells is the number one source of chaos.
3. Find your key: the column that connects the tables. Order number, tax ID, SKU, email. Write down what it is called in each file, because the names almost never match.
4. If a workbook has several sheets, delete the irrelevant ones or tell Claude explicitly which to use.

Step 2: Upload everything into one chat

Start a new chat and attach all the files at once, via the paperclip or drag and drop. Don't feed them one by one across separate messages: Claude does a better job when it sees the whole picture at once.

Step 3: Write the prompt (a real example)

The classic mistake is typing "merge these tables" and hoping for magic. Claude will merge them, but by its own guesses. Describe the task the way you would brief a new hire:

``` I have three files: orders from our CRM (orders.xlsx), a payments export from the bank (payments.xlsx), and a customer list (clients.xlsx).

Merge them into one Excel table.

Join keys: - orders and payments match on the order number: it is the "Number" column in orders and "Order ID" in payments; - pull the customer from clients using the "Tax ID" column.

Columns for the final table: order number, date, customer, order amount, amount paid, difference.

Rules: - if an order has several payments, sum them; - don't drop unmatched rows silently: collect them in a separate list and show it to me before the final build; - never invent data: if a value is missing, leave the cell empty.

At the end, print control totals: row counts for each source file, the row count of the result, and the total of the "order amount" column before and after the merge. ```

Why this works:

• The keys are named explicitly, with the exact column names from each file. That is half the battle.
• Duplicates get a rule in advance. Several payments per order is normal, not an error.
• Unmatched rows never vanish quietly. That is where the real problems live: typos in order numbers, lost payments.
• "Never invent data" is spelled out. An empty cell beats a plausible-looking guess.

Step 4: Verify before you trust it

Don't send the file to your boss unchecked. Not because Claude is unreliable in some special way, but because that is what you do with any merged report, whoever built it. The minimum routine:

1. Row counts. If orders had 1,480 rows and the result has 1,512, some key duplicated and multiplied rows.
2. Checksums. The money total in the source must match the money total in the result. A plain AutoSum in Excel takes a minute.
3. Spot-checks. Pick 5-7 random rows from the result and trace them back to the source files by hand. Random ones, not the first ones: the first rows are usually the cleanest.
4. The unmatched list. Read the list Claude showed you before the build. That is where you see what got lost and why.

If something doesn't add up, don't start over. Reply in the same chat and say exactly what is wrong: Claude will fix the logic and rebuild the file.

Common traps

• Mixed date formats. 07/01/2026 in one file, 2026-07-01 in another, and raw serial numbers like 46204 in the third. Claude usually detects this on its own, but it is safer to say upfront: "normalize all dates to YYYY-MM-DD".
• Duplicate keys. The same order number appears three times. Decide before merging: sum them, keep the latest record, or have Claude show you the list.
• Merged cells. A staple of pretty reports. When the file is read as data, a merged cell has a value only in its first row; the rest come out empty. Ask Claude to fill values down, or unmerge in Excel beforehand.
• Numbers stored as text. "$12,500.00" with a stray space or currency sign is text, not a number. Ask Claude to convert such columns to numeric, or your checksums will never reconcile.
• One customer under three names. "Acme Corp", "ACME Corporation" and "acme corp." are three different customers to a formula. Fuzzy matching is exactly where an AI shines, but ask it to print the mapping table so you can eyeball the questionable pairs.

A note on data privacy

Before uploading anything, look at what is actually inside the file:

• If the merge doesn't need names, phone numbers, or emails, strip those columns first and join on an internal ID instead.
• Anything under an NDA or regulation should be cleared with whoever owns security in your company. That applies to any external service, not just AI tools.
• Review the privacy settings in your own Claude account and the current Anthropic data policy in the official help center: both get updated over time.

The short version

Merging several Excel files through Claude genuinely beats VLOOKUP when the data is messy: mismatched column names, broken dates, duplicates. The recipe: clean the files, name the join keys explicitly, forbid guessing, then verify row counts, checksums, and a handful of random rows. The manual check is never optional; it is part of the method.

If you want to go beyond this one recipe, AGINE Academy teaches you to work with Claude through hands-on lessons. It is a story-driven online academy where you play a robot named UNIT and complete missions inside the real Claude, not a simulation. Every lesson ends with a working result you keep. The starter block of 4 lessons is free with no signup, and the full program runs from installing Claude Desktop all the way to Claude Code, for $25 a month.