iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To retrieve data from several Excel workbooks, use Power Query’s From Folder connector for files stored locally, or the SharePoint Folder workflow for work or school files in SharePoint or OneDrive. Combine the files, choose the right sheet or table, and then either append rows into one longer table or merge tables to bring back fields that match a shared key.
Choose between stacking rows and looking up matching values
First decide what “retrieve values” means for your task. If each workbook contains more records of the same kind, combine the records into one table. If you have a key—such as an ID or account number—and want fields associated with matching keys, merge the relevant tables.
| Need | Power Query operation | What it does |
|---|---|---|
| Put the records from multiple workbooks into one longer table | Append | Adds rows from one query after another. Columns are aligned by header name, so columns in different orders can still line up. A column missing from a source can appear with null values. |
| Find records with the same key and bring selected fields across | Merge | Matches tables on a common column value. You can expand the merged result to include the fields you need. |
Folder combine is the ingestion method for bringing in many similarly structured workbooks; it is not a substitute for choosing append or merge based on the result you need. Microsoft explains these operations in its append queries and merge queries overview guidance.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Combine workbooks in a local folder
- Prepare the folder. Put only the workbooks you intend to import in a dedicated folder. Similar files should use consistent column headers, data types, and number of columns. Column order may differ because Power Query matches columns by name.
- Open the folder connector. In Excel, select Data > Get Data > From File > From Folder.
- Review the file list. Filter by extension or folder path if needed. Check that unrelated workbooks are not included before combining.
- Combine the files. Choose the combine option. Power Query uses a sample workbook to establish extraction steps and applies those steps to the other files.
- Select the workbook object. Choose the intended sheet, table, or named range in the sample/navigation experience. Workbooks can contain several objects, so confirm you have selected the one holding the records you want.
- Transform and load. Review and clean the combined data in Power Query, then load it into Excel. If your goal is matching fields rather than one long table, use the combined query as appropriate and merge it with the table containing the keys.
For a single workbook rather than a folder, select Data > Get Data > From File > From Excel Workbook, browse to the file, and select the relevant object in Navigator. Microsoft documents the combine files workflow and the Excel workbook connector.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Use SharePoint or OneDrive work files
For work or school files stored in SharePoint or OneDrive, use the SharePoint Folder workflow to locate the target folder and combine its contents. You can also select one file’s binary value to navigate into that workbook by itself. Microsoft notes that SharePoint.Contents is optimal in environments with a large number of files. See Microsoft’s SharePoint Folder connector documentation for the applicable connection flow.
Keep the query reliable as files change
Power Query stores transformation steps and reruns them when you refresh the query. Microsoft describes refresh as bringing in additions, changes, and deletions from external sources. For a folder combine, the extraction function is based on a sample file, so new workbooks need to remain compatible with the object and structure used to create the query. Review the output after changing source files or adding files with a different layout. See Microsoft’s refresh guidance.
- Keep unrelated files out of the source folder, or filter them from the file list.
- Keep headers, data types, and column count consistent where possible; a changed layout can affect sample-driven extraction.
- Check that newly added workbooks contain the expected sheet, table, or named range.
- Inspect the combined output for nulls or unexpected rows before using it downstream.
Check Excel version and workbook format
Power Query is available in Excel for Windows, Mac, and the web, but capabilities can vary by host and deployment. Confirm the connector and interface available in your Excel environment before following exact menu steps. Microsoft also notes that legacy .xls and .xlsb workbooks can require the Access Database Engine OLEDB provider. That provider cannot be installed in cloud service environments; a gateway with ACE may be needed in those cases. Consult Microsoft’s Excel connector requirements and Power Query overview for current platform details.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteWhen to standardize the source workbooks first
Folder combine works best when the files share a predictable structure. If workbooks have different layouts, inconsistent object names, or no stable key for matching, inspect and standardize them before relying on a consolidated result. Otherwise, the sample-based extraction may not represent every file, and a merge may not identify the intended corresponding records.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
