To combine multiple Excel files into one workbook, use Power Query: in Excel for Microsoft 365 on Windows, choose Data > Get Data > From File > From Folder, select the folder, then choose Combine & Transform Data. This works especially well when the files have the same columns; for a few files or different layouts, copying sheets or using Excel’s Consolidate command may be more appropriate.
What combining Excel files can mean
People use “combine” to describe several different jobs. You might want all records from separate monthly workbooks in one long table, several worksheets brought together in a single workbook, or matching values from multiple sheets summarized by category. These are not the same operation, and choosing the right one prevents a lot of cleanup.
- Append rows: place records from multiple files underneath one another. For example, add January, February, and March order rows to one table.
- Combine worksheets: copy each file’s worksheet into a single workbook, keeping the sheets separate.
- Summarize ranges: calculate totals from similar layouts, such as department budgets, using Data > Consolidate.
- Match columns side by side: bring fields from different sources together using a shared identifier, such as a customer ID. This is a lookup or data-model task, not a simple append.
Before starting, decide whether you need one larger data table, one workbook containing multiple tabs, or a summary. If you append files that were meant to remain separate, or copy tabs when you wanted one sortable list, the final workbook may look combined but still be difficult to analyze.
Choose a method that fits the files
Power Query is usually the best repeatable option for a set of similarly structured files. It imports and transforms data without requiring you to paste each range by hand, and you can refresh the query when source files change. It is especially useful for recurring reports stored together in a folder.
Manual copying is reasonable when there are only two or three small workbooks, you need to preserve distinct worksheets, or the layouts differ enough that each sheet needs individual attention. Excel’s Consolidate command is designed for summarizing corresponding values from multiple ranges; it does not simply stack every source row into one table.
- Use Power Query to append recurring, similarly shaped datasets.
- Copy worksheets when keeping each source as its own tab matters.
- Use Consolidate for aligned summaries, such as totals organized by the same row and column labels.
- Use a lookup or other data-combination method when you need to match records by an ID rather than stack them.
For very large data collections, also consider whether the result belongs in a database or another data tool. An Excel worksheet is limited to 1,048,576 rows, so a combined result cannot exceed that worksheet limit.
Combine files from a folder with Power Query
This method is intended for files that share a consistent structure, such as one worksheet or table with the same headers in every workbook. Put only the source files you want to include in a dedicated folder. Avoid mixing unrelated workbooks, temporary files, or the output workbook in that folder.
- In Excel for Microsoft 365 on Windows, open a blank workbook or the workbook that will hold the result.
- Select Data > Get Data > From File > From Folder, browse to the source folder, and select it.
- Review the file list. Choose Combine & Transform Data to inspect and shape the incoming data before loading it.
- In the combine dialog, choose the sample file and the worksheet or table to use. Check that Excel has selected the intended data and headers.
- In Power Query Editor, review the column names, data types, and rows. Remove unwanted columns or files if needed, then choose Home > Close & Load.
Power Query creates a query based on the files in the folder and combines the selected data. For later updates, put additional matching files in the folder and use Data > Refresh All. The refresh uses the same transformation steps, so inspect the result after adding files with changed columns or formats.
Excel for Mac has Power Query capabilities in Microsoft 365, but available connectors and authoring features can differ from Windows and may depend on the Excel build. If the From Folder option or a particular transformation is not available, use a supported import route in that version, combine the data in Excel for Windows, or use manual copying for a small one-time job. Excel for the web also does not provide the full desktop authoring experience for every Power Query task.
Append queries when data is already imported
If the source tables are already available as Power Query queries, append them rather than starting a folder import. Appending adds rows from one query to another; it matches columns by column name, not by their left-to-right position. Columns present in only one source can result in blank values for the other source’s rows, so consistent names and meanings matter.
- Load each source table or range into Power Query, commonly through Data > Get Data > From Table/Range.
- In Power Query Editor, choose Home > Append Queries > Append Queries as New.
- Select two tables or choose the option for three or more tables, then select the queries to combine.
- Review the appended preview, set appropriate data types, and choose Home > Close & Load.
Appending is not the same as merging queries. Append stacks records; a merge joins columns by matching one or more key fields, much like matching customer records in two lists. If a customer list contains one row per customer and a transaction list contains many rows per customer, a merge may repeat customer information across transaction rows. Check key uniqueness and the desired result before choosing a join.
Copy worksheets into one workbook
To keep the source sheets separate, open the source and destination workbooks. Right-click a worksheet tab, choose Move or Copy, select the destination workbook in the To book list, choose where the sheet should go, and select Create a copy if the original should remain unchanged. Repeat for the other sheets, then save the destination workbook.
Copying a sheet preserves its layout more directly than appending tables, but it can also preserve links and dependencies. Formulas may still refer to the original workbook, named ranges can have workbook-level conflicts, and charts or pivot tables may rely on source data that was not copied. After copying, check formulas, external links, print settings, and any workbook-specific features that matter. If the workbook is large, save a separate copy before making changes.
For data that needs to become one table, copying whole tabs is often only an intermediate step. Standardize the headers and use Power Query to append the tables, or paste only the data rows beneath a single header row for a small one-time combination.
Use Consolidate for matching summaries
Data > Consolidate is useful when several worksheets contain summaries laid out in a similar way and you want a single calculation, such as a total or average. In the destination sheet, select the cell where the result should begin, choose Data > Consolidate, select a function, and add each source range. You can use labels in the top row or left column to match categories when the ranges are not identically positioned.
For example, three department sheets might each list travel, equipment, and training costs. Consolidate can summarize corresponding categories across the ranges. It is not a general-purpose file merger: it does not preserve every transaction row as an appended database table. If you need to filter individual transactions later, append the detail rows instead.
Prepare files and verify the combined result
Source consistency is the main factor in whether an automated combination works cleanly. Before importing, check that files use the same header row, similar column names, and compatible data types. A column called Invoice Date in one workbook and Date in another may not be treated as the same field. Extra title rows, merged cells, blank rows above the headers, or totals at the bottom can also interfere with a clean import.
- Use one header row with a unique, meaningful name for each field.
- Keep dates as dates and numbers as numbers; avoid mixing text labels into numeric columns.
- Remove unrelated notes, subtotals, and blank rows from the data area when practical.
- Check for duplicate files so the same records are not imported twice.
- Retain a source-file or month column when you may need to trace a record back to its origin.
After loading, compare the combined row count with the expected total from the source files, inspect several records from different files, and check that date and currency values look sensible. If the source files each have 500 data rows, for example, three files should contribute 1,500 data rows when there are no excluded rows or headers counted as data. Confirm that totals reconcile with the originals and that no source was skipped.
Common problems and how to fix them
Columns appear blank or split: check whether headers differ, are missing, or contain extra spaces. In Power Query, inspect the column list and rename fields consistently. Because append aligns by column names, two columns in different positions can still combine correctly if their names match, while differently named versions may remain separate.
Repeated headers appear in the middle: some source files may have repeated header rows within the data. Filter those rows out in Power Query, or clean the source files before combining. Also check that the selected object is the actual table or data range rather than a report sheet with title text.
Dates or numbers become text: files may use different regional formats or contain mixed values. Review the detected data type in Power Query and verify that values were interpreted correctly before loading. Do not assume a displayed value is correct merely because it looks plausible; a date such as 03/04 can be ambiguous across regional conventions.
Records appear twice: look for duplicate source files, repeated exports, or overlapping date ranges. Combining does not automatically know whether two identical rows are legitimate. Remove duplicates only when you have a reliable definition of what makes a record unique, such as a transaction ID.
Refresh fails or misses new files: confirm that the folder path still exists and that new files have the expected format and structure. If the output workbook is stored in the same folder being imported, it may be picked up as a source on a later refresh; keep it elsewhere or exclude it from the query.
Privacy, file safety, and refresh behavior
Combining workbooks does not make their contents trustworthy. Treat downloaded files and email attachments with the same care as other documents: use trusted sources, keep security protections enabled, and do not enable macros just to combine data. Power Query imports data and records transformation steps; it does not require running VBA for the folder-combination workflow described here.
A folder query can read files from the path it was configured to use when refreshed. That is convenient, but it means future files placed there may become part of the output. Keep the folder limited to intended inputs and review its contents before refreshing. If the workbook is shared, consider whether the source path is accessible to other users; a local folder path on one computer may not work for colleagues.
Refresh does not necessarily preserve every manual edit made directly in the loaded output table. Treat the query output as generated data and make lasting transformations in Power Query or in a separate downstream table. Keep untouched source copies and save the combined workbook under a clear name so the import can be repeated or audited.
Frequently Asked Questions
Can I combine Excel files without opening each one?
Yes. Power Query’s From Folder workflow can import and append multiple compatible workbooks without opening each file individually. The files still need a structure that the query can interpret consistently, and you should review the imported preview before loading.
Can Excel combine files with different column orders?
Yes, when you append in Power Query, columns are matched by their names rather than position. For reliable results, make the names consistent. If fields have different names or meanings, decide whether they should be renamed, kept separately, or transformed before appending.
Will the combined workbook update when I add another file?
If you used a folder-based Power Query, place the new matching file in the source folder and refresh the query, typically with Data > Refresh All. A manually copied workbook will not update automatically, and a refresh can fail if the new file has a different layout.
How do I combine several sheets into one sheet?
If each sheet has the same headers and you want all records in one list, append the tables with Power Query. If you only need a one-time result from a few small sheets, copy the data rows beneath one header row and remove repeated headers. Copying the sheets into one workbook keeps them as separate tabs.
Is Consolidate the same as combining files?
No. Consolidate summarizes corresponding ranges, often by adding values that share labels. It does not ordinarily stack every source record into one table. Use Power Query to append detail rows when you need one filterable dataset.
Related
- Close Excel Shortcut: Windows and Mac Keys
- Checklist Template Excel: Set Up, Track, and Reuse Tasks
- Excel Drop-Down Lists: How to Create and Manage Them
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Draw.io Merge Cells: Steps, Limits, and FixesYes. In draw.io, also called diagrams.net, you can merge adjacent cells in an editable table: select the cells,…
- How to Compare Documents in Microsoft WordTo compare two versions in Microsoft Word, open Word and choose Review > Compare > Compare on Windows.…
- Excel WEEKDAY: Return a Day Name from a DateTo return a weekday name from a date in Excel, use =TEXT(A2,"dddd"), assuming A2 contains a valid date;…
- Excel Find and Replace Shortcut Key: How Ctrl+H WorksIn Excel for Microsoft 365 on Windows, press Ctrl+H to open Find and Replace, then enter the text…
- Excel Group Rows Count: How to Count Rows in GroupsExcel’s Group command does not calculate a row count; it only lets you collapse or expand selected rows.…
- Customize Styles in Word: A Practical Step-by-Step GuideTo customize styles in Microsoft Word, open the Home tab, right-click a style in the Styles gallery, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.