Windows: List all files in a folder and its subfolders using Excel

I first wrote about the methods for doing this back in 2008 (!), but today I found another, much simpler way of getting a list of all files in a Windows folder and its subfolders using Excel (any version after 2010, I believe). (Update 28 November 2022: There are some issues with Method 1 that I found while testing it, so for an even quicker and cleaner method, skip to Method 2 below)

Method 1

It’s quick, clean, and you can specify which data to keep, delete or add before you get your final list. And any updates to the folders can be updated automatically via Excel if you save your list. Details and demo in this 3 minute YouTube video. https://www.youtube.com/watch?v=KsBtczyaPnc

In case that video ever gets deleted or moved, here are the basic steps:

1. Open a new Excel document.
2. On the Data tab, click the drop-down arrow next to Get Data.
3. Select From File > From Folder.
4. Click Browse and choose the topmost folder containing the files you want to list.
5. Click OK. Depending on how many files there are, the list could take several seconds (or more) to display.
6. A preview of the data found is shown in a new window (NOTE: This is NOT all the data, just a sample).
7. Check the data columns:
   • If you’re happy with what you see, click Load and go to Step 8.
   • If you want to add or delete columns, click Transform Data. In the next window, right click on the column header of any column you don’t need, then select Remove. By default the Attributes column likely displays ‘Record’—you can click the double-arrow icon to the right of that column header to show the attribute types you can select from. By default, all are selected. To clear the list and just select one or two, clear the ‘Select All’ checkbox at the top of the list, then choose the attributes you want to list. If not all attributes are shown, click the ‘Load More’ link. Don’t expect to see everything related to a file’s properties—for example, for a list of photos, the Tag, Title and Subject properties were NOT listed. Once you’ve selected your attributes, click OK to close this window.  To now load it all into Excel, click Close and Load on the Home tab.
8. Once all the data loads, you can continue to modify columns etc. Save the file if you later want to update the list.

(NOTE: In my testing, everything worked fine initially, but in later testing, I ran into an ‘Evaluation ran out of memory…’ error message consistently, even after closing and reopening Excel. In the comments under the YouTube video some people said that they’ve been able to extract 37,000+ file listings, but mine were nowhere near that big, more like 200 to 500. I couldn’t find the cause or how to fix it. If I do, I’ll report back here.)

Update from testing (27 Nov 2022): Testing results: I think the ‘Evaluation ran out of memory…’ error message MIGHT be related to the size (or type?) of the individual files. It runs perfectly well on a set of Word docs in a client’s folder and subfolders, where the biggest docs are 80 MB or so. But it chokes on a folder of videos, where most are in the several GB range. It’s almost like Excel is looking to load the actual files into the spreadsheet in the background, as versus just reporting the names of the files. This is a guess and further testing would be required, but as it ran perfectly well on a set of Word (and related docs) but not on videos, and then ran correctly on the Word docs again, I’d say file size might be the reason. I just tried it on a top-level folder of music files—it ran fine (3200+ files, with a total size of 20 GB). So I tried it again on a smaller subfolder of just 12 videos with a total file size of just over 3 GB, and it gave the error message again. So, more likely the file type than the size?

Method 2

For an even simpler option, check this 3-minute YouTube video. It’s all good, but if you’re looking for just the method to copy everything in subfolders as well as the selected folder, jump straight to the 2-minute mark: https://www.youtube.com/watch?v=F7SE5h7AUBg

As before, if that video ever goes missing, here are the basic steps:

1. Open Word or Excel or another text editor where you’ll copy your results.
2. Open Explorer to the top-level folder.
3. In the Search box type a single period and press Enter. This will list everything in the top-level folder and all its subfolders (the default option on the Search tab showing the results).
4. Go to the Home tab in Explorer.
5. Select Copy Path.
6. Paste (Ctrl+v) the results into your Word, Excel, or other document.

[Links last checked November 2022]