How to Consolidate Excel Files Into One Workbook

To consolidate Excel files, first decide whether you want to keep each file’s worksheet separate or combine matching rows into one table. For separate sheets, copy the worksheets into a master workbook. For matching tables, use Data > Get Data > From File > From Folder and Power Query to append the files into one refreshable table. Make a backup before you begin, and check that source files use consistent layouts.

What You’ll Need

Microsoft Excel for Microsoft 365 on Windows, ideally with permission to use Power Query.
The Excel workbooks you want to combine, saved in a folder you can access.
A clear decision about the result: separate tabs, one appended table, or a summary of totals.
Consistent column headings and data types if you plan to append rows with Power Query.
A new master workbook saved outside the folder containing the source files, especially if you plan to refresh a folder query.

Step-by-Step

Choose the kind of consolidation you need. If you need one workbook that preserves each source file as a separate worksheet, use the worksheet-copy method in Step 5. If each file contains the same kind of records—for example, monthly sales with the same columns—use Power Query to stack the rows into one table. If you only need totals or another calculation across similarly arranged ranges, Excel’s Consolidate command may be suitable; see Step 6. These approaches produce different results, so choose before you start.
Prepare and back up the source files. Save a copy of the workbooks, then put the files to be combined in one folder. Open a few and confirm that the relevant sheet or table is present in each. For a row append, standardize the column names, remove introductory notes above the header row, and check that dates and numbers are stored consistently. Close the source workbooks before importing them. Do not put the finished master workbook in the same input folder unless you deliberately filter it out; otherwise, a later refresh may import the master as another source.
Start a folder import in Power Query. Create a blank workbook for your consolidated result, then choose Data > Get Data > From File > From Folder. Browse to the folder containing the source workbooks and select it. In the file list window, check that the expected files appear and that temporary or unrelated files are not included. Select Combine & Transform Data to inspect and prepare the data before loading it. The Combine option works best when each workbook has the same type of data in the same named sheet or Excel table.
Select the right sheet or table and review the sample. In the Combine Files dialog, choose the worksheet or table that contains the records. Power Query uses a sample file to determine how to interpret the other files. Check the preview: the first row should be the actual column headings, and the columns should represent the intended fields. If the preview shows blank rows, title text, or the wrong sheet, choose a better sample object or adjust the query before continuing. Select OK to open the combined query in the Power Query Editor.
Clean the combined query before loading it. Confirm that the column names are correct and that each column has the right data type, such as date, whole number, decimal number, or text. Remove columns you do not need by selecting them, right-clicking, and choosing Remove. Filter out blank or unwanted rows using the filter arrows in the column headings. If a source filename column is included, keep it when you need to trace records back to their original file; otherwise, you can remove it. Watch for error values or unexpected nulls, which can indicate that one workbook has a different layout.
Load the appended result into the master workbook. In Power Query Editor, choose Home > Close & Load > Close & Load To. Select Table, then choose whether to load the result to a new worksheet or an existing worksheet. A new worksheet is usually the safest choice for a first load. Select OK and wait for Excel to create the table. Check the row count and scan records from more than one source file to make sure the files were appended rather than merged side by side or omitted.
Copy worksheets when you want separate tabs. Open the master workbook and a source workbook. In the source, right-click the worksheet tab and select Move or Copy. In the To book list, choose the master workbook. Pick the position for the sheet, select Create a copy so the original remains unchanged, and select OK. Repeat for the other worksheets and workbooks. Rename tabs if needed, and check for duplicate names, formulas linked to the original workbooks, or hidden sheets that you also need. Save the master as an Excel Workbook (*.xlsx).
Use Consolidate for a summary of matching ranges, if appropriate. Open the destination sheet and select the cell where the summary should begin. Choose Data > Consolidate. Select a function, such as Sum, then add each source range to the Reference box and select Add. If the ranges have matching labels, select Top row and/or Left column as appropriate so Excel matches items by label rather than relying only on position. Select Create links to source data if you want linked detail rows, then select OK. This creates a summarized result; it does not preserve all source records as one detailed table.
Save and check how updates will work. Save the finished workbook with a clear name and note where the source files are stored. A Power Query folder import can be updated by adding or replacing files in the source folder and choosing Data > Refresh All in the master workbook. Check that the query’s folder path still points to the correct location. Copied worksheets do not update when their original files change, and a Consolidate summary may need to be run again. Keep the backup until you have verified the final result.

Common Mistakes

Mixing files with different layouts: Power Query can append records reliably only when the source objects are compatible. A workbook with a different header row or a renamed column may create extra columns or errors. Standardize the files first, or handle the outlier separately.

Including totals and repeated headers: If every monthly sheet has a total row, appending all rows can make a later total count the subtotals as well as the detail. Remove those rows in the source or filter them out in the query. Similarly, repeated header rows should not be treated as data.

Choosing the wrong object: A workbook may contain several sheets and tables. Selecting a similarly named but irrelevant sheet can produce a plausible-looking, incorrect result. Inspect the preview and confirm it contains the expected fields before loading.

Expecting copied tabs to stay linked: Move or Copy creates a copy of a worksheet; it does not create a live connection to the source workbook. Formulas on the copied sheet may still reference the old workbook. Check formulas and decide whether to retain those links or convert appropriate results to values.

Overwriting or re-importing the master: Saving over a source file can destroy the original, while storing the master among the inputs can cause it to be included in the next folder refresh. Use a separate output location or exclude the master by filtering the file list.

Tips

For recurring monthly or weekly updates, use Power Query rather than repeating manual copy-and-paste. Keep the folder path stable and add only files that follow the established layout.
Convert each source range to an Excel table with Ctrl+T on Windows before importing when practical. Give tables clear names and use the same table name in each workbook. On Mac, use Command+T to create a table.
Keep an identifying field such as month, region, or source filename in the consolidated data. It makes checks, filtering, and tracing a suspicious row much easier.
Check for duplicate records after combining. A file may have been copied into the folder twice, or two source files may overlap. Do not remove duplicates automatically unless you know which fields define a duplicate.
Save a dated copy before changing a query or replacing the source folder. A refresh changes the query output, so retaining a known-good version gives you a straightforward recovery point.
On Excel for Mac, the available Power Query connectors and menu options can vary by Microsoft 365 update. Check Data > Get Data for a From Folder option. If it is unavailable, use a Windows version of Excel for that folder import, or copy the worksheets into a master workbook manually.

Troubleshooting

Some files do not appear: Check that they are in the selected folder, are supported workbook formats, and are not open or temporarily locked. In the folder file list, remove filters that exclude them. If the master is in that folder, check whether the query is filtering it out as intended.

The result has extra columns or many blank values: Compare the column headings in each source file, including spaces and punctuation. Confirm that Power Query selected the same sheet or table structure in each workbook. Correct the source layouts, then choose Data > Refresh All. If the files cannot be standardized, create a separate query for the different layout and combine the results after aligning their columns.

Dates or numbers look wrong: A column may be interpreted as text, or regional date conventions may differ between files. In Power Query Editor, select the column and choose the appropriate data type. If a date such as 03/04/2026 is ambiguous, confirm whether the source means March 4 or April 3 before changing how it is interpreted.

Refresh fails after moving files: The query may still point to the old folder path. Open Data > Queries & Connections, right-click the query, and choose Edit. In Power Query Editor, review the Source step and update it to the current folder, then close and load and refresh again. If the folder is on a network or cloud location, confirm that it is accessible and that you have permission.

The copied worksheet contains broken links: Open the copied sheet and inspect formulas that refer to another workbook. If the original file has moved or been renamed, update the links only after confirming the correct source. If you only need fixed results, copy the relevant cells and use Paste Special > Values, while preserving a separate copy of formulas if they may be needed later.

Frequently Asked Questions

Can I combine several Excel files into one workbook without Power Query?
Yes. Use Move or Copy to add each worksheet as a tab in a master workbook. For a single combined list, you can copy and paste the rows manually, but check headers, duplicates, and row counts carefully.

Does Power Query change or delete my original files?
No. A folder query reads the files and loads a result into the master workbook; it does not normally edit the source workbooks. Keep backups anyway, and avoid changing or deleting files while a refresh is in progress.

Will the combined table update automatically when I add a file?
Not simply because a file was added. Open the master workbook and choose Data > Refresh All to retrieve files from the folder again. The new file must be in the query’s source folder and follow a compatible structure.

Can I combine workbooks that have different column orders?
Power Query matches fields by column names when combining compatible objects, so different column order can be manageable. Inconsistent or missing names can still create separate columns or blanks. Standardize the headers and verify the preview and final output.

What is the difference between Combine Files and Consolidate?
Combine Files with Power Query appends detailed records from multiple files into a table. Data > Consolidate summarizes matching ranges using a function such as Sum. Choose based on whether you need the underlying rows or just an aggregate result.

Can I consolidate files stored on a Mac?
Yes, but the exact Power Query connectors available depend on your Excel for Mac version and update. If From Folder is not offered under Data > Get Data, copy the sheets manually or perform the folder import in Excel for Microsoft 365 on Windows.

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.