Get Excel to List the Files in a Folder

There is a particular folder on my hard drive with several dozen files in it. I wish I had a list of those files so that I could email them, or create an exhibit list, etc.

The following routine will make Excel spit out all those filenames in a snap:

Step 1:  Select cell A1 in a new worksheet and go the Formulas Tab.  Select the button called Name Manager:

Under Name, type MyFiles.  The “Refers to” box should already contain “Sheet1!$A$1” if your cursor is in cell A1. Put this in parentheses and type =FILES in front of it, like so:

     Now hit OK.

Step 2:  Copy the folder address you want to index and paste it into cell A1.

Also, add \* at the end of the file path:

MyFiles is now pointing to the file path in cell A1.

Step 3:  Put the following function in cell A3:

=INDEX(MyFiles,ROW()-2)

The INDEX formula tells Excel to pull filenames from MyFiles, which is the folder referenced in A1. The formula in A3 pulls the first value because the current row is 3, so ROW()-2 =1.

Step 4:  Copy the formula down several rows. Notice that the formula looks exactly the same in each row, but since the ROW() function evaluates the current row number, each new formula pulls the next filename.

The results will look like this:

Step 5: Copy the formula down about as many rows as there are files in the folder, until you begin to get error messages.  That will mean that you’ve reached the end of the files:

That’s it!  

Save the workbook with a snappy name like “Excel Esquire Directory Listing Miracle File.xlsx”

Here’s a few additional tips to make the most of this routine:

Tip 1: You can adjust the wildcard in cell A1 to pull only filenames with certain characteristics.  For example, putting *.doc* at the end would pull only the files with a .doc or .docx extension.

I want to pull the documents that have the word Park in them, so I adjust the wildcard to read \*Park*, with the following results:

Tip 2: You can change the file path in cell A1 whenever you want and generate a new list of files.

Tip 3: This routine does not pull the filenames from subfolders in the target folder.  You would have to repeat this operation for each subfolder if you wanted to extract those files.

Tip 4: If you recoil at the sight of error messages, try wrapping the INDEX formula in the warm embrace of IFERROR to generate a friendlier message:

=IFERROR(INDEX(MyFiles,ROW()-2,”Congratulations! You’ve reached the end!”)

---

In a future post I’ll explain how to create a hyperlinked document list—in such a list you can click on the document name in a list, and the document will launch automatically.

Click the Follow button to stay tuned, and to learn about CLE-accredited Excel workshops!