Excel is extremely useful in working with lists, and in the modern world, a common listing requirement is the list of files and folders within a folder. So whether you are just indexing a folder for backup purposes, or you need the names of files in the folder to find missing items, Excel can easily do it. Below a method of how to export file names from a folder to excel.
How to export file names from a folder to excel
Although there are some VBA and other methods, the latest versions of Excel have made it exceptionally easy. All you need to make sure is that you have PowerQuery/ Get & Transform loaded. To check if you have it go look at the ‘Is PowerQuery Loaded pages‘, but see below how useful it is first.
To understand PowerQuery better, have a look at the online PowerQuery course but the simple steps are (Using Excel 2016):
- Click on DATA
- New Query
- From File, and choose From Folder (as shown below)
You then choose the folder that contains the files by browsing to it.
When you click OK, it will show you the list of files in the folder. You will note that it has listed all the folder information including the path.
And that is it. If you now click LOAD, it will load it back into Excel as a table which you can now work with (see below).
This is great if you have a folder with only files in it i.e. there is no other folder with other files. You will notice that PowerQuery won’t allow you to select that folder. You have to choose a folder which has no other folders in it! [Update- latest version of PowerQuery now allows this]
What can you do if you want to export folder and subfolders into a list in Excel?
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.