In Excel for Microsoft 365 on Windows, choose Data > Get Data > From File > From Folder, select the folder, and choose Combine & Transform Data. Power Query can append the records from compatible files into one query, which you can load to Excel and refresh when the folder’s contents change.
What combining files means
Power Query’s folder workflow is designed for a recurring set of files that share a broadly consistent layout. For example, if each monthly sales workbook has the same columns, Power Query can read the files and stack their rows into a single result. The source files remain where they are; the query creates a combined view rather than editing or physically joining the originals.
“Combine” here usually means append: rows from one file are placed below rows from another. It does not match records by a shared customer ID or other key. That second task is a merge, the Power Query equivalent of joining tables, and it is a different operation.
The folder connector is useful for monthly reports, exported transactions, survey responses, and other repeated extracts. Instead of opening each file and copying its rows into a master workbook, you define the import and cleanup steps once. Refreshing reruns those steps over the files Power Query finds in the folder.
Prepare the folder and files
A little preparation reduces errors later. Put the files you intend to combine in a dedicated folder, and make their structures as consistent as practical. They should have the same kind of data in the same columns, with compatible headers and data types. For example, a Date column should represent dates in every file, not dates in some files and explanatory text in others.
- Use one consistent header row, preferably with the same spelling and order in each file.
- Remove unrelated documents, old exports, and temporary copies from the folder or its subfolders.
- Check that each workbook or text file contains the intended data, not a cover sheet or a differently structured report.
- Keep the folder in a stable location. Moving or renaming it can break the query’s source path.
Files do not always need identical names, and the columns do not necessarily need to appear in the same order. However, consistent names and layouts make the combine process easier to inspect and maintain. If a file has extra columns, missing columns, a different worksheet name, or a different header row, review the result carefully rather than assuming Power Query will interpret it as intended.
Combine files step by step
- In Excel, open the workbook where you want the combined result. Select Data > Get Data > From File > From Folder.
- Browse to the folder and select Open or OK, depending on the dialog. Excel displays a preview of the files it found. Inspect the names and paths to confirm the folder is the right one.
- Choose Combine & Transform Data to open the combine setup and Power Query Editor. Combine & Load is a quicker route when the files are already clean and you do not need to inspect or adjust the transformations first.
- In the combine dialog, choose the sample file and the relevant object, such as a worksheet or Excel table. The sample helps Power Query determine how to interpret the contents of the other files. Select the correct item if a workbook contains several sheets or tables.
- Review the combined preview in Power Query Editor. Check the headers, rows, and data types. Make any needed cleanup, such as removing blank rows or unwanted columns, and confirm that the first row is treated as headers if that is how the source data is arranged.
- Select Home > Close & Load to send the result to Excel. To choose a destination or load as a connection, use the Close & Load menu and choose Close & Load To.
Excel creates a query and helper queries or steps to apply the sample-file interpretation across the files. Names and generated steps can vary with the source type and Excel build. The important result is a repeatable query, not a one-time copy-and-paste operation.
How Power Query combines the files
The folder connection first produces a list of files and their properties, including items such as file name, extension, path, and binary content. Power Query then applies a transformation to the contents, based on the sample file and object you selected, and appends the resulting rows. This is why the sample matters: it establishes the pattern used to read the rest of the files.
The query may include steps that promote headers, change data types, expand the file contents, or filter files. You can inspect and edit these steps in the Applied Steps pane in Power Query Editor. If you want to retain each source file’s name in the output for tracing, make sure the query keeps or adds that file-name information before it is discarded during expansion. That can help identify which export contributed a questionable row.
Power Query can combine common file types, including Excel workbooks and text or CSV files, but the files still need a compatible interpretation. A folder containing a mix of unrelated formats and layouts is not automatically a well-formed dataset. A sensible approach is to use separate folders and queries for different file types or report designs.
Refresh the combined result
When new compatible files are added to the folder, select Data > Refresh All to rerun the query. You can also right-click the loaded query table and select Refresh. If a source file is edited, refresh to bring its current contents into the result. Refresh does not rewrite the original files; it updates the query output in the workbook.
Refreshing is not the same as automatically monitoring a folder every second. Excel updates the result when you refresh, or according to refresh settings you configure where available. You can review query settings through Data > Queries & Connections, then open the query’s properties. For a workbook used regularly, a refresh-on-open setting may be convenient, but check that the source folder is accessible whenever the workbook opens.
A refresh can fail if the folder has moved, a file is locked or unavailable, or a new file no longer matches the expected structure. After adding a new reporting period, review the refreshed row count and a few records. A successful refresh only means the query ran; it does not prove every source row was interpreted correctly.
Common problems and practical fixes
- Unexpected files appear in the result: Check the file list and any filtering steps. A broad folder location may contain backups, archives, or unrelated exports. Narrow the source folder or filter by extension, name, or path in Power Query.
- Some rows are missing or columns look wrong: Inspect the sample file and the selected sheet or table. Confirm that all files use the same header row and that the relevant data is in the same object. A worksheet with a title above the headers can be interpreted differently from a clean table.
- New columns do not show up: The query’s generated expansion and transformation steps may reflect the sample structure. Inspect those steps and update the query for the new schema. A change to a source file does not guarantee that the query will automatically redesign every downstream step.
- Dates or numbers turn into errors: Regional formats can differ. For example, a date written with day and month positions reversed may be interpreted incorrectly. In Power Query, set the appropriate data type and, where necessary, use the locale-aware type conversion option rather than relying on automatic detection.
- Duplicate records appear: Power Query appends what it reads; it does not know whether two rows represent the same transaction. Look for duplicate source exports or overlapping date ranges. Remove duplicates only when you have a reliable definition of what makes a record unique.
- Refresh cannot find a file: Verify the folder path, permissions, and whether the source is available. A path to a local drive may not work for another person who opens the workbook on a different computer.
One frequent misconception is that adding a file with a different layout will be harmless because Power Query is “combining” the folder. The query follows defined transformation steps; it does not infer the meaning of every new report. Treat structural changes as a reason to inspect and possibly revise the query.
Append versus merge, and choosing the right approach
Use the folder combine workflow when files contain successive batches of the same kind of records, such as January, February, and March sales exports. Appending stacks those records into a longer table. Use Merge Queries when you need to match two tables using a shared field, such as adding a product category to sales rows by matching Product ID.
If the files have markedly different layouts, consider transforming each layout into a consistent table first, then appending the normalized results. For a small, one-off task, manual copy and paste may feel quicker, but it is easy to omit a file or repeat rows. For a recurring task, a Power Query process is generally easier to refresh and audit once its steps are correct.
Platform and file-location considerations
The menu path above is for Excel for Microsoft 365 on Windows. Power Query features and supported data sources vary across Excel for Mac and Excel for the web, and the Windows folder-connector steps should not be assumed to appear identically in those versions. On a Mac, check the options under Data > Get Data in the installed Excel build. Excel for the web is not a like-for-like replacement for creating every desktop folder query.
For shared use, consider where the folder lives and who needs access. A query that points to a local path such as a user-specific drive may refresh only on that computer. If colleagues need to refresh it, use a shared location that their Excel setup and permissions can reach, and confirm the query works for those users. Folder access and file permissions still apply even though the data is displayed in Excel.
Frequently Asked Questions
How do I combine multiple Excel files into one workbook? Put compatible files in a folder, then in Excel for Windows choose Data > Get Data > From File > From Folder. Select Combine & Transform Data, choose the appropriate worksheet or table, review the result, and use Close & Load.
Does Power Query combine files by adding rows or columns? The standard combine-files workflow appends rows from the files into one table. It does not place each file side by side or match records by an ID. To match tables using a key, use Merge Queries instead.
Will new files be included automatically? They can be included the next time you refresh, provided they are in the query’s folder scope and follow a compatible structure. Excel does not necessarily update the output the moment a file is copied into the folder.
Can I combine files with different column names? It may be possible, but the result depends on the query’s transformations and how the columns are expanded and named. Standardize the headers where possible, or edit the query to map the different layouts deliberately. Always inspect the result after a schema change.
Can I combine CSV files from a folder? Yes. Use the same From Folder entry point and select the appropriate sample file and parsing options. Confirm that delimiters, encoding, headers, and regional number or date formats are consistent across the CSV files.
Related
- Excel MIN and MAX Dates: Find the Earliest and Latest
- Excel Merge Shortcut: How to Merge Cells in Microsoft 365
- Excel Merge Shortcut: Keys, Options, and Safer Methods
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Row Height Limit: The 409-Point MaximumThe maximum row height in Excel for Microsoft 365 on Windows is 409 points. To set a row’s…
- Excel Protect Sheet vs Protect Workbook: Key DifferencesProtect Sheet controls what people can change on an individual worksheet; Protect Workbook protects the workbook’s sheet structure,…
- How to Add a Draft 2 Watermark in Word on Every PageTo add a DRAFT 2 watermark in Word for Microsoft 365 on Windows, open the document and choose…
- Google Debt Snowball Spreadsheet: Build One in SheetsYes. You can build a debt snowball spreadsheet in Google Sheets by listing each debt, sorting debts from…
- How to Protect a Sheet in Excel and Set PermissionsIn Excel for Microsoft 365 on Windows, open the worksheet, choose Review > Protect Sheet, select which actions…
- How to Archive Emails by Date Range in OutlookTo archive Outlook emails from a particular date range, search for messages received within the dates, check the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.