Handy Excel Tips

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.

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

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.

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)

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

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.

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.

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.

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.

Popularity: 42%