How to Get the List of File Names in a Folder in Excel (without VBA)

In this video, I will show you how to get a list of file names from folders into Excel (without the use of any VBA or coding).

You can use this technique to fetch all the file names from a folder and list that in Excel.

This Excel trick uses an old Excel Function - FILES.

FILES function can be used to get the list of all the file names (or specific file names) in a specific folder in Excel. It an old excel function that does not work in the worksheet, but still works in named ranges in Excel.

All you need is this formula with the folder address.

To get the folder address, save the excel file in the same folder and use this formula - =SUBSTITUTE(CELL("filename"),RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("@",SUBSTITUTE(CELL("filename"),"\","@",LEN(CELL("filename"))-LEN(SUBSTITUTE(CELL("filename"),"\",""))),1)),"*")

One of the good things about this method is that it allows you to get specific files from the folder. For example, if you want to only get Excel files or only Word Files, or Only files with a specific extension, you can do that using this method.

Here is a step-by-step written tutorial (which also shows how to do this using VBA):



⚙️ Gear I Recommend:

Note: Some of these links here are affiliate links!

#Excel #ExcelTips #ExcelTutorial

[omkarpolekar]

There is a better way to do it. hit Ctrl + A (To selct all) in the folder which contains all files/folders the list of which you want. Press Shift & right click on any of the files/folders. click on 'copy as path' - Go to excel 2013 or later version - paste in cell A1 there. It will paste the whole path of all selected files in column A. In the cell B1 write only the file name of the first file/folder of your list. In cell B2 use Flash fill option or simply hit ctrl + E which will generate all the file names of the whole file/folder list. (If it doesn't then do same for B2 also & then press ctrl + E in B3 which will bring up the desired result)

[Fabiano]

I just wanted to say a big thank you for such a clearly explained video. Simply brilliant!

[sleepot_]

Thank you!
I looked all over for help with this and your method, though long and winding as it is, is the only one that actually worked on my computer without issues.

[paulmurray]

This was extremely useful. I appreciate the clear presentation of the topic as well.

[safaboi]

Thank you so much...you saved me hours and hours processing thousands and thousands of files for my thesis....THANKS!

[nboorla]

You may just have saved my job with this! Thank you so much you've found yourself another subscriber!

[shambhalachakra]

Such great advise and to the point! Thank you so much for sharing your expertise. This will help me so much of my time from now. Greatly appreciated :)

[uavrecon]

This worked great for my needs. Quick and clear video, thank you Sir.

[madhavichowdhary]

It was very precise and very well explained. Brilliant ! Thank you

[isqhdjj]

Brilliant! This is the most - straight-forward method of listing files in a folder and the biggest time-saver. If you ever get a chance to update (add to this method), I would love to see this expanded to help create a "link" to the file itself in the workbook. So, the resulting workbook can be used as a 'true IDEX of the folder contents, and when a user opens the workbook, s/he can readily "open" a give file by just clicking on the 'link' within the Excel file.) /I've done 'this" many times over my year, - using different methods/tools; but Using the formulas/method in this Video, really saves a lot of work. Best Regards. and a big THANK YOU for posting this.

[shushanknaik]

Thank you so much amit, this video helps me to copy all my wedding files name very quickly.thanks once again.

[lexhoge]

Just great, very simple and easy to manage without programming!!! :-)

[playchompoo]

Big thanks, just one for question if we have subfolder? please advise

[premchandgada]

Very nicely explained, and useful. Thank you.

[rhythmapprentice]

Awesome video! Thank you so much for helping me find a shortcut!

[emadkawekji]

Brilliant!!! It is very helpful. Thank you very much.

[sitaramarajusagi]

I am immensely thankful for this video with great information.. I have many files named in Telugu Language and I wanted to have a database of files I had on different subjects in Telugu. This video Thanks once again.

[yahya-gtlw]

Thank you brother
Good tutorial
Thank you again.

[WildanKurniadiCode]

Thank you so much sir! you're a life saviour.. god bless you

[alexsepel]

Thank you very much, just what I was looking for :)