List of files in Excel (easy way, no VBA macros or formulas)

In this post, I’ll answer a question about how to create a list of files in a folder (and any subfolders) in Excel. For this illustration, we’ll use Power Query instead of VBA/Macros.

Objective

Before we jump into the details, let’s look at the objective. We want to create a list of files in a folder (as well as any subfolders). We want this list in Excel. We do not want to use VBA code or macros. We do have a fairly modern version of Excel for Windows which includes Power Query.

We basically have a folder with some files and a subfolder, like this:

And we want something like this in Excel:

Good news … we can create this with only a couple of mouse clicks 🙂

How To – Windows

First, click Data > Get Data > From File > From Folder.

In the resulting Browse dialog, simply browse to the desired folder and click Open.

You’ll then see a preview screen with a list of the files in that folder and any subfolders, like this:

Next, click the down arrow on the Load button and select Load To. In the resulting Import Data dialog, select Table and either a new or existing worksheet as desired.

Click OK and bam…

And that is what we call mission success 🙂

As a note, if you wanted to remove any columns or rows, you can click on Transform Data instead of Load. In the resulting Power Query window, you can easily delete columns you don’t need (Home > Remove Columns). You can also remove any rows you don’t need by using the filter buttons on the columns.

How To – Excel for Mac

If you are on Excel for Mac, and Power Query doesn’t have the From Folder option at the time you read this, my friend Rod showed me a great option for pasting the full path and file name into Excel for Mac.

Step 1 – Open Finder and Select Files

Open Finder and select the files, like this:

Step 2 – Copy

Now, do one of these two Copy commands

• Command+C – if you want to copy the file name only (not the file path)
• Command+Option+C – if you want to copy the file name and path

Step 3 – Paste

Toggle over to Excel and select a cell. Now, use one of these two Paste commands:

• Command+V – if you copied the file name only
• Paste Special > Text – if you copied the file name and path

Thanks Rod!

Conclusion

Hope this helps! And if you have any other Power Query or Excel for Mac tips, please share by posting a comment below … thanks!