Hello, Excellers have you come here for some more #formulafriday fun. Good. Me too!. Today let’s look at a really cool way to generate a list of files in a folder without any macros, coding or VBA at all. So this blog post is part of my Formula Friday series. That must mean that we use an actual Excel Formula to carry out this exercise.

Well, that is correct. We are going to use a function or formula from the early days of Excel. it is the Files function. Never heard of it?. Don’t worry. A lot of Excel users haven’t.

What is the Files Function?

The Files is a formula from early versions of Excel and whilst this does not work in cells anymore, it actually works with named ranges!. If you want to read my blog posts on Named Ranges then you can check out the links below.

Formula Friday – 3 Reasons To Use Named Ranges In Your Excel Formulas

Formula Friday – 2 Ways To Create A Dynamic Named Range In Excel

So, back to the Files function. We can use this little function to get a list of files in a folder. I find this really useful if I have a large number files and just need to list them all really quickly on a regular basis. In my example today I have a folder on my desktop called Excel Reports. We will extract all the files in this folder.

Follow The Instruction Step by Step. – Part 1.

1. Enter the full file path of the folder you want to list the Excel files from. I have done this in cell A1 of a new Excel worksheet. C:\Users\EXCEL\Excel Reports\*
2. Go to the Formula Tab | Select the Define Name option.
3. Once you have the New Name Dialog Box open then enter the following details into it.
4. Name: FileNamesList (or anything else you want to identify the named range by).
5. Scope: Workbook
6. Refers to: =FILES(Sheet1!$A$1)
7. Nearly there guys.

Getting The List of Files From Your Folder. Part 2.

1. We need to combine the named range we created in the first part of the solution above. FileNamesList. Combining this with the INDEX function gives us our full formula solution. Put the cursor in the first cell where you want the list of files to start. In this example, I am choosing cell B5.
2. Type the following formula into the first cell. =IFERROR(INDEX(FileNamesList,ROW()-2),””)
3. Drag the formula down to get a list of files.

As we specified that we want to extract ALL files names in this formula as we used the * or wildcard in the named range. If we want to just list the Excel files, then we can change the * to .xlsx or if you want to list PDF’s, then.PDF and so on so forth.

That’s it, how to extract a list of files in a folder. Have you used this formula before?. Share how you have in the comments below.

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Formula Friday series. Click on the link below

How To Excel At Excel – Formula Friday Blog Posts.