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)
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.
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.
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.
Useful video, i need to done this one in macro can it is possbile. Example: every day in drives file will keep on chages on day to day basics, so i want to do a macro can you help me out with that one
Thank you so much for the interesting tutorial. Earlier I was manually copy pasting the file names from the folder to excel sheet. This video is super useful. Just one question. Is it possible to create a link on all the file names in the list so that by just clicking on the file name in the list one can open the file in the folder? Please let me know. Thank you. Best regards.
Hi I followed the steps that you provided, but it is giving me a #name? I have doubled checked all the spelling in my formula. Does this work with all types of files like .jpg ? Thank you for your help.
A MILLION THANKS to you for doing all these such wonderful, excellent, extremely helpful to people like me. I have been looking and wanting to be able to something like this for a long time, and could not find any tutorial on UA-cam. And also, I didn't know how to ask the question. So, this tutorial video has been a "life saver" for me. In addition, your clear, detail, helpful explanation and your right speed in your tutorial are greatly appreciated and acknowledged. Thank you so much again. (I just have a question or, perhaps, is a request. Is there a shortcut or an easier way after getting all file to the worksheet, is there an easier way for me to insert or create a hyperlink. The way I am doing it now is the old fashion way which is click Insert Hyperlink icon then select the file and so on and so on.). Thank you.
Dear brother i use this formula its work but its not taking number from begning.its leaving the first 5 row and than picking the 6th row.please tell me the solution
Awesome! This is exactly what i need :) almost... Is there a way to include the names of folders as well? I also found that in some folders, there doesn't seem to be any logic to the order in the generated array. Why isn't it always alphabetical?
Now that we have this list of file names, can you show how to retrieve data from the workbooks using this list of names as the reference? Example you wanted the data stored in cell A1:A1 for each file.?
I am using Excel 2016 for Mac. I followed your steps exactly, but I get a N/A error? Does the Files() function still work when defining names on a Mac? Any help you can offer is greatly appreciated. Thanks!!
what about subfolders and their content? I have bunch of files inside several folders? I wanted to get their date info so I can easily sort it out in excel. REPLY
Only Filename Displayed i need properties value automatically fill example one folder so many image here then the image name,width,height,image creation date visible on excell
This is Simply brilliant! however i have one question. Please can you help me by showing how can i extract all the file names which are stored in two folders. for example, I have One folder with 10 files and second folder with 10 files, what is the easiest way in getting the 20 file names.
This formula works great and is a huge time saver. I was hoping to expand its usefulness even further. When using this formula is it possible to include "Hyperlink" so that when the list is created the items of the list become live links in the spreadsheet referencing the files location in the folder? The links would be used to launch video files from the spreadsheet. If so this would really help me with my project. Thank you.
hi, good learning video however have one doubt. do we really need to create test file in same folder from where we want file names? I tried without that and it gave me same folder path. is there a way to do without saving test file in original folder?
AMAZING!!!!! Note, for some reason, I had to use the semicolon instead of the comma to separate the formula calls. Maybe because of my language settings? In any case, thanks so much!
will this work for subdirectories? I.e. folder has several folders with files, I would like to get a folder and files. I tried following it but I get nothing. Thanks.
Hello sir. Your tutorial is amazing. Sir I have a doubt in excel. Please tell me how to remove duplicates words in same cell in same line. example in cell A1 has some words " MAA400 MAA400 NEFT TRANSFER". here the words MAA400 are repeated twice . So I want remove duplicates words in same line in same cell.
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 :)
Thanx a lot, I have folder containing more than 5400 files. I tried and able to retrieve only 256 files name. what will be the next option to get full list?
This works great, but do you have a suggestion for gathering further information about the folders? Like sorting through the metadata and having it available to be inserted into a Pivot table?? That'd be amazing!
sir can u plzz tell me, after making list, how we can make same file auto hyperlinks that we can click on the name in list, it will open the file. because i saw in one file but no body teach me how to do. i will be very thank full to you
perfect Love u sir Love u sooooooooooooooooooooooooo my dear you will not know that how much you help me , just please last thing that i want to know , create one index button that i click on that button and file make auto index and add new file name also with existing files those files are come new on that folder and make again auto hyper link
Zachary Ogborn yes sir you are right but to get refresh file name list i need to do again dabble click on cell + button and then list is getting refresh, and plus there is one bug if i add a new file and that file having name that the file go to 1st number while sorting and then list will resort and after i removing 1st file from the folder still exist on cell B1 but this file is no more exist on that folder why this name arrive and those file who are after B1 all name as per folder name exist
Ghulam Murtaza my plan is if i have more folder like 10 and according to these 10 folder i will make file containing 10 folders list in 10 different columns and then i create one indexing button, while clicking on this indexing button automatically arrive all new fresh list of all 10 folders name list with hyperlinks according to newly added file and deleted file, if you suggest me to do this job by creating one macro then i will also tray, if will succeeded to make this file i will flay with happiness in the sky and i will pray for you
Thank you very much sir Each time i view your videos, i learned from them. Today i learn to make list. Its an amazing experience. Love Trump Excel ❤️❤️❤️❤️
At 5:18 is where you lost me. could not see how to "lock" the row and follow next steps. but then I read below on using command line and it worked nicely. but thank you for making the video. You may include some notes or something for the 5:18 mark.
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)
+Omkar Polekar
Worked Fine. But Flash Fill not worked
great
worked perfectly for me - the Trump Excel way didn't work for me. Thank you!
here is a video explaining the method in the above comment: ua-cam.com/video/JN65g-0k534/v-deo.html
This is easy to to use and works without error, but it will not update when we have added more files or when we changed our file names
I just wanted to say a big thank you for such a clearly explained video. Simply brilliant!
Bravo. Can't believe this hidden trick is even existing in UA-cam.
Used this to extract 132 files names. This video saved me an hour.
Yayyy... Glad it helped :)
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.
sir ye to alphabatical sequence se import kr rha hai agr datemodified sequance se imort krne ho to kese krende?
Thank you so much amit,this video helps me to copy all my wedding files name very quickly.thanks once again.
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.
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.
Useful video, i need to done this one in macro can it is possbile. Example: every day in drives file will keep on chages on day to day basics, so i want to do a macro can you help me out with that one
Cool tip! I used the Row function with no argument to increment the formula. That is a little easier.
Wonderful video I loved it
This was extremely useful. I appreciate the clear presentation of the topic as well.
you are a Genius.. many thanks for this precious video
This worked great for my needs. Quick and clear video, thank you Sir.
Awesome trick. Thanks a ton. It worked like a charm
Mr. Bansal: Fantastic!!! A top notch tutorial, made life very easy. Thank you!
So helpful thanks! How can this be done to list folders instead of files?
Sumitji, how can we prepare a seperate list of all songs in excel from a pen drive or CD. Kindly explain.
Does this only works to identity Excel or Docs file, what if I want to get PDF or Sub Folders Path
Thanks for the technique, its saved my time
It was very precise and very well explained. Brilliant ! Thank you
This was super helpful, thank you!
Excellent Video to enhance our understanding. However why should we need to get the seperate list of file names , when they are already in a folder?
Thank you so much for the interesting tutorial. Earlier I was manually copy pasting the file names from the folder to excel sheet. This video is super useful. Just one question. Is it possible to create a link on all the file names in the list so that by just clicking on the file name in the list one can open the file in the folder? Please let me know. Thank you. Best regards.
You may just have saved my job with this! Thank you so much you've found yourself another subscriber!
Clear and precise. Thanks.
Very very useful stuff!!! Thank you!!!!
Thanks my dear. I have a question if I want to get All file names in folder and sub folder ? How ?
Thank you a lot. This will reduce my workload.
how would one make this work with files stored on sharepoint onedrive (not local)
Very nicely explained, and useful. Thank you.
Nice Information and really useful.
great tutorial, you just showed me another way of copying names of files.
You are amazing, man! Thanks!
Awesome video! Thank you so much for helping me find a shortcut!
Thank you brother
Good tutorial
Thank you again.
Dude, this is awesome!!
This is very useful, many thanks to you. I could not get any files when I use it with OneDrive!
VERY NICE...WILL THIS AUTO-UPDATE ON ADDITIONS OF FILES IN THE FOLDER..???
Great Video. Any chance you can show how we can create the link to actual file names which are generated? That would be a great help. Thanks
Hello and thank you very much for this video. If i would want to rename files in the folder consistently, can you please recommend the ways to do it?
great video, thanks. it is possible to get names of folders (not files)?
You saved my team hours of work. Thanks for the video :)
Hi
I followed the steps that you provided, but it is giving me a #name?
I have doubled checked all the spelling in my formula.
Does this work with all types of files like .jpg ?
Thank you for your help.
A MILLION THANKS to you for doing all these such wonderful, excellent, extremely helpful to people like me. I have been looking and wanting to be able to something like this for a long time, and could not find any tutorial on UA-cam. And also, I didn't know how to ask the question. So, this tutorial video has been a "life saver" for me. In addition, your clear, detail, helpful explanation and your right speed in your tutorial are greatly appreciated and acknowledged. Thank you so much again. (I just have a question or, perhaps, is a request. Is there a shortcut or an easier way after getting all file to the worksheet, is there an easier way for me to insert or create a hyperlink. The way I am doing it now is the old fashion way which is click Insert Hyperlink icon then select the file and so on and so on.). Thank you.
Dear brother i use this formula its work but its not taking number from begning.its leaving the first 5 row and than picking the 6th row.please tell me the solution
HI, Can you help why it's not working in 2010 version. I am not getting the formula when I pressed F3
Awesome! This is exactly what i need :) almost... Is there a way to include the names of folders as well?
I also found that in some folders, there doesn't seem to be any logic to the order in the generated array. Why isn't it always alphabetical?
This really helpful!! Thank you so much!!
Now that we have this list of file names, can you show how to retrieve data from the workbooks using this list of names as the reference? Example you wanted the data stored in cell A1:A1 for each file.?
Is there a way to get an excel list of pdf file folders? I need to do a vlookup on the pdf folder names, compared to an excel list of many more names.
Awesome... is it possible to get the file creation date
I am using Excel 2016 for Mac. I followed your steps exactly, but I get a N/A error? Does the Files() function still work when defining names on a Mac? Any help you can offer is greatly appreciated. Thanks!!
Thank you so much sir! you're a life saviour.. god bless you
what about subfolders and their content?
I have bunch of files inside several folders? I wanted to get their date info so I can easily sort it out in excel.
REPLY
I'm on the same issue now. I see no one replied to you in 2 years hehe
Only Filename Displayed i need properties value automatically fill example one folder so many image here then the image name,width,height,image creation date visible on excell
your video are very helpful and thank to you.
This is Simply brilliant! however i have one question. Please can you help me by showing how can i extract all the file names which are stored in two folders. for example, I have One folder with 10 files and second folder with 10 files, what is the easiest way in getting the 20 file names.
This formula works great and is a huge time saver. I was hoping to expand its usefulness even further. When using this formula is it possible to include "Hyperlink" so that when the list is created the items of the list become live links in the spreadsheet referencing the files location in the folder? The links would be used to launch video files from the spreadsheet. If so this would really help me with my project. Thank you.
How to save define name, because every time I open file I has to define formula again, please help
Under what category is the FILES formula in?
what i need is i want to know if the value of cell'file name' is existing in a folder by not using Cntrl F..
thanks in advance
hi, good learning video however have one doubt. do we really need to create test file in same folder from where we want file names? I tried without that and it gave me same folder path. is there a way to do without saving test file in original folder?
The formula also displays all the files other than excel files... How can we get only the excel file list
Hi sir difine name ek baar set karne se nahi horaha he jab bi file open karta hun to pir se difine name karna hota he aisa kyun...????
Fantastic video my friend, a great help
AMAZING!!!!! Note, for some reason, I had to use the semicolon instead of the comma to separate the formula calls. Maybe because of my language settings? In any case, thanks so much!
Hi, thank you for this video its very helpful,but is there a way to make it make it read folders also
Very good tutorial. Thank you very much
Very good. Thanks.
will this work for subdirectories? I.e. folder has several folders with files, I would like to get a folder and files. I tried following it but I get nothing. Thanks.
You are a life saver good sir. Thanks
Thanks, this was really usefull! Now this is giving a list of files in a certain folder. How would I get the folders in this directory as well?
Thank you so mutch, Sir.
Hello sir. Your tutorial is amazing. Sir I have a doubt in excel. Please tell me how to remove duplicates words in same cell in same line.
example in cell A1 has some words
" MAA400 MAA400 NEFT TRANSFER".
here the words MAA400 are repeated twice . So I want remove duplicates words in same line in same cell.
Great Bro I used your method and have easily created the list of my files. thank you so much
Can you please tell if there is a way to extract other information of files like date, authors, etc in other columns
Will this be dynamic?
Hi Sumit, I have multiple folders in a folder, how do we list files for all those together? Thanks in advance.
Brilliant!!! It is very helpful. Thank you very much.
does this not work for multiple folders
Thanks for Simple Video
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 :)
Thank you so much...you saved me hours and hours processing thousands and thousands of files for my thesis....THANKS!
Video is ok but in that folder we have date wise sub folder means how can we do it
Thanx a lot,
I have folder containing more than 5400 files. I tried and able to retrieve only 256 files name. what will be the next option to get full list?
How would you list all these files by created date?
Great technique
Is there a way to only find the numbers of files in the folder or excel sheets in the the folder?
Thank you very much, just what I was looking for :)
This works great, but do you have a suggestion for gathering further information about the folders? Like sorting through the metadata and having it available to be inserted into a Pivot table?? That'd be amazing!
I got it figured out. Thank you for the response.
Hi, how did you dit it? did you manage to do it with VBA? or with excel?
Can we see the latest file name by making some changes to the formula
sir can u plzz tell me, after making list, how we can make same file auto hyperlinks that we can click on the name in list, it will open the file. because i saw in one file but no body teach me how to do. i will be very thank full to you
Ghulam Murtaza In C1 paste this formula and copy it down.
=IF(B1="","",HYPERLINK(LEFT($A$1,LEN($A$1)-1)&B1,B1))
perfect Love u sir Love u sooooooooooooooooooooooooo my dear you will not know that how much you help me , just please last thing that i want to know , create one index button that i click on that button and file make auto index and add new file name also with existing files those files are come new on that folder and make again auto hyper link
Ghulam Murtaza The hyperlinks should automatically update with the files
Zachary Ogborn yes sir you are right but to get refresh file name list i need to do again dabble click on cell + button and then list is getting refresh, and plus there is one bug if i add a new file and that file having name that the file go to 1st number while sorting and then list will resort and after i removing 1st file from the folder still exist on cell B1 but this file is no more exist on that folder why this name arrive and those file who are after B1 all name as per folder name exist
Ghulam Murtaza my plan is if i have more folder like 10 and according to these 10 folder i will make file containing 10 folders list in 10 different columns and then i create one indexing button, while clicking on this indexing button automatically arrive all new fresh list of all 10 folders name list with hyperlinks according to newly added file and deleted file, if you suggest me to do this job by creating one macro then i will also tray, if will succeeded to make this file i will flay with happiness in the sky and i will pray for you
Extraordinary Video.Thank you so much.
Thank you very much sir
Each time i view your videos, i learned from them. Today i learn to make list. Its an amazing experience.
Love Trump Excel ❤️❤️❤️❤️
WOW! great tips thank you.
Thank you very much.You help me a lot!
how to make vlookup in same no but different date. if there same no but two different dates , I want to see current date.
Thank you so much 💓
Is there a way you can do this with folder names?
At 5:18 is where you lost me. could not see how to "lock" the row and follow next steps. but then I read below on using command line and it worked nicely. but thank you for making the video. You may include some notes or something for the 5:18 mark.
how to lock.. i didnt understood that