Easiest Way to Copy File Names from Folder to Excel
I recently had a situation where I needed to copy file names from a folder to Excel worksheet. I thought this would cause a headache with Excel, but actually there is a very simple way to do it.
If you’ve ever had to copy file names from a folder to Excel (whether to create some kind of report, organize files, or keep track of data on your hard disk) you know how frustrating it can be when there’s no clear option for doing it. But don’t worry, I’m going to share the simple solution I found to make this process quick and easy.
Here is my list of files that I want to see in Excel sheet:
The worst possible way to do this is to copy the filename and paste it into Excel. One of the best possible ways to copy file names from a folder to Excel is to use Excel’s data tools.
Copy File Names from Folder to Excel
Click on Data tab, and then on New Query > From File > From Folder:
Depending on the Excel version that you are using, instead of New Query button, you will have a Get Data button:
Select the folder where your files are stored, and then click on Open:
After the data loads, click on Transform Data:
Power Query Editor will open, and you will see the list of files, including filename, extension, and some other columns:
Remove the columns you don’t need by right-clicking on each column name and clicking on Remove Columns:
Click Close & Load and you will see the files that you currently have in your folder:
Now, if you add a new file to your folder, and you click on Refresh, you will see the recently added file in the Excel sheet:
This happens because you have an existing connection in Excel with your folder:
I hope this guide will help you get the files from folder as excel rows. I recently wrote another guide on How To Merge CSV Files into Single File, so make sure you also check that one.
Please leave your comment if you use a different way to copy file names into excel.