Merge Excel Sheets into One Table
To merge Excel sheets into one table, first decide which worksheets contain comparable records. Then align their columns, append the data rows, and check the result against the originals. Putting every tab in the same workbook is a different task from combining its records into one sheet.
You can do this manually, build a repeatable query in Microsoft Excel, write a macro, or use CombineSheets in your browser. The right choice depends on how often the job repeats, how consistent the sources are, and whether a values-only output meets your needs.
Prepare the worksheets before combining
Keep an unchanged copy of each workbook. List the tabs you actually need, leaving out summaries and notes unless they belong in the final dataset. Adding a summary beneath its detail records can double-count amounts in later calculations.
Check the header names and their meaning. Revenue and Sales may describe the same field in your organization, but software cannot safely assume that. Decide on consistent labels before combining. Also check whether dates, amounts, and identifiers use compatible representations.
Record the expected data-row count for each selected table, excluding its header. Decide whether repeated rows should remain. An identical record can be an accidental overlap or a legitimate repeated event; removing it requires a business decision.
Use copy and paste for a small one-time job
Create a destination sheet with one header row. Copy the first table's data beneath it, then append each remaining table without copying its header again. If column orders differ, rearrange the source columns in a working copy before pasting, or copy each field into its matching destination column.
Use Paste Special > Values when you want a fixed table of results. Ordinary pasting can bring formulas and formatting into the destination, so review references and appearance if you choose that route. Neither paste method checks whether you selected every required record.
After each paste, compare the newly added row count with your notes. Inspect the boundary between tables for a repeated header, an omitted last row, or an extra total. Manual copying is reasonable for a few small tables, but the selection work must be repeated when new reports arrive.
Use Power Query for a recurring report
Power Query is useful when the same preparation and append steps need to run again. Use desktop Microsoft Excel for Windows or a recent supported Mac version; available connectors and menus differ by version. On Mac, check the supported Power Query workflow before following Windows menu instructions.
For similarly structured files in a folder, the general Windows workflow is:
- Put the intended source files in a dedicated folder, keeping the destination workbook elsewhere.
- Choose Data > Get Data > From File > From Folder and select that folder.
- Review the file list and filter out unrelated files before combining.
- Use Combine & Transform Data, selecting the intended table or worksheet from the sample workbook.
- Check headers, data types, and the resulting rows in the editor, then use Close & Load.
The selected sample object defines a pattern for the other files. This does not automatically collect every different tab in every workbook. For details, use Microsoft's folder import instructions.
If the tables already exist as separate queries, use Home > Append Queries in the editor and select the queries to stack. Append matches column names rather than positions; absent fields become null. Review the append behavior before loading. Save the query and refresh it for later reports, checking that the source structure still fits its steps.
Consider VBA when custom rules justify maintenance
A VBA macro can loop through chosen workbooks and worksheets, map fields, and copy records according to rules you write. It can suit a recurring internal process that needs more control than a manual sequence provides.
That control comes with maintenance. Someone must define sheet selection, header handling, error handling, and duplicate rules, then test the macro against changed inputs. Macro permissions also depend on the desktop environment. Do not assume a macro copied from another workbook understands your report structure or preserves the features you need.
Combine worksheets with CombineSheets in the browser
Open the worksheet combining tool, select your files, and review the worksheet choices in each workbook. Hidden worksheets start unselected. Choose One table to append selected records into one sheet. The Excel sheet merger runs in your browser, so file data is processed locally and is not uploaded.
The first non-empty row in each selected sheet becomes its header. Names match regardless of capitalization or surrounding spaces. Blank or duplicate header names stop the merge. Remove report titles above the labels in a working copy so that the intended header is first.
The first table establishes column order. New names in later tables are added at the end, and missing fields leave empty cells. Review the new and missing columns for every sheet; different names remain different columns even when they sound similar.
For One table, you can add the optional Source file column. In a multi-sheet workbook, its labels include the workbook and worksheet names. Optional duplicate removal compares the whole aligned business row, ignores that added source field, and retains the first copy. It does not select the newest record by an identifier.
Choose XLSX or CSV for one table. Choose Keep separate sheets with XLSX when you want the selected tables on individual tabs instead. Source-column and duplicate-removal options apply only to One table. Both modes export values only: formula expressions and workbook formatting are not preserved. Recalculate and save formula-driven originals first; a missing saved formula result stops export.
Choose the method that fits your next run
| Situation | Starting point |
|---|---|
| A few small tables, needed once | Copy and paste with row-count checks |
| Regular reports with a stable structure | Power Query with reviewed refresh steps |
| Custom processing rules and someone to maintain them | A tested VBA macro |
| A local browser workflow and a values-only result | CombineSheets, within its batch and device limits |
For CombineSheets, free input batches allow 30 files and 50 MB total, with a guard of 1,000,000 non-empty cells. A pass allows 300 files, 100 MB, and 2,000,000 cells. Phones use 20 MB and 300,000 cells across tiers. A device can run out of browser memory below these guards.
Check the combined table before using it
Review rows read, empty rows skipped, duplicates removed, and output rows. Open the download and compare identifiers, amounts, blank cells, and a few records at each source boundary. Reconcile totals using the same records and duplicate decision as the merge.
Keep the originals for future calculations and formatting. For a worked example of changed column order and a repeated record, follow the monthly reports walkthrough. Before sharing the result, make sure you can account for every column, the row count, and each duplicate you removed or kept.