Are you spending hours at the end of each month or quarter opening dozens of departmental Excel reports and manually copying and pasting them? Tedious manual entry often leads to missing rows, duplicates, and unforced errors. You can merge every file into a single master sheet with just a few clicks—no VBA macros or coding required.
Quick Summary
- Path: In Excel, go to [Data] > [Get Data] > [From File] > [From Folder]
- Supported Versions: Built into Microsoft 365 and Excel 2016–2024 (Excel 2010 and 2013 require a free add-in)
- Multi-Sheet Merge: Use the Excel.Workbook function and promote the first row to headers
- Auto-Updates: Drop new files into the folder and press ‘Ctrl + Alt + F5’ to refresh instantly
Can You Use Power Query in Your Version of Excel?
Power Query is Excel’s built-in data transformation and combination engine. It automatically structures, cleans, and merges data across multiple workbooks without complex scripts. Think of it as an automated sorting machine that takes incoming mail from different desks and organizes it into a single standardized pile.
Availability depends on your version of Excel. Most modern versions include it by default, requiring no extra installation.
| Support Type | Excel Version | How to Access |
|---|---|---|
| Built-in | Microsoft 365, Excel 2024, Excel 2021, Excel 2019, Excel 2016 | Available directly under the [Data] tab |
| Add-in Required | Excel 2013, Excel 2010 | Install the official Power Query add-in from Microsoft Download Center |
Step-by-Step: How to Merge All Excel Files in a Folder
The simplest workflow is to place every file you want to combine into a dedicated folder and import the entire directory. As long as the files share the same layout, you can easily combine dozens of workbooks at once.
1. Open Excel and click the [Data] tab in the ribbon.
2. Select [Get Data] > [From File] > [From Folder].
3. Browse to the folder containing your Excel files and click Open.
4. In the preview window, open the Combine dropdown and click [Combine & Transform Data].
5. Select the target sheet from the sample file preview. The Power Query Editor will open, merging all files into one continuous dataset.
Related Reading
How to Recover an Unsaved Excel File
How to Mask Data in Excel Instantly Without Formulas
How to Merge Multiple Sheets and Clean Up Headers
If each workbook contains multiple sheets or offset header rows, you can standardize them in the Power Query Editor. Click [Use First Row as Headers (Table.PromoteHeaders)] on the Home ribbon to ensure consistent column headers throughout.
To extract data across all sheets in every workbook at once, add a custom column using Excel.Workbook([Content]), or write the M expression Table.Combine(List.Transform(Excel.Workbook([Content])[Data], each Table.PromoteHeaders(_))) to expand every sheet simultaneously.
Before closing, check for common errors. Mismatched column names or non-numeric values in date or number fields will trigger type conversion errors. Also, filter out temporary files (names starting with ~$) that appear when files are open, as they can break the query.
Why Adding New Files Only Requires a Single Refresh
Once you finish transforming your data, click [Close & Load]. The combined data appears in a new worksheet formatted as an Excel table. The real benefit comes when next month’s files arrive.
Simply save the new file into the target folder, open your master workbook, and click [Refresh All] or press Ctrl + Alt + F5 (or Alt + F5 in Excel for the web). Power Query automatically reruns all transformation steps and appends the new records to the bottom.
People often call this an “instant” merge because updating it takes only one click. Actual run times vary from a few seconds to several minutes depending on file size, row counts, and hardware specs.
Create a dedicated folder right now, drop your Excel reports into it, and click [Data] > [From Folder] to set up your automated pipeline.
Related Articles
- How to Remove Duplicates in Excel Without Losing Data
- The Excel F4 Shortcut: Repeat Your Last Action Instantly
- How to Fill Blank Cells in Excel with a Shortcut
- How to Add Hyphens to Phone Numbers in Excel in 3 Seconds
답글 남기기