Search Excel Files in a Folder for a Cell Value and Return File Path
Question details
The user wants to find a specific text or value stored inside the cells of multiple workbooks within a folder, and output the exact file path of the matching workbook.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching through a directory of Excel workbooks to locate specific internal data rather than relying on file names for identification.
- Observed behavior
- The user needs to retrieve the exact file path next to the searched value upon a successful match, or display N/A if no match is found across the folder's workbooks.
Ensure all the Excel files you want to search are placed in a single, accessible folder, and copy the exact folder path to your clipboard before beginning.
Use Power Query to Connect to the Folder and Filter Cell Values
Power Query allows you to connect to an entire directory, combine the workbook contents, and filter for your specific identifier to retrieve its file path.
This method establishes a dynamic connection to your folder. If you add new files or change your search criteria, you can easily update the results without rebuilding the entire query.
Open Excel, go to the Data tab on the ribbon, click Get Data > From File > From Folder. Paste the folder path containing your workbooks and click OK.
In the preview window, click Transform Data. Once the Power Query Editor opens, click the double-arrow Combine Files icon on the Content column header to extract the data from all listed workbooks.
Locate the column that contains the cell values you want to search. Click the filter dropdown on that column's header, select Text Filters > Equals, and input your target search value.
Remove any unnecessary data columns by selecting them, right-clicking, and choosing Remove Columns. Ensure you keep the column containing your search value and the 'Folder Path' or 'Name' column.
Click Close & Load on the Home tab of the Power Query Editor. The matched file paths will be loaded into a new Excel worksheet. Simply click Refresh on the Data tab whenever your folder contents change.
Utilize a File-List Excel Add-in
If you prefer not to configure Power Query, installing a dedicated add-in can automate the folder search process directly from the Excel interface.
Try WPS Office for Your Spreadsheet and Data Needs
While advanced Power Query folder searches are native to Microsoft Excel, WPS Office offers a completely free, highly compatible alternative for managing, editing, and analyzing your daily spreadsheet data. Enjoy a lightweight suite that handles your workbooks flawlessly.
- 1. Download WPS Office: Visit the official WPS website to download and install the free office suite on your device.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheets and seamlessly open your existing .xlsx files without worrying about formatting issues.
- 3. Manage Data Easily: Use the familiar Data tab to sort, filter, and analyze your spreadsheet data efficiently.
Frequently Asked Questions
Can I dynamically change the folder path without rebuilding the Power Query?
Yes. You can type the folder path into a specific Excel cell, define it as a named range, and use Power Query's Advanced Editor to reference that named range. This allows the query to update dynamically when you change the cell value and hit Refresh.
What happens if the search value is not found in any workbook in the folder?
By default, the query will return an empty table. To display 'N/A' alongside your original list of search values when no match is found, you can merge your original search list query with the folder query using a Left Outer Join in Power Query.
Does this method search all subfolders within the specified directory?
Yes, when using the 'Get Data From Folder' option in Power Query, it automatically extracts files from both the main folder and all its subfolders. You can filter the 'Folder Path' column if you wish to exclude certain subfolders from your search.
