How to Get a List of all Files in a Folder and Subfolders into Excel

Introduction

In this tutorial, you will learn how to create a comprehensive list of all files in a specific folder and its subfolders using Excel. This process utilizes Power Query, making it an efficient way to manage your file data. Whether you're organizing project files or simply tracking your document library, this guide will help streamline the task.

Step 1: Prepare Your Environment

• Ensure you have Excel installed on your computer.
• If you are using Excel 2010 or 2013:
  • Download and install the Power Query add-in from the Microsoft website: Download Power Query.
• Open Excel after installation to access Power Query.

Step 2: Launch Power Query

• Open a new or existing Excel workbook.
• Navigate to the Power Query tab in the ribbon.
• Click on "From File" and then select "From Folder."

Step 3: Select the Folder

• In the dialog box that appears, browse to the folder containing the files you want to list.
• Click "OK" after selecting the folder.

Step 4: Review the File List

• Power Query will display a preview of the files in the selected folder.
• You will see columns such as Name, Extension, Date modified, and Size.
• Review the list to ensure it includes the data you need.

Step 5: Include Subfolders

• To include files from subfolders, check the option labeled "Include subfolders" in the Power Query interface.
• This will expand the search to all files within the selected directory and its subdirectories.

Step 6: Load Data into Excel

• Once satisfied with the file list, click "Close & Load" in the Power Query window.
• The data will be loaded into a new worksheet in Excel.

Step 7: Format and Analyze the Data

• After loading, you can format the data as needed:
  • Use Excel’s sorting and filtering features to organize your file list.
  • Create charts or graphs if necessary for visual representation.
• Consider saving your workbook for future reference.

Conclusion

You have successfully created a list of all files in a folder and its subfolders using Excel's Power Query. This method can save you time and help you manage your files more effectively. As a next step, explore additional Power Query features to further enhance your data management capabilities.