Combine Excel Files: Practical Methods and Steps

How-to basics · Updated November 19, 2026

To combine Excel files with the same column layout, put them in one folder, then in Excel for Microsoft 365 on Windows select Data > Get Data > From File > From Folder. Choose Combine & Transform Data to append their rows into one query. If you need to match records by an ID or summarize separate worksheets, use a merge or consolidation instead; the right method depends on how the files relate.

What combining Excel files can mean

People use “combine” for several different tasks, and choosing the wrong one can produce a tidy-looking but incorrect result. The most common task is appending: adding rows from several files to one longer table. For example, you might stack January, February, and March sales records that all have Date, Product, and Amount columns.

A second task is merging: matching rows from different tables using a shared field, such as Customer ID. A customer list might have names and addresses, while an orders file has IDs and purchase totals. Merging can add the customer details beside each matching order. A third task is summarizing, where you total or count values from corresponding ranges without creating one detailed list.

  • Append rows: use Power Query for repeated files with a consistent structure.
  • Match records: use Power Query’s merge feature when tables share a reliable key.
  • Summarize ranges: use Data > Consolidate when the goal is a total or other summary, not a combined record-level table.
  • Join a few small ranges: copy and paste, or use a formula if the data is already in one workbook.

Why Power Query is usually the best choice

Power Query is Excel’s data-import and transformation tool. It can collect data from a folder of workbooks, apply the same cleanup steps to each file, and load the combined result into a worksheet or the Data Model. Its main advantage over manual copying is repeatability: when new files arrive, you can add them to the folder and refresh the query rather than rebuilding the combined table.

The folder method works best when files have compatible layouts. Their column names and meanings should match, and the relevant data should be in the same kind of object, such as a named Excel table or a worksheet with a consistent name. Extra columns, different header rows, or different sheet names may require adjustments in the query. Power Query does not know that two differently named fields mean the same thing unless you tell it how to handle them.

Power Query is not limited to simply stacking rows. In its editor, you can remove unwanted columns, filter out blank rows, set data types, rename fields, and merge tables. Those steps are recorded as a query, which makes the process easier to review and repeat than a long series of manual edits.

Prepare the workbooks before combining them

A little preparation prevents many common import problems. Keep the source files in a dedicated folder, and avoid saving the final combined workbook in that same folder if the folder query might include it. Otherwise, a later refresh could read the output as another source and duplicate its contents. Keep unrelated workbooks and temporary files out of the source folder too.

Check that the files use a consistent structure. Ideally, each file has one header row, no merged cells in the data area, and one record per row. Convert each dataset to an Excel table with Insert > Table or Ctrl+T, and give tables a clear name if you are selecting tables rather than sheets. Tables expand as data is added, unlike a fixed range that can miss new rows.

  • Use the same column names and meanings in every file; a column called Revenue should not contain units in one workbook and dollars in another.
  • Check that dates are real Excel dates and numbers are numeric, not text that only looks numeric.
  • Remove explanatory notes or blank title rows above the actual headers, or plan to remove them in Power Query.
  • Decide whether duplicate records are valid. Combining files does not automatically identify duplicates as errors.

Combine files from a folder in Excel for Windows

These steps apply to Excel for Microsoft 365 on Windows. Keep a backup of the source files before making structural changes, and make sure the files are closed or saved before importing.

  1. Put the workbooks you want to combine in one folder. If the data is in tables, use a consistent table name and layout where possible. If it is in worksheets, keep the relevant sheet name and layout consistent.
  2. Open a new or existing workbook to hold the combined result. Select Data > Get Data > From File > From Folder, browse to the source folder, and confirm the selection.
  3. Review the file list in the folder preview. Remove unrelated files from the folder, or use the available filtering options if only some listed files belong in the result.
  4. Select Combine & Transform Data. In the combine prompt, choose the appropriate sheet or table from the sample file. Excel uses that choice to interpret the other workbooks, so check that the same object exists and has the expected layout in the remaining files.
  5. In the Power Query Editor, inspect the preview. Confirm that the headers are correct, remove unwanted rows or columns, and check the data types. If a first row was imported as data rather than headers, use Home > Use First Row as Headers.
  6. Select Home > Close & Load to return the result to Excel. To choose where it goes or load it as a connection, use the Close & Load dropdown and select Close & Load To.

The exact wording or availability of import options can vary by Excel build. Power Query features and connectors also differ between Windows, Mac, and Excel for the web. If From Folder is not available in your installation, check the current Power Query options for that platform or combine the data using a supported Windows desktop installation.

Refresh the combined result when files change

After the query is created, add new compatible workbooks to the source folder and refresh the result with Data > Refresh All. Excel reruns the recorded import and transformation steps against the folder contents. You can also right-click the loaded query table and choose Refresh. If you rename or move the folder, the query’s source path may need to be updated in its settings or source step.

Refreshing is not a substitute for checking the data. A new file with a different header, an unexpected sheet name, or a changed data type can cause errors or missing values. After a refresh, compare the row count or totals with the source files, and review any error rows reported by the query. If you need an audit trail, preserve the original files and consider retaining a source-file name column so each imported record can be traced back.

Merge tables or summarize worksheets instead

To match information across tables, open the query editor and use Home > Merge Queries. Select the two tables and the corresponding key column in each, then choose the join kind that matches your goal. For example, a left outer join keeps every row from the first table and adds matching values from the second; unmatched rows in the second table are not added as separate records. Expand the merged column to select the fields you want to include.

Before merging, make sure the key fields have compatible data types and consistent values. A customer ID stored as a number in one table and as text in another may not match as expected. Duplicate keys also matter: if the lookup table has multiple rows for one ID, a merge can produce multiple results for a record rather than a single match.

For totals from similarly arranged ranges, select Data > Consolidate. Choose a function such as Sum, add each source range, and specify whether labels are in the top row or left column when appropriate. Consolidate is useful for summary figures, but it does not create a detailed, appended list of every source record. If you need to preserve individual transactions, use Power Query instead.

Use a formula for small, fixed datasets

If you have copied two same-shaped datasets into one workbook and want to stack their rows, VSTACK can return one combined array. It is available in Microsoft 365 and Excel 2024. It is not a folder-import tool: it does not discover new files or automatically clean inconsistent workbooks.

For example, assume the first dataset occupies A2:B3 and the second occupies D2:E3 in the same workbook, with headers kept outside those ranges. Enter =VSTACK(A2:B3,D2:E3) in an empty cell. The result is four rows by two columns, with the two ranges stacked in order. If one range has more columns than the other, Excel fills the missing positions with #N/A; make the layouts consistent or handle the mismatch deliberately. For a changing collection of files, a refreshable Power Query is generally easier to maintain.

Common problems and how to avoid them

  • Repeated headers in the middle of the result: a source file may contain more than one header row. Remove repeated header rows in the query or fix the source files before combining.
  • Missing data: the selected sheet or table may not exist in every file, or the query may be filtering out rows. Check the sample-file selection and the applied steps in Power Query.
  • Numbers or dates treated as text: inconsistent regional date formats or text symbols can prevent correct type detection. Inspect the column values and set an appropriate type after import.
  • Duplicate records: appending combines rows; it does not deduplicate them. Decide what makes a record unique, then remove duplicates only if duplicates are genuinely unwanted.
  • Refresh errors: a source file may have been renamed, moved, locked, or changed structurally. Restore the expected folder and layout, or edit the query to account for the change.
  • Unexpectedly large results: the folder may include the output workbook, backups, or unrelated files. Keep source and output locations separate and inspect the folder preview.

How to choose the right method

For dozens or hundreds of recurring monthly files with the same columns, use Power Query’s folder import. For two short lists that will not change, copying and pasting may be quicker, provided you verify the headers and data types. For records connected by an ID, merge rather than append. For a compact summary of corresponding ranges, use Consolidate. Formulas are useful for a small, fixed set of ranges, but they can become difficult to manage when files move or the source list grows.

The key question is whether you need one longer dataset, a set of matched fields, or a summary. Establish that first, then check the result against a known row count or total. A successful combine is not just one that completes without an error; it must preserve the records and relationships the analysis depends on.

Frequently Asked Questions

Can I combine multiple Excel files without opening each one?
Yes. In Excel for Microsoft 365 on Windows, use Data > Get Data > From File > From Folder and combine compatible workbooks through Power Query. You can review the imported files and transformations without manually opening and copying data from each workbook.

Can Excel combine files with different column names?
It can, but the columns need to be reconciled. Rename fields in Power Query so that equivalent information has a consistent name, and decide what to do with fields that occur in only some files. Otherwise, the combined table may contain separate columns or blank values.

Will combining files remove duplicates?
No. Appending adds records; it does not decide whether two similar rows represent duplicates. Use a defined key or a deliberate duplicate-removal step only after confirming which records should count as the same.

Can I combine worksheets from one workbook?
Yes. Power Query can import workbook sheets or tables, and you can append compatible queries. The folder workflow is intended for files in a folder; for worksheets within one workbook, choose an appropriate workbook or sheet import route and check that the sheet layouts match.

Can I combine Excel files on a Mac?
Power Query support and available connectors differ by Excel version and platform. The instructions above describe Microsoft 365 on Windows. Check the Data tab and current Power Query support in your Mac version; if the needed folder connector is unavailable, use a supported Windows desktop version or another method appropriate to the files.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.