Data Analysis — Buyer’s guide · 2026
Stack several same-column Excel files into one table — with matched headers, a source-file column, and a row count that reconciles.
6 min read
Quick answer — how to combine multiple Excel files into one
For two or three files with identical columns, copy the rows from each and paste them under the first, keeping one header row. For a folder you refresh regularly, use Data → Get Data → From Folder in Excel (Power Query), which appends every matching file and re-runs when the folder changes. When you also need a column that records which file each row came from and a row count you can reconcile, upload the files to an AI data analyst and ask it to append them — it stacks the matching columns, adds a source_file column, and returns totals that tie back to every file.
You have several Excel files with the same columns — monthly exports, one file per region or client — and you want them stacked into one table you can filter and total. There are three good ways to do it; which one fits depends on how many files you have and how often you repeat the job.
Beginner · Excel for Microsoft 365, 2021, 2019 & 2016.
The core job is an append: take like-structured rows from each file and stack them into one longer table. For a handful of one-off files, copy-paste is the honest answer — open each file, copy its data rows (not the header), and paste them beneath the first file's data. Keep exactly one header row, and make sure the columns line up in the same order in every file before you paste.
Copy-paste breaks down when the files pile up or repeat monthly: it is slow, easy to paste into the wrong place, and it loses track of which rows came from which file. The next sections cover the two ways to keep it reliable — Power Query for a folder you refresh, and an upload-and-append that also records the source and checks the count.
These are two different outcomes and it is worth deciding first. If you only need the files in one place, copy each file's sheet into a single workbook: right-click the sheet tab (or Home → Format → Move or Copy Sheet) and copy it into the destination. You get one workbook with many tabs — good for storage. You can still total across those tabs with formulas, but the data is not one filterable table, so you cannot slice or pivot across all of it at once.
If you want to analyze the files together — sum revenue across regions, filter every client at once — you need them appended into one table, not sitting on separate tabs. That is the job the rest of this page solves. A quick test: if the question is "what is the total across all files," you need one table.
For a folder you re-run every month, Excel's built-in answer is Power Query. Put the files in one folder, then Data → Get Data → From File → From Folder, point it at the folder, and choose Combine & Transform. Power Query reads each file, appends the rows, and — this is the payoff — re-runs when you press Refresh, so next month's file is picked up without rebuilding anything. It is the right native tool when the structure is stable and you repeat the task.
Two honest caveats: the files should share the same schema — Power Query matches columns by name, not position, so a renamed or type-shifted column can surface as errors or nulls you have to chase — and Power Query has a learning curve the first time. For a deeper walk-through of Power Query and other advanced moves, see beyond PivotTables: advanced Excel workflows.
Recording which file each row came from and proving the total reconciles are both doable natively — Power Query can add a Source.Name column, and you can build row-count and total checks on top of it — but you set them up yourself. An AI data analyst like Anomaly lets you ask for the source column and the reconciliation in plain language and review the returned result. Import the files (New project → Import Data → browse to each, or drag them in together; .xlsx, .xls and .csv up to 1GB), then ask it to append them.
Follow along with the three sample files
Three regional sales workbooks — same five columns, four rows each — with the East file storing whole numbers where North and South use decimals. Together they reproduce the run shown below. Synthetic data, no real customers.
a2_sales_north.xlsx · a2_sales_south.xlsx · a2_sales_east.xlsx
"Append a2_sales_north, a2_sales_south and a2_sales_east into one table. Match the columns, add a source_file column showing where each row came from, and give me the row count and the totals for units and amount per file and combined so I can check nothing was dropped."
Append in Anomaly — three files into one 12-row table with a source column and reconciled totals
Result — It stacked the three files into one 12-row table, added a source_file column, and reconciled the result: 60 units and 600 in amount, with each file's subtotals adding up and all 12 order IDs unique — nothing dropped or double-counted. It also flagged a real difference it handled: the East file stored unit price and amount as whole numbers while North and South used decimals, so it standardized the types in the combined table without changing any values.
That reconciliation is the difference that matters. Appending rows is easy; knowing that the combined file contains exactly what the sources did — no dropped rows, no silent type mismatch — is what makes it safe to send on. Uploading is one file at a time via browse (or several dropped in at once), and the combine is a step you ask for after import, not a merge-at-upload button; the source column and the counts appear because you asked for them. The combined table exports to CSV; the files here are small, and the supported inputs are .xlsx, .xls and .csv up to 1GB each.
If your goal is to match rows across files by a shared id — pull a customer's details from one file onto their orders in another — that is a join, a different job from everything above. In Excel you do it with XLOOKUP / VLOOKUP or Power Query's Merge Queries (not Append Queries). Appending, which this page covers, stacks like-structured rows and never matches on a key. If your files are different tables that share an id, you want the join route instead.
If this is a recurring monthly job, the same set of exports arrives every period and needs the same stack. Whatever method you pick, the thing that keeps it reliable is keeping each export in the same shape — expected columns, compatible types, and consistent meanings. When the structure holds, the append is a repeat, not a rebuild.
Related: if the combined data feeds a report you rebuild every period, see create a refreshable monthly dashboard from Excel data. Combining CSVs rather than Excel files, or comparing two files for their differences, are separate jobs handled elsewhere.
For a few files with the same columns, copy the rows from each and paste them under the first — keep one header row. For a folder you refresh often, use Data → Get Data → From Folder in Excel (Power Query), which appends every file, can add a Source.Name column to record which file each row came from, and re-runs when the folder changes; you can build row-count and total checks on top of it. If you would rather request the source column and a reconciled total in plain language and review the returned result, upload the files to an AI data analyst and let it stack the matching columns, add a source_file column, and return counts that tie back to each file.
Yes, as long as the files share the same columns — the same fields in the same meaning. Combining is an append: the rows of each file are stacked into one longer table. That is different from a key-based merge, where you match rows across files by an id to widen them into more columns. If your files have identical structure (monthly exports, one per region or client), any of the methods on this page will stack them; pick by how many files you have and how often you repeat it.
That is a different goal from one combined table. To keep each file on its own tab, open the destination workbook and use Home → Format → Move or Copy Sheet (or right-click the sheet tab) to copy each file's sheet in. You end up with one workbook and many sheets — handy for storage. You can still total across those sheets with formulas (a 3-D SUM across tabs, say), but the data is not one filterable table. To slice, filter, or pivot across all the files at once, you still need to append the rows into one table.
Three files is the point where copy-paste starts to get tedious but is still fine for a one-off. If it repeats every month, Power Query's From Folder is worth setting up once. If you want a source column and a reconciled count without building the query, upload all three and ask for the append — the sample on this page is exactly three regional files stacked into one 12-row table.
That is a join, not an append, and it is a different job from this page. Merging on a column means matching rows across two files by a shared key (an order id, a customer id) to bring their columns together — done with XLOOKUP/VLOOKUP or Power Query's Merge Queries. Appending, which this page covers, stacks like-structured rows into one longer table and does not match on a key.
Power Query's From Folder comes closest in Excel: point it at a folder and it picks up new files that match the structure. You can set it to refresh when the workbook opens or on an interval, so it is not purely manual — but a basic From Folder query is not an unattended end-to-end pipeline unless you configure that refresh or automate it separately. Keeping every monthly export in the same shape is what makes any of these methods, native or AI, stay reliable month to month.
Upload your Excel files, ask to stack them into one table, and get a source-file column and totals that tie back to each file — without building a query.
Technical Product Manager, Data & Engineering
Ash Rai is a Technical Product Manager with 5+ years of experience building AI and data engineering products, cloud and B2B SaaS products at early- and growth-stage startups. She studied Computer Science at IIT Delhi and Computer Science at the Max Planck Institute for Informatics, and has led data, platform and AI initiatives across fintech and developer tooling.
LinkedInContinue exploring AI data analysis with these related insights and guides.
Compare two versions of a spreadsheet and see what changed — View Side by Side, conditional formatting, the Spreadsheet Compare add-in, and a keyed way to show added, removed, and changed rows even when the rows move.
Combine many same-structure CSV exports into one file — headers matched once, types kept, a source column, and a count that reconciles.
Compare two lists in Excel for matches and, more usefully, find the records missing from either side — the COUNTIF, VLOOKUP, and XLOOKUP methods, where they break, and a reviewable way to reconcile both lists.
