This article shows how to make a list of the folders, or even the files inside a folder on an excel spreadsheet.This is a very useful tool as it is impossible to type in the names manually especially when there are plenty of files.

If you wish to make a list of all the folders or files lying in any of your directory, you can simply do so using “Dir” command. Though this is a DOS command, but to be able to generate a list it does not require you to have any expertise in it. There are only a few things that you need to keep in mind.
Making a list of folders can come in very handy when you have loads of folders and you quickly want to print a list, as it will take you ages if you start keying them in, when it’s just a minute job.
This can also be a good security tool. Suppose a group of people are working on a common drive, so that everyone has the access and there are lots of folders, so to keep track of all of them and to make sure no body deletes any folder, you can simply make a list in excel so that everybody is aware what all folders are in there which, in turn, will make everyone a careful worker.
The following example explains how you can copy the folder listing to a text or an excel file.

Example: We want to make a list of the folders shown in the screen below.

generating-list.JPG

Go to start menu, choose run and type “cmd”. Hit OK.

generating-list1.JPG

Now your folder is there in D drive so type in “D:” and hit enter. To reach “D:\Excel\Articles” just type “cd” (The cd command stands for “change directory.”) and then space and then type in “Excel” and hit enter. Again type in “cd” followed by spacebar and then write articles and press enter. Or to avoid having to write folder location you can simply drag that folder to the “Command screen” after typing in cd and spacebar.

generating-list2.JPG

Type in “Dir > ListofExcelArticles.xls” (You can give any name to your file but it must be one word and if you need it in notepad simply add the extension .txt instead of .xls)

generating-list3.JPG

Now if you go to the referred location you will notice “ListofExcelArticles.xls” file has appeared as shown below.
generating-list4.JPG

If you open this file you will notice that you don’t actually need a lot of stuff and everything has been put into column A as shown below.

generating-list7.JPG

So to be able to segregate the names of folders and to delete all unnecessary stuff, you can simply use the Excel’s “Text to column” tab. Simply select column A and choose “Text to Columns” option under the “Data” menu.

generating-list5.JPG

Choose “Fixed Width” as it gives you the option to create or delete a break line to suit your needs. Keep hitting Next till you reach finish.

generating-list6.JPG

You will notice it has separated the items into different columns and moved folder names to a separate column D from where you can simply cut and paste to some other sheet.

generating-list8.JPG

Share This

Popularity: 32%



Reader's Comments

  1. Gautam | July 5th, 2008 at 4:07 am

    This was quite useful too.

    I had written a macro to do something similar. I shall email the same to you.

    Gautam

  2. Ermaani | September 14th, 2008 at 1:07 pm

    This was VERY useful

    Thank you

  3. DukeR. | January 21st, 2009 at 11:02 pm

    This worked fine for me and was Very handsome, Many thanks man!!

  4. Andi | March 24th, 2009 at 3:29 pm

    Thanks, I’ve wanted to be able to print these type of lists for ages.
    Very useful

  5. Adnan | April 22nd, 2009 at 5:07 am

    Wow that was great!!! thatnks alot for sharing that method its really usefull…

  6. Ashok Kumar | May 8th, 2009 at 9:51 pm

    Its very usefull. Seems like very simple steps but worth a lot. Thanks to handyexceltips.com. :)

  7. Jeff | May 8th, 2009 at 10:19 pm

    Thanks for publishing this powerful tip !! Jeff

  8. salar | May 22nd, 2009 at 1:23 am

    Thank you for this tip, as it’s helped me a lot in my work and it’s easy

  9. Atul | May 24th, 2009 at 2:40 pm

    Fantastic tip Shikha, was backing up my music and was looking out to create a list of Albums within each Partition I had. Searched up the internet and look where I am. Thanks a Ton you made my day :D

  10. Eddie | June 9th, 2009 at 4:58 pm

    This is an extremely clever tip. Thank you very much for uploading it :)

  11. Axel Moonshine | June 11th, 2009 at 7:27 am

    very useful. thanks alot. :D

  12. Fernandesfran | August 2nd, 2009 at 8:04 am

    Thanks a lot, this proved very helpful indeed!

    Cheers,
    FRF

  13. awkward | September 3rd, 2009 at 1:40 pm

    Alternately, you could use the dir command with the /b option, for a command that looks like this:

    dir /b > filenames.txt

    This will create your list of files without having to run it through excel.

  14. Anshuman A.S. | October 7th, 2009 at 2:09 am

    I have been asking people since Windows was introduced to help me out with this.

    Thanks a million. Such a simple thing but so useful.

  15. Preethi | November 6th, 2009 at 10:14 am

    really good……. but even more useful and better is the article custom right click under Excel shortcuts category…….try it!! its seems a wonder for a computer novice like me………

  16. macaaroni | November 11th, 2009 at 5:03 am

    Outstanding tut! But awkward had the best addition, the /b command line REALLY did the trick! It strips out all of the excess info and just leaves you with the file name, perfect!

  17. federico | January 21st, 2010 at 7:12 am

    Thanks for the tip
    this help me figured out a way to make a list of a karaoke collection the easy way (when having lots of songs “files” in multiplefolders)this will generate a list of all contents withinn that folder & subfolders.
    this is the way i did it

    dir /S/b > filenames.xls

    /s = display files in a specific directory and all subdirectories
    /b = uses bare format no heading information or summary.
    :-D

  18. Shumaker | February 2nd, 2010 at 11:20 am

    Nice tool to print directory listings, previously i used print directory.

  19. Jason Anderson | February 16th, 2010 at 7:39 pm

    Excellent - just what I was after.

  20. Uday | March 3rd, 2010 at 10:24 am

    Really very cool…

    I like it.

    Uday

  21. Ted | April 19th, 2010 at 2:48 pm

    This is a great tip. Thanks so much!

  22. Geoff | April 19th, 2010 at 8:20 pm

    Wow this is fab, easy to do and created lists of all movies and music.Many Thanks

  23. Sandie | July 27th, 2010 at 1:56 pm

    This helped me a lot thanks

  24. Grin Reaper | September 22nd, 2010 at 4:02 pm

    Thanks !!! this was really really useful, and saved me a tonne of my time when i was in a crunch!
    God Bless you for this

  25. Grin Reaper | September 22nd, 2010 at 4:03 pm

    Thanks a lot !!! This was extremely helpful and saved me a tonne of time when i had none. God bless you for this.

  26. raoof | October 16th, 2010 at 3:54 pm

    tanx

  27. Kirk Jackson | October 19th, 2010 at 5:18 pm

    I’ve been trying to do this for years the hard way… Thanks so much for the tip. Didn’t know you could export to excel from the cmd :)

  28. Polika | November 25th, 2010 at 8:07 am

    Thanks

  29. Abhilash | November 26th, 2010 at 9:31 am

    Thank was really useful. I am saved writing lines of code to perform this operation..

    :)

  30. Cristina | December 4th, 2010 at 2:12 pm

    @Gautam, could u maybe e-mail me that Macro? cause i have no idea how to make this one.
    i need all the directories and files/sub folders in my excel :D

Leave a Comment

Close
E-mail It