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.

Wikipedia_folder

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:

Wikipedia_folder_namemanager

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:

Wikipedia_folder_MyFiles     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:

Wikipedia_folder_foldername

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.

Wikipedia_folder_firstresult

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.

Wikipedia_folder_formulacopy

The results will look like this:

Wikipedia_folder_1stfewresults-SNIP-ZOOM

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:

Wikipedia_folder_errormessage

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:

Wikipedia_folder_foldername_Park3

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!

About excelesquire

NYC attorney and Excel enthusiast.
This entry was posted in eDiscovery, Everybody. Bookmark the permalink.

5 Responses to Get Excel to List the Files in a Folder

  1. bruce Rising says:

    Seems to work on a new file I created. But when I try to place it into another existing file (*.xlsm), it only generates errors. I recreated it in the existing file, but can’t generate a file list. I can in an xlsx file

    Like

  2. excelesquire says:

    Thanks for your comment Bruce. It’s true that you need to save the file as a macro-enabled (.xlsm) file, but I’m not sure why you’re getting error messages. I’ll investigate and get back to you.

    Like

  3. MF says:

    Interesting FILES function. May I know what the function do?

    Like

  4. Pingback: Get Excel to Automatically Launch Documents from an Exhibit List, Privilege Log, or Hot Documents List | Excel Esquire

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s