To combine Excel files into one, either copy sheets or data into a new workbook, or use Power Query to append matching tables from multiple files. For a repeatable process—especially when new files arrive regularly—Power Query is usually the best choice; for a one-time job, copying may be quicker.
Choose the right kind of combination
“Combine files” can mean several different things. First decide what the finished workbook should contain, because the right method depends on the result you need:
- One table with all records: Append rows from several files that share the same columns. For example, combine monthly sales workbooks into a single list.
- One workbook with separate tabs: Keep each source sheet distinct, such as one tab per department or reporting period.
- One summary of matching ranges: Add, count, or otherwise summarize values in corresponding positions, such as totals by region across several worksheets.
- A wider table combining related fields: Match records using a shared identifier, such as a customer ID. This is a join, not simply an append, and requires a clear rule for matching rows.
Appending is the usual meaning when someone wants to combine several files into one dataset. It puts records underneath one another; it does not automatically match or reconcile duplicates. If one workbook has customer names and another has the same customers’ order details, appending them will not line up those fields. A join or lookup-based workflow is needed instead.
Combine files manually for a one-time task
Manual copying is straightforward when there are only a few workbooks and the data is small. In Excel for Microsoft 365 on Windows, open the source and destination workbooks, select the source data, copy it, and paste it into the destination. Keep the header row only once, then paste each later file’s records beneath the existing data. Check that the columns are in the same order before pasting.
To bring an entire worksheet into another workbook, right-click its sheet tab and choose Move or Copy. In the “To book” list, select the destination workbook, choose where to place the sheet, and select Create a copy if you want the original sheet to remain in its source workbook. Repeat for each sheet. This preserves a sheet as a separate tab rather than stacking its rows into a single table.
Before copying, make sure the destination has enough empty space and that you are not pasting over existing records. A useful sequence is:
- Create a new workbook and give it a clear name.
- Copy the first source dataset, including its headers.
- Compare the next file’s headers and column order with the destination.
- Copy only its data rows, then repeat for the remaining files.
- Save the combined workbook and inspect the first, last, and a few middle records.
Copying and pasting can also carry unwanted formatting, formulas, hidden rows, or formulas that refer back to the source workbook. Use Paste Values when you want fixed results rather than formulas, and inspect links or formulas before distributing the file. For very large datasets, manual selection is more error-prone and may run into worksheet row limits.
Use Power Query to append files from a folder
Power Query is Excel’s built-in data import and transformation tool. It can read a set of workbooks, combine their tables or sheets, and record the steps so the result can be refreshed later. It works best when source files have a consistent structure: the same column names, compatible data types, and a predictable table or worksheet to import.
In Excel for Microsoft 365 on Windows, put the source workbooks in one folder, then choose Data > Get Data > From File > From Folder. Select the folder and choose Combine & Transform Data in the preview window. Excel will use a sample file to guide the combination. Select the relevant sheet or table, review the preview, and use the Power Query Editor to remove unwanted columns or rows and correct data types. When ready, choose Home > Close & Load to load the result into a worksheet, or use the load options to choose a destination.
After the first setup, add new source workbooks to the folder and choose Data > Refresh All to update the combined query. Refreshing reruns the recorded import steps; it does not mean that every possible change to a source file will be handled correctly. If someone renames a column or changes a sheet’s structure, review the query and its output.
Keep the output workbook outside the source folder, or configure the query to exclude it. Otherwise, a later refresh may try to include the combined workbook as another input. Also avoid putting unrelated workbooks in the folder: if they have a different layout, they can cause errors or introduce irrelevant data.
Power Query is available in Excel for Microsoft 365 on Windows. Excel for Mac has Power Query capabilities, but available connectors and authoring options can differ by Excel version and update channel; a folder-import option may not appear in every Mac installation. Excel for the web also does not provide the same full query-authoring workflow as desktop Excel. If the required command is missing, check the current product version or use desktop Excel on Windows for the folder-combine steps.
Handle files with different layouts
Power Query can combine files more reliably when their structures agree. “Same structure” means more than having roughly similar information: headings should identify the same fields, and each field should be in a consistent column. For instance, one file may call a column “Order Date” while another calls it “Date”; one may store dates as real dates while another stores text. Standardize these differences before combining or create transformation steps that account for them.
Remove report titles, blank lines, subtotals, and repeated header rows if they are not actual records. If each workbook contains a named Excel table, selecting that table can be more dependable than importing a worksheet whose used range includes decorative content. If a worksheet is the only source, inspect the sample transformation and confirm that the first row is treated as headers when appropriate.
Files with different columns need a deliberate policy. You can rename equivalent fields to a shared heading and decide what to do when a field is absent—often the combined output will show a blank for that column in records from the file that lacks it. If the files represent unrelated categories, keeping them in separate queries or tabs may be clearer than forcing them into one table.
Keep sheets separate or summarize matching ranges
If the goal is to preserve each source sheet, use Move or Copy rather than appending. Give tabs descriptive names, and check for duplicate sheet names: Excel cannot have two worksheets with the same name in one workbook. Copying sheets can also bring along formulas, charts, named ranges, or links that still refer to the original workbook, so inspect these items if the combined file must stand alone.
For matching ranges that need to be added or summarized rather than stacked, Excel’s Data > Consolidate command may fit. It can consolidate values using a function such as Sum or Average and can match labels when the source ranges use consistent row or column headings. It is not a general-purpose tool for collecting every record into one table. For detailed transaction lists, use an append workflow; for summaries of corresponding values, consider Consolidate or a PivotTable after the records are combined.
Check the combined workbook
A successful import is not proof that the result is correct. Compare the output with the sources before relying on it. Record how many data rows each source contains and check that the combined row count is plausible. Look for missing records, duplicated headers, unexpected blank rows, and values that shifted into the wrong columns.
Review data types as well. Dates imported as text may sort incorrectly, and product codes with leading zeros can lose those zeros if interpreted as numbers. Identifiers are often better treated as text when their digits are labels rather than quantities. Check totals or a few known records against the original files, especially after removing rows or changing types in Power Query.
Consider whether duplicates should remain. Two identical rows may be legitimate separate transactions, or they may be accidental copies. Do not remove duplicates automatically without deciding what makes a record unique—for example, an order ID may be a safer basis than comparing every visible field. Keep an untouched copy of the source files so that you can investigate discrepancies.
Common problems and how to avoid them
- Repeated headers appear in the middle of the output: Remove header rows from the data during transformation, or make sure the import treats headers consistently.
- Some records are missing: Check whether the query points to the intended folder, whether a source file has a different sheet or table name, and whether filters exclude rows.
- Columns are blank or misaligned: Compare source headings and types. Similar-looking columns with different names may not be recognized as the same field.
- A refresh fails after a file change: Restore the expected sheet or table structure, or edit the query steps to reflect the new layout.
- The workbook becomes slow or unwieldy: Remove columns and rows that are not needed before loading, and avoid loading multiple unnecessary copies of the same data.
- Links still point to source workbooks: Inspect formulas and workbook links, especially after copying whole sheets. Values-only output may be more suitable for a static handoff.
Excel worksheets have a maximum of 1,048,576 rows and 16,384 columns. If the combined result exceeds a worksheet’s capacity, do not assume that loading it to a sheet will preserve every record. Use an appropriate data model or database workflow, or split the output in a controlled way.
Choose a method for future updates
For a small, fixed set of files, manual copying is simple and transparent. For recurring monthly or weekly files with a stable layout, Power Query saves repeated work and makes the transformation steps visible. Keeping separate tabs is best when readers need to inspect each source independently. Consolidate is aimed at summarizing corresponding ranges, not creating a detailed appended ledger.
Before sharing a combined workbook, remove or protect sensitive information that should not be distributed, and confirm that the recipient can access any linked source files if live links remain. If you need a refreshable result, document where source files belong and avoid changing their naming or layout without updating the query. If you need a static snapshot, save the final output and make clear that it will not update when the original files change.
Frequently Asked Questions
Can I combine multiple Excel files into one worksheet?
Yes. If the files contain the same kind of records and matching columns, append their rows with Power Query or copy the data beneath one header row. If you want each source preserved separately, copy each worksheet into the destination workbook instead.
Will Power Query update the combined file automatically?
No. It can rerun the saved import steps, but you generally need to refresh the query, for example with Data > Refresh All. New files are included only if they are in the source folder and meet the query’s selection and structure requirements.
Can I combine files that have different column names?
Yes, but decide which headings mean the same thing and standardize them or transform them in Power Query. If fields differ because the files contain different kinds of information, keep separate outputs or define how unmatched fields should appear.
Does combining workbooks delete or change the originals?
Power Query reads source data and creates a query result; it does not need to overwrite the source workbooks. Copying sheets also leaves the original in place when you choose Create a copy. Still, save a separate destination file and verify which workbook you are editing.
Can Excel combine files stored in different folders?
The From Folder workflow is designed to read files from a selected folder. You can move or copy the intended inputs into one dedicated folder, or build a more tailored query if the sources must remain in separate locations. Keep the source locations stable so refreshes can find them.
Related
- Change All Caps to Lowercase in Excel: A Practical Guide
- Can You Change Border Color in Excel? Steps and Tips
- Can Git Track Excel Files? Version Control Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Create a Calendar Template in Word That You Can ReuseTo create a reusable calendar template in Word, make a seven-column table for the days of the week,…
- Split a Google Sheets Column Into Two ColumnsTo split a column in Google Sheets, select the cells, choose Data > Split text to columns, then…
- Can You Split a Cell in Google Sheets? Here’s HowYes, you can split the text in a Google Sheets cell into separate columns using Data > Split…
- Wrap Text in Excel: Keyboard Shortcut and Quick StepsIn Excel for Microsoft 365 on Windows, select the cell or cells and press Alt, then H, then…
- Excel Max Value in a Column: Find the Largest NumberIf your values are in B2:B100, enter =MAX(B2:B100) in an empty cell; Excel returns the largest numeric value…
- Excel Workbook Extension: XLSX, XLSM, and MoreThe standard extension for a modern Excel workbook is .xlsx. Use .xlsm when the workbook needs to keep…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.