Choose the Right Way to Combine Excel Files Without Losing Track of Your Data

To merge Excel documents, first decide what the finished workbook should contain. Use Power Query From Folder to append similarly structured files into one refreshable table, Power Query Merge to add related columns by matching an ID, Move or Copy to preserve worksheets as separate tabs, or Data > Consolidate to calculate totals and other summaries.

Related: How to Create a Pivot Table in Excel From Clean Data.

Select a row number above.

Choose the merge method based on the output you need

“Merge” describes four different operations in Excel. Choosing the wrong one can produce a valid workbook that does not answer your actual question.

For recurring reports with compatible schemas, Power Query From Folder is usually the most practical starting point. You define the import and cleaning steps once, then refresh the result when compatible files are added or updated.

These methods are not interchangeable. Consolidate applies a summary function instead of stacking records. Power Query works with table data rather than preserving each workbook’s formulas, charts, macros, formatting, and overall structure. Move or Copy retains separate worksheets, but it does not create a single analysis-ready table.

Microsoft documents the Power Query folder workflow for desktop versions from Excel 2016 through Microsoft 365 in its folder-import guidance. The same desktop-version range is listed for Excel Consolidate.

Prepare the source files before merging

Create backup copies before changing anything, and build the merged output in a new workbook rather than modifying the only copies of the source files. Backups provide a recovery point and make it easier to compare the result with the originals, as recommended in this workbook-merging preparation guidance.

For an append, inspect every input as a table:

• Standardize column headers, including capitalization, spacing, and punctuation.
• Make corresponding columns use compatible data types.
• Remove blank rows and columns that interrupt the tabular range.
• Confirm that each workbook contains the intended worksheet, table, or named range.
• Decide how to handle extra, missing, or renamed columns before loading the result.

Power Query can match columns by name even when their order differs. That flexibility does not make inconsistent headers harmless: Customer ID, CustomerID, and Customer Number will not automatically become the same field merely because they contain similar values.

For a folder import, place the intended inputs in a dedicated folder. Check whether relevant subfolders will be included, and plan to filter the file list by Extension or Folder Path when the location also contains archives, temporary files, or unrelated documents.

For a key-based join, verify that both key columns represent the same identifier. A numeric customer number and a text customer number may look alike on screen but fail to match reliably. Inspect blank keys, duplicate keys, whitespace, leading zeros, and one-to-many relationships before merging.

For Consolidate, determine whether the source ranges have matching layouts or matching labels. Select an empty destination area with enough space below and to the right for the generated summary.

If the finished dataset will be published, use synthetic or already-public information in the workflow. Confidential, personal, or regulated records should not enter a public publishing process because TablePage publications are public.

Append multiple Excel files into one table with Power Query

Consider an illustrative folder containing three monthly sales workbooks:

• Sales_2026_01.xlsx
• Sales_2026_02.xlsx
• Sales_2026_03.xlsx

Each workbook contains a table with the columns Date, Region, Product, and Amount. The files can contain different numbers of records, but their column names, meanings, and data types should be compatible. This synthetic example explains the workflow; it is not a report of product testing.

To combine the files in desktop Excel:

1. Open a new workbook.
2. Select Data > Get Data > From File > From Folder.
3. Select or paste the path to the source folder.
4. Review the resulting file list before combining anything.
5. Choose Combine & Load or Combine & Transform Data.
6. Select the relevant worksheet, table, or named range from the sample workbook.
7. Review the preview and confirm the combination.
8. If you opened Power Query Editor, complete the transformations and select Close & Load.

Combine & Load creates the query and loads the combined result directly. Use it when the folder is controlled and the files already have clean, predictable structures.

Combine & Transform Data opens Power Query Editor before loading. It is the better choice when you need to:

• Filter out non-Excel extensions.
• Exclude archive or unrelated subfolders.
• Remove temporary or extraneous files.
• Rename or remove columns.
• Correct date, number, or text types.
• Filter unwanted records.
• Add a source filename for traceability.

Power Query uses a sample file to define the transformations applied to the other files. For Excel inputs, the standard Combine Files dialog selects one worksheet, table, or named range from the sample file—not several objects at once. If every workbook contains multiple relevant sheets, you need a more deliberate query design rather than assuming the standard dialog will combine all of them.

Column order may differ because matching can occur by column name, but the sources still need compatible structures, names, and data types. Folder imports may also consider files in included subfolders.

The Skip files with errors option omits problem files; it does not repair them. Record every skipped filename and investigate the cause. Otherwise, the loaded table may appear complete while lacking an entire month, region, or department.

After checking the preview, choose Close & Load. When a compatible workbook is added to the same folder—or an existing source is updated—refresh the query to rebuild the combined table from the current inputs.

This procedure appends table data. It does not preserve each source workbook’s complete formatting, formulas, charts, macros, or workbook structure. If those elements must remain available on separate worksheets, use Move or Copy instead.

Join two tables by an ID with Power Query Merge

Appending and joining solve different problems:

• Append stacks records that share the same fields.
• Join adds related fields by matching one or more key columns.

Suppose an Orders table contains Order ID, Customer ID, Order Date, and Amount. A separate Customers table contains Customer ID, Customer Name, and Region. To add customer details to each order, join the tables on Customer ID.

In Power Query Editor:

1. Select the Orders query.
2. Choose Home > Combine > Merge Queries to modify that query, or Merge Queries as New to create a separate result.
3. Set Orders as the left table and Customers as the right table.
4. Select Customer ID in both tables.
5. Choose the appropriate join kind.
6. Confirm the merge.
7. Expand the new table-valued column and select the customer fields to add.

For a key made from multiple columns, select the columns in the same corresponding order on both sides. The matching columns should also have compatible data types. Table position matters because left and right directional joins retain different sets of records, as explained in Microsoft’s Power Query Merge overview.

For the Orders example, a left outer join normally preserves every order. After merging, expand the Customers column and select fields such as Customer Name and Region.

Run a separate left anti join to list orders whose customer IDs have no matching customer record. Investigate these rows rather than silently dropping them.

Repeated keys also require attention. If one order matches two customer rows, expanding the join produces two copies of that order. Two left rows matched to three right rows produce six combinations. This can inflate an amount total even though every original order remains present. Compare row counts before and after the join, inspect blank keys, and confirm whether duplicates are legitimate before accepting the result.

Copy complete worksheets into one workbook as separate tabs

If you need one workbook containing the source worksheets as separate tabs, use Excel’s Move or Copy command instead of combining their rows with Power Query.

This is a manual workflow supported by Microsoft community guidance rather than formal product documentation:

1. Open the source workbook and destination workbook.
2. In the source workbook, right-click any sheet tab.
3. Choose Select All Sheets.
4. Right-click a selected tab again and choose Move or Copy.
5. Select the destination workbook.
6. Choose where the sheets should appear.
7. Enable Create a copy if the worksheets must remain in the source workbook.
8. Confirm the operation.

The grouped-sheet procedure transfers the selected worksheets from one open workbook together, but it normally must be repeated for each source workbook. The sequence is outlined in a Microsoft Q&A answer about combining workbooks as separate sheets.

This method suits a small number of workbooks when retaining separate tabs matters more than creating one flat, analyzable table. For example, it can collect quarterly workbooks into a yearly file while keeping each month on its own sheet.

After copying, inspect:

• Duplicate or conflicting sheet names.
• Formulas that refer to other worksheets.
• External workbook links.
• Charts and their source ranges.
• Sheet-level and workbook-level named ranges.
• Macros and other workbook-level features.

Do not assume every dependency will transfer exactly as intended. Open representative sheets, recalculate the workbook, test important links, and compare key outputs with the originals.

Avoid relying on an unreviewed VBA macro merely to process many files. Automation that clears destination sheets, assumes matching sheet names, or writes several source ranges to the same destination can erase or overwrite content. The supplied third-party example illustrates these hazards, so production automation requires backups, testing, logging, and explicit collision handling rather than copying the example unchanged from a general workbook-merging tutorial.

Summarize matching ranges with Excel Consolidate

Data > Consolidate combines selected ranges with a summary function such as SUM or AVERAGE. It does not append every source row.

To consolidate data:

1. Create or select a blank master worksheet.
2. Select the top-left destination cell.
3. Choose Data > Consolidate.
4. Select the summary function, such as SUM.
5. Add each source range in the Reference box.
6. Select the applicable row or column label options.
7. Enable Create links to source data if the result should update when source values change.
8. Select OK.

When a source range is in another workbook, use Browse to locate the file and insert its path into the Reference field.

There are two ways to align the source ranges:

• By position: Use this when every source has the same layout, labels, label order, and cell locations.
• By category: Use this when matching labels identify corresponding values even though those labels appear in different positions.

Nonmatching labels can produce separate rows or columns in the result rather than being treated as the same category. Leave sufficient empty space below and to the right of the destination cell so the output does not overwrite existing content.

Selecting Create links to source data allows the consolidated result to respond when linked source values change. Without links, you must update the summary manually.

Formulas are another option for a small number of selected calculations. A 3-D reference can calculate the same cell across a range of worksheets, while individual sheet references can address values in different positions. Use them sparingly: complex cross-sheet formulas can be harder to audit than an explicit table-based workflow.

Validate the merged data, then prepare it for public sharing

A successful refresh or completed copy operation does not prove that the result is complete. Validate the output against the intended sources.

For appended data:

1. Count the expected data rows in each source.
2. Add those counts together, excluding header rows.
3. Account for deliberate filters, rejected records, and known exclusions.
4. Compare the expected count with the loaded table.
5. Compare important totals, such as Amount, with the source workbooks.
6. Spot-check records from the beginning, middle, and end of the result.

For joins, compare row counts before and after the merge. Review unmatched records with an anti join and investigate unexpected increases or decreases. An increase may indicate repeated keys; a decrease may indicate that an inner join discarded unmatched records.

For folder imports, maintain a simple manifest as a quality-control record. This is a recommended audit aid, not a built-in Excel requirement.

Review every file omitted through Skip files with errors. Its records are absent from the combined table, so the error should be corrected or documented before the dataset is treated as complete.

Before sharing, normalize column names and confirm date, number, and identifier types. Keep codes as text when leading zeros are meaningful—for example, an identifier such as 00421 should not become 421. Aim for a clean, flat dataset with one header row, one record per row, and one consistent meaning per column.

Once the result is suitable for publication, TablePage can turn a spreadsheet or structured dataset into a public interactive data page.

Privacy warning: TablePage publications are public. Never upload sensitive, confidential, personal, or regulated information. Publish only data that has been reviewed and is safe for unrestricted public access.

Choose the workflow that matches the required output: append recurring files with Power Query From Folder, join related tables with Power Query Merge, copy worksheets when separate tabs must remain, or use Consolidate for summaries. Before using the result, validate row counts, totals, unmatched records, and skipped files. If the dataset will be published, prepare a clean, flat table containing only information that is safe to make public.