Concatenate Excel Files: Combine Workbooks Step by Step

To concatenate Excel files—combine their data into one workbook—use Power Query in Excel for Microsoft 365 on Windows: choose Data > Get Data > From File > From Folder, select the folder, choose Combine & Transform Data, and load the result. This is usually the best method for recurring jobs; for a one-time merge, copying and pasting may be quicker.

What “concatenate Excel files” can mean

People use “concatenate” in two different ways. In a file-management question, they often mean appending the rows from several workbooks into a single table. For example, three monthly sales files can become one list with January, February, and March transactions underneath one shared header. Excel’s Power Query feature is designed for that kind of combination.

In spreadsheet formulas, concatenate means join text values together. For example, joining a first name and last name in two cells creates one text string; it does not combine workbooks. If names are in A2 and B2, =TEXTJOIN(" ",TRUE,A2,B2) returns “Mina Patel” when A2 contains Mina and B2 contains Patel. TEXTJOIN is available in Microsoft 365 and newer Excel versions. The rest of this guide focuses on combining workbook data, while noting when formulas or copying are useful alternatives.

Choose the right way to combine files

The best approach depends on how many files you have, whether you will receive more later, and whether their layouts match. Power Query is generally preferable when multiple files have the same columns or when the task will be repeated. It can import, combine, and reshape the data, and a later refresh can bring in updated source files.

Manual copying is reasonable when there are only a few small workbooks and the task is genuinely one-off. Formula-based stacking can be convenient when the source ranges are already in the same workbook and you want a live combined view, but it is less convenient for a changing collection of separate files.

Choose Power Query for a folder of similarly structured files, repeatable monthly imports, or data that needs cleaning.
Choose manual copying for a few simple files that will not need refreshing.
Choose a stacking formula for ranges in worksheets within the same workbook when a dynamic result is useful.
Do not simply concatenate files at the file-system level by joining their binary contents. Excel workbooks are structured files; their contents need to be imported and combined as data.

Combine files from a folder with Power Query

Power Query can read a set of files from one folder and append their records. In Excel for Microsoft 365 on Windows, put the source workbooks in a dedicated folder, then use this workflow:

Make sure the files contain the data you want to combine and follow a consistent layout. Ideally, each file has one header row and the same column names.
In a new or existing destination workbook, select Data > Get Data > From File > From Folder.
Browse to the folder and select it. Review the file list before proceeding; remove unrelated files from the folder or filter them out in the query.
Select Combine & Transform Data to inspect and shape the imported content before loading it. Combine & Load is a quicker option when the files are already clean and consistent.
In the combine window, confirm the correct sheet or table is selected as the sample. If the data is in a named Excel table, selecting that table is often more dependable than relying on a sheet’s used range.
Review the preview, set appropriate data types, and select Home > Close & Load to place the result in Excel. You can choose Close & Load To if you want to specify where or how the query is loaded.

When Power Query combines a folder, it creates a query that applies a transformation to the files’ contents and appends the resulting rows. Excel may also create helper queries and a sample-file transformation. Those supporting queries are part of the process, so avoid deleting them unless you understand how the main query depends on them. The exact dialog wording can vary slightly with updates to Excel.

For the next batch, place eligible files in the same folder and use Data > Refresh All. Power Query reruns the import steps against the files it finds. Keep the destination workbook outside the source folder if there is a risk of including its output as an input file. Also filter out temporary or unrelated files, such as workbooks that do not contain the expected data.

Prepare workbooks so their rows line up

Appending is not the same as matching records by a key. Power Query places rows from one source beneath rows from another; it does not automatically determine that a customer called “A. Chen” in one file is the same person as “Alex Chen” in another. Matching or reconciling records requires additional transformation steps, such as merging queries on a reliable identifier.

Before combining, compare the source layouts. A good set of files uses the same column names and meanings, the same header location, and compatible data types. A column named Date should contain dates in every file, not dates in one file and comments in another. If one workbook calls a field “Client” and another calls it “Customer,” rename one column so the intended information has a consistent label.

Remove title lines, blank rows, subtotals, and repeated report headers that are mixed into the records.
Check that each source has one header row, not a different number of introductory rows.
Decide how to handle missing columns. A missing field may appear as blank or null for some records, but confirm the result instead of assuming the data means the same thing.
Use Excel tables for source data where practical. Tables expand as data is added and give the import a clear boundary.
Keep a backup of the original files. Power Query reads and transforms source data; it does not require editing those source workbooks.

Consider a concrete example: one file has columns Order ID, Date, and Amount, while another has Date, Order ID, and Amount. If the names are consistent, Power Query can align fields by column name rather than treating the second file’s first column as the same field as the first file’s first column. If the headers differ or are not promoted correctly, inspect and correct the query before loading; otherwise values can land under misleading headings.

Use formulas for ranges already in one workbook

If the data is already in worksheets in the same workbook, VSTACK can place one range below another and return a dynamic array. VSTACK is available in Microsoft 365 and Excel 2024. It does not, by itself, scan a folder of separate workbooks or import an unlimited set of files.

Assume January!A1:C20 has headers in row 1 and 19 records in rows 2 through 20, while February!A1:C18 has the same headers and 17 records. Enter this formula in an empty cell with room for the result to spill: =VSTACK(January!A1:C20,February!A2:C18). It returns 37 rows: one header row plus 36 records, because the second range starts at row 2 and does not repeat the header. If the two ranges have different column counts, the stacked result may include padding values, so matching column structures are preferable.

For separate workbooks, formulas that refer to other files can be fragile: links depend on file paths, and moving or renaming a source can disrupt them. A formula solution also needs ranges updated as records grow unless the sources are structured appropriately. For a folder that receives new files, Power Query is normally easier to maintain.

When copying and pasting is enough

For a small, one-time job, open the source workbooks, copy the data rows, and paste them below the existing records in a destination sheet. Copy the header only once. If the files have different column orders, rearrange or map the fields before pasting rather than assuming that the first column always represents the same information.

After pasting, check whether values and formatting behaved as intended. “Paste Values” can prevent copied formulas from continuing to refer to cells in the source workbook, but it also removes formulas and may not preserve the formatting you want. Pasting normally can bring formulas, formats, and external references along. The appropriate choice depends on whether the combined sheet should contain fixed values or working formulas.

Manual merging becomes error-prone as the file count grows. Common mistakes include copying a header into the middle of the data, missing a row, pasting into the wrong column, and importing a filtered subset without noticing. If the same task will happen again, taking time to establish a Power Query process can reduce repeated handling.

Common problems and how to check the result

A successful load is not proof that the combined data is correct. Check row counts, column names, data types, and a few records from each source. If possible, compare a total such as the sum of Amount before and after combining. A mismatch can reveal omitted files, duplicate imports, filtered records, or text values that were not treated as numbers.

Repeated headers appear among the records: a source may contain a header on every printed page. Filter those rows out in Power Query or clean the source data.
Dates or numbers look wrong: sources may use different regional formats or store values as text. Inspect the imported type and convert values deliberately; ambiguous dates deserve particular care.
Some files are missing: verify the selected folder and any filename or extension filters. Confirm that the files use the expected sheet or table name.
Unexpected blanks appear: compare column names and source schemas. A renamed or absent field can produce null values rather than the information you expected.
Records appear twice: check whether duplicate source files are in the folder and whether the same data was also appended manually. Power Query appends rows; it does not automatically remove duplicates.
Refresh returns an error: a new file may have a different sheet name, layout, password requirement, or data type. Review the query’s applied steps and correct the source or transformation.

For data that must be auditable, preserve the original files and record which folder or batch was loaded. You can also add a source filename column during the Power Query transformation, making it easier to trace a combined row back to its workbook. This is especially useful when investigating an unexpected value or duplicate.

Power Query, formulas, or manual merge?

These approaches solve related but different problems. Power Query imports and transforms data, making it suited to repeatable file-based work. VSTACK assembles worksheet ranges into a formula result that can recalculate as its referenced ranges change. Copying and pasting creates a static result unless you deliberately retain formulas or repeat the process.

Power Query is usually the strongest choice for many files or future refreshes, but it takes setup and a little familiarity with the query editor. A formula is transparent and quick for a small number of stable ranges, but formula references need maintenance and may depend on source workbook links. Manual copying has the lowest setup cost, yet it offers the fewest safeguards against omissions. On Excel for Mac and Excel for the web, Power Query connectors and authoring or refresh capabilities can differ from Windows desktop Excel; check that the needed folder workflow is available in your version before designing a recurring process.

Frequently Asked Questions

Can Excel combine multiple files automatically?
Yes. In Excel for Microsoft 365 on Windows, use Data > Get Data > From File > From Folder, combine the files in Power Query, and refresh the query when the source folder changes. Consistent file layouts make this more reliable.

Does concatenating Excel files delete or change the originals?
Power Query imports data into a query and destination workbook; it does not need to alter the source workbooks. Keep backups anyway, and do not confuse importing data with saving over the original files.

Can I combine files with different column orders?
Usually, if the fields have consistent, correctly identified column names. Check the query preview and output to confirm that values are aligned by the intended field, and standardize inconsistent names before loading.

How do I add new files after combining a folder?
Put compatible workbooks in the folder used by the query, then select Data > Refresh All. If the new files have a different structure, the query may need changes before they can be combined.

Will combining files remove duplicate rows?
No. Appending puts rows together; it does not decide which records are duplicates. Remove duplicates only after choosing which columns define a duplicate and checking that doing so will not discard valid repeated transactions.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.