Power Query: Get Excel Files From a Folder in Excel

How-to basics · Updated November 20, 2026

To get data from Excel files in a folder, open Excel for Microsoft 365 on Windows and choose Data > Get Data > From File > From Folder. Select the folder, then choose Combine & Transform Data to inspect and combine its files in Power Query before loading the result into Excel.

What Power Query does with a folder

The From Folder connector reads a folder’s file list and exposes information such as each file’s name, extension, path, and binary content. When you combine the files, Power Query applies a set of transformation steps to a sample file and repeats those steps for the other files that match the combination. The output is one query result, which can be loaded to a worksheet or the Excel Data Model.

This is useful for recurring collections: for example, weekly sales workbooks saved into one folder, monthly expense exports, or separate regional reports that share a layout. Instead of opening and copying each workbook by hand, you add new files to the folder and refresh the query. Power Query does not permanently merge or alter the source workbooks; it reads them and produces a separate result.

The key condition is consistency. Files should contain the same kind of data in a similar structure. The same column names and data types are ideal. If one workbook has a different header row, an extra title line, or a differently named worksheet, the combined result may need extra cleanup or may omit that file’s data.

Prepare the files before connecting

Make a dedicated folder for the files that belong in the result. Avoid putting unrelated workbooks, archived versions, or the output workbook in that folder. If the output workbook is included as an input, it can cause confusing results or a circular-looking process when it is refreshed. A separate subfolder for completed or rejected files can help keep the source set controlled.

Before connecting, check a few representative files and confirm that:

  • The data starts in a predictable place, ideally as an Excel table with column headers.
  • Every file uses the same column meanings and broadly the same layout.
  • There are no temporary or lock files, such as files whose names begin with ~$.
  • The files use the formats you intend to include, such as .xlsx or .xlsm, rather than a mix of unrelated formats.

Power Query can often work with ordinary worksheets as well as named Excel tables, but a table gives the data a clear boundary and makes its headers easier to identify. If each source workbook has several sheets or tables, decide which one contains the records you want before combining.

Connect to the folder in Excel

  1. In Excel, open the workbook that will hold the query. Select Data > Get Data > From File > From Folder.
  2. Browse to the folder containing the source workbooks and select Open or OK in the folder picker.
  3. Review the file listing. It shows the files Power Query found, and gives you a chance to inspect names, extensions, and paths before combining.
  4. Select Combine & Transform Data to open the combination process and Power Query Editor. Choose Combine & Load only when the files are already consistent and you do not need to inspect or adjust the imported data first.
  5. If prompted, choose the object to extract from the sample workbook, such as a worksheet or table. Confirm the preview and select OK.
  6. In Power Query Editor, review the combined rows and apply any needed cleanup. Then choose Home > Close & Load, or use the Close & Load options to send the result to a particular destination.

The exact wording or placement of buttons can vary slightly between Excel builds, but the folder connector is under Get Data and From File. The preview is not a promise that every workbook will match it: inspect the query result and, if possible, test it with files that represent the variations you expect.

How the combine process works

Power Query creates a main folder query and supporting queries or functions that describe how to extract data from a sample file. The main query applies that extraction to each eligible file and appends the resulting rows. In the editor, the final result may look like an ordinary table, but the supporting steps are what let a refresh repeat the process.

For instance, imagine a folder containing North_January.xlsx and North_February.xlsx, each with a table called Orders and columns for OrderDate, Product, and Amount. Combining the files appends the records from both tables into one result. If a later file adds a new column, renames Product to Item, or stores the records on a different sheet, the existing extraction steps may not handle it as intended. Consistency is more important than matching filenames.

Power Query may also include source-file information in the combined output, depending on the steps used. Keeping a filename column can be valuable for tracing an unusual record back to its workbook. If it is not present, retain or add a source name during transformation when auditability matters.

Filter files and shape the combined data

Before or after combining, use the folder listing to limit which files are processed. Filtering by Extension can exclude PDFs or text files; filtering by Name can select a predictable naming pattern; and filtering by Folder Path can narrow a folder that contains subfolders. A filter can also exclude temporary files beginning with ~$ so Excel’s lock files are not treated as real workbooks.

In Power Query Editor, common cleanup steps include promoting the correct row to headers, removing blank rows, setting data types, renaming columns, and removing columns that are not needed. Check dates, decimal amounts, and identifiers carefully. A product code such as 00124 should usually remain text if its leading zeros matter; treating it as a number can remove them.

Apply transformations in a sensible order. First filter out files that do not belong, then choose the relevant sheet or table, then clean and type the resulting columns. If a file has a title row above the data, remove that row before promoting headers. When the query includes multiple worksheets, make sure the sample-file selection and filtering steps select the intended sheet rather than combining unrelated tabs.

Refresh the query and manage file paths

After the query is loaded, add new matching workbooks to the source folder and refresh the result. In Excel, use Data > Refresh All to update queries and other connections in the workbook, or use Refresh on the query’s result table or Queries & Connections pane when available. Refresh reruns the saved steps; it does not automatically correct a changed source layout.

The folder path is part of the connection. If the folder is moved or renamed, the query can report that the path was not found. You can update the source location in the query’s source step in Power Query Editor, or edit the connection’s source settings where available, then refresh. For workbooks used by several people, choose a location that those users can access consistently. A path on one person’s local drive may not exist on another person’s computer.

Refresh behavior also depends on access and permissions. A workbook stored on a network location may require that the user be connected to the network. If credentials or privacy settings are requested, use the appropriate organizational account and follow your organization’s data-handling rules. A successful refresh only means the query could read the files; it does not confirm that the data itself is complete or correct.

Common problems and practical fixes

  • Some files are missing: Check the folder path, extension filters, filename filters, and whether the files are in a subfolder that the query is not including. Confirm that the file is not temporary or locked.
  • The query reports a conversion error: Look for mixed data types, unexpected text in a numeric column, or dates formatted differently between workbooks. Inspect the error row and decide whether to correct the source or transform the column safely.
  • Headers appear as data: The sample workbook may have a title or blank row above the headers. Adjust the sample transformation to remove those rows before promoting headers, then verify that the change works on the other files too.
  • Only one sheet’s data appears: Confirm the selected object in the sample file and the steps that filter the workbook objects. If different files use different sheet names, standardize them or build logic that deliberately handles the variations.
  • New columns do not appear as expected: Inspect the sample-file transformation and any steps that select or reorder columns. A step that explicitly keeps only existing columns can exclude newly added fields until the query is adjusted.
  • Refresh fails after sharing: Check whether the recipient can access the same folder path and has the necessary permissions. Moving the source files to a shared, stable location may be preferable to relying on a local drive.

When a folder query is the right choice

Use From Folder when files are separate because they arrive over time or from different people, but their records need to be analyzed together. It is especially helpful when the same import and cleanup must be repeated. A normal Excel copy-and-paste process may be simpler for a one-time task, but it becomes easy to miss a file or apply inconsistent cleanup when repeated regularly.

If the records already live in one well-maintained workbook, querying a specific table may be clearer than querying a folder. If files have substantially different layouts, a single automatic combine may be brittle; separate queries for each layout, followed by a deliberate append, can be easier to maintain. For files stored in SharePoint or OneDrive for work or school, the SharePoint Folder connector may suit a shared document library better than a local synchronized-folder path, though it requires the appropriate site information and access.

Excel for Mac and Excel for the web

The steps above target Excel for Microsoft 365 on Windows, where the From Folder route is available. Excel for Mac has Power Query features, but connector availability and authoring options can differ by version and update channel. Check Data > Get Data (Power Query) on your Mac for a From Folder option; if it is not offered, create or edit the folder query in Excel for Windows rather than assuming the Windows menu path exists on Mac.

Excel for the web is not a complete substitute for Windows desktop Power Query authoring. Web capabilities for working with and refreshing queries depend on the connection and the Microsoft 365 environment. For a folder query that needs setup, troubleshooting, or changes to its transformations, use the supported desktop version and confirm that the files and connection remain accessible to the people who need to refresh it.

Frequently Asked Questions

Can Power Query combine Excel files with different names?
Yes. Filenames do not have to match if the files are in the selected folder and contain compatible data. You can filter by filename when only a particular naming pattern should be included.

Do all files need the same worksheet name?
Not always, but matching names and layouts make the combination more reliable. The query’s sample-file steps determine which object is extracted. Different sheet names or structures may require additional transformation logic or standardization.

Will new files be included automatically?
They are picked up when the query is refreshed, provided they are in the queried location and pass its filters. Adding a file does not update the loaded table until a refresh runs.

Can I combine files from subfolders?
The folder listing can include files from subfolders, but review the Folder Path column and any filtering steps to confirm what is included. If you only want one level or a particular subfolder, filter the paths deliberately.

Does Power Query change the original workbooks?
No. The query reads the source files and loads a separate result. Transformations affect the query output, not the contents of the original files, unless you separately edit those workbooks.

Related

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.