To consolidate multiple Excel workbooks into one, choose whether you want to combine their rows into a single table, keep each workbook’s sheets as separate tabs, or summarize matching ranges. In Microsoft Excel for Microsoft 365 on Windows, Power Query is usually the best repeatable option for combining similarly structured files; use Move or Copy for separate tabs and Data > Consolidate for summaries.
Decide what you mean by consolidate
Consolidation can mean several different things, and the right method depends on the result you need. Before moving data, decide whether you are combining records, collecting worksheets, or calculating totals. These outcomes are not interchangeable: adding every workbook’s rows to one list is different from adding the values in corresponding cells.
- Append records: Put rows from several workbooks into one table, such as monthly orders or staff schedules. Power Query is well suited to this when the source columns follow the same pattern.
- Keep worksheets: Bring each source sheet into one destination workbook as a separate tab. Move or Copy is straightforward when the files are few and you want to preserve sheet layouts.
- Summarize matching ranges: Add, count, average, or otherwise summarize corresponding ranges. Data > Consolidate can do this when the source ranges have compatible layouts.
For example, if January.xlsx and February.xlsx each contain a table with Date, Product, and Units columns, appending the rows creates one transaction list. If each workbook instead has a sales total in cell B5 and you want a combined total, a summary method is more appropriate.
Use Power Query to combine rows from multiple files
Power Query imports, reshapes, and combines data without requiring you to paste every new batch by hand. It is particularly useful when the source workbooks share a consistent structure and new files will arrive regularly. The resulting query can be refreshed after the source files change or new matching files are added.
- Put the source workbooks in one folder. Keep unrelated files and the destination workbook out of that folder, or plan to filter them out during import.
- In Excel for Microsoft 365 on Windows, select Data > Get Data > From File > From Folder.
- Choose the folder and confirm. In the file preview, choose Combine > Combine & Transform Data if you want to inspect and clean the data before loading it.
- In the combine prompt, select the sheet or table that contains the records. Check the sample preview and confirm that the correct object is selected.
- In Power Query Editor, check the column names, data types, and rows. Remove columns or filter rows if needed, then select Home > Close & Load to send the result to Excel.
The exact dialog labels can vary slightly with Excel updates and the choices you make. The key idea is that Power Query uses one file as a sample to interpret the others. That is why consistent worksheets or named tables make the process more reliable. If one workbook calls a column Item and another calls it Product, the combined result may contain two columns rather than recognizing them as the same field.
For data that already sits in separate tables inside one workbook, Power Query can also append queries. In the editor, use Home > Append Queries or Home > Append Queries as New, then select the tables or queries to combine. Choose the new-query option when you want to retain the original queries as separate sources.
Refresh the combined result when files change
A folder-based query is not a live link that updates every cell the moment a source file is edited. It reads the source files when the query is refreshed. To update the result after changing a source workbook or adding another file that meets the query’s criteria, select Data > Refresh All in the destination workbook.
Power Query also stores transformation steps, such as selecting a worksheet, removing unwanted columns, or changing a data type. On a refresh, Excel applies those steps again. This saves repeated manual work, but it does not guarantee that a changed source layout will be interpreted as intended. If a source file renames a sheet or changes its columns, inspect the query and its result after refreshing.
Before sharing the finished workbook, consider whether recipients can access the source folder. The query may need the original files and folder location to refresh. If they only need a static report, you can preserve the loaded results without expecting other users to refresh them, but make clear that those results will no longer update automatically.
Move worksheets into a single workbook
If the goal is to gather complete worksheets rather than combine their records, use Excel’s sheet-moving command. Open the source workbook and the destination workbook. Right-click the worksheet tab you want to move, choose Move or Copy, select the destination workbook in the To book list, and choose where the sheet should appear. Select Create a copy if the original must remain in its source workbook; otherwise the sheet is moved out of it.
Repeat for each sheet and source file. If you choose (new book) in the destination list, Excel creates a new workbook containing the selected sheet. This method usually preserves the sheet’s formatting and layout better than copying a range, but check formulas, chart references, named ranges, and links after moving it. A formula on a moved sheet may still refer to another workbook if it depended on data outside that sheet.
Use descriptive sheet names, especially when several source workbooks contain tabs all named Sheet1 or Summary. Excel requires sheet names in one workbook to be unique, so it may alter a duplicate name or require you to rename it. If the files have many tabs, decide on a naming convention before importing them, such as North_Sales and South_Sales.
Summarize matching ranges with Data > Consolidate
Excel’s Consolidate command is designed to calculate a summary from multiple ranges. It can combine values using a selected function such as Sum, Average, Count, or Max. In the destination worksheet, select the top-left cell for the result, then choose Data > Consolidate. Pick the function, add each source range in the Reference box, and select Add after each range.
If the source ranges have labels, use Top row and Left column to match data by headings rather than relying only on cell positions. For instance, monthly worksheets with product names down the left side can be consolidated by row labels. If the layouts are identical and labels are not being used, Excel can consolidate by position. Select Create links to source data if you want the summary to include links back to the source cells.
This command is useful for compatible summary blocks, but it is not a general-purpose way to combine transaction lists. It does not append every source row into a normalized table. If one range has different labels, missing categories, or an inconsistent layout, check the result carefully or reshape the source data first with Power Query.
Prepare files and prevent common problems
Most consolidation errors come from inconsistent source files rather than from the act of combining them. A short preparation pass can prevent missing records, duplicate headings, and misleading totals.
- Standardize the structure: Use the same column names and meanings across files. A column should not mean Units in one workbook and Revenue in another.
- Use one header row: Avoid title rows, blank rows, merged cells, and subtotals above or inside the data table. These can be imported as records or disrupt column detection.
- Check data types: Dates, numbers, and identifiers should be consistent. A product code such as 00125 may need to remain text so Excel does not drop leading zeros.
- Look for duplicates: Combining files does not automatically know whether repeated rows are accidental. Define what makes a record unique and remove duplicates only when that rule is clear.
- Filter the folder contents: Exclude old versions, temporary files, unrelated workbooks, and the output workbook. Otherwise, a query may import data you did not intend to include.
- Keep a source copy: Preserve the original workbooks until you have checked row counts, totals, and a sample of the combined records.
A common mistake is assuming that files with similar appearances have identical structure. One workbook may have a hidden row, a different table name, or a new column. After combining, compare the number of source files and records with the output, and investigate any unexpected blanks or errors before relying on the result.
Choose the method that fits your workload
For a one-time job with a few worksheets, Move or Copy is often the quickest choice. For a repeated monthly or weekly process, Power Query usually saves effort because it records the transformation and can refresh from a folder. For a set of similarly laid-out summary ranges that need to be added or averaged, Data > Consolidate may be a better fit.
There is no need to force every task into one method. A practical workflow might use Power Query to append transaction rows, then a PivotTable to summarize the combined table by month or department. Another workflow might copy a few presentation sheets into a reporting workbook while keeping the detailed source data separate. The method should follow the intended output, not merely the fact that there are multiple files.
For very large datasets or processes used by many people, consider whether a database or a managed reporting system would be more suitable than a collection of workbooks. Excel can handle many everyday consolidation tasks, but workbook size, calculation complexity, and the need for reliable multi-user data entry can become limits. Keep source data, transformation steps, and final reporting distinct where possible.
Excel on Mac and Excel for the web
The menu paths above describe Excel for Microsoft 365 on Windows. Excel for Mac includes Power Query capabilities, but available connectors and authoring features can differ by version. In particular, do not assume that the Windows From Folder workflow is available in every Mac build. Check the Get Data options in your installed version; if the needed connector is absent, use a supported import route or perform the folder query in Excel for Windows.
Excel for the web is useful for viewing and editing many workbooks, but local-folder import and some query-authoring workflows are desktop-oriented. A workbook that already contains a query may have different refresh options depending on its data source and account environment. If the process depends on combining files from a local folder, set it up in the desktop version and verify the refresh behavior in the environment where the workbook will be used.
Frequently Asked Questions
Can I combine multiple Excel files into one sheet?
Yes. If the files contain the same kind of records with matching columns, use Power Query to append their rows into one table. If the structures differ substantially, standardize them or transform each source before combining.
Will Power Query update automatically when I add a workbook?
Not immediately. Add the file to the folder covered by the query, then refresh the destination workbook with Data > Refresh All. Check that the new file matches the query’s expected sheet, table, and columns.
Can I combine workbooks without opening each one?
For compatible files, a folder-based Power Query can read and combine them without manually opening and copying each workbook. You still need to select the correct source object, review the transformation, and refresh the query when the files change.
Does Data > Consolidate merge all rows?
No. It summarizes corresponding ranges using a function such as Sum or Average, optionally matching labels. To stack transaction records from several files into one list, use Power Query or another row-append method.
How do I combine workbooks but keep their formatting?
Use Move or Copy to bring entire worksheets into a destination workbook. Copying cells alone may not preserve every sheet-level feature, and moved sheets can still contain references to other workbooks, so inspect formulas and links afterward.
Related
- Goal Seek in Excel: Steps, Examples, and Calc Differences
- Building a Heat Map in Excel: Steps, Scales, and Tips
- Binary Excel Solver: How to Build and Solve 0-or-1 Models
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 Clear the AutoComplete Cache in OutlookTo clear every saved recipient suggestion in classic Outlook for Windows, select File > Options > Mail >…
- How to Filter Rows in Google SheetsTo filter rows in Google Sheets, select a cell in your data, choose Data > Create a filter,…
- How to Insert a Check Box in Word 365 and Make It ClickableTo insert a clickable check box in Word 365, turn on the Developer tab, then choose Developer >…
- Excel Remove Duplicates but Keep One Copy in a ListTo remove duplicates in Excel but leave one copy, select your data and choose Data > Remove Duplicates;…
- Excel Not Highlighting Cells When Selected? FixesIf Excel is not visibly highlighting a selected cell, first press Esc to leave edit mode, then click…
- Year Calendar in Excel: Create a 12-Month ViewTo create a year calendar in Excel, either open File > New and search for a calendar template,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.