Tracking and managing data across multiple files can be challenging. With Power Query, you can get the file names from a specific folder and all its sub-folders. In this article, we are going to show you how to get a list of file names in Excel.

Download Workbook

We will get help from Power Query to get a list of file names. Although Excel’s Power Query feature is for gathering and querying data, we can use it to list name of files instead of data in them.

What is Power Query?

The Power Query is a data management and querying tool for Excel. It was released as an add-in for Excel 2010 and 2013. Microsoft merged it into Excel with 2016 version. If you are using Excel 2016 or newer – including Microsoft 365 – you most likely already have this feature. To learn more about Power Query, see Power Query 101.

 

Using Power Query to get a list of file names

  1. If you’re using Excel 2016, 2019 or Office 365, follow Data > Get Data > From File > From Folder in the Ribbon.
    If you’re using Excel 2013 or earlier, follow Power Query > From File > From Folder in the Ribbon.
    How to get a list of file names in Excel 01
  2. Enter the main folder of your files either by typing its name or using Browse. Click OK when you have the target selected.
    How to get a list of file names in Excel 02
  3. Next is a preview window. You can see a list of files in the folder you selected and its sub-folders. You can click the Load button to populate the full list in your workbook. Power Query also allows you to modify this data before inserting it in to the worksheet. Click Transform Data (or Edit in older versions) to proceed.
    How to get a list of file names in Excel 03
  4. Clicking Transform Data button opens the Power Query window with the list of names. You can,
    1. Delete unwanted columns, such as Content.
    2. Show additional information about files.
    3. Change the order of the names, such as ascending names or descending modify date.
    4. Filter by folder or file type (extension).
    5. Filter out hidden files or folders
      How to get a list of file names in Excel 04
  5. We suggest removing the Content column first and then expanding the Attributes column to organize the list.
    1. You can remove the Content column by right-clicking on it and selecting Remove.
      How to get a list of file names in Excel 05
    2. To expand the Attributes column, click on the icon at the header, de-select the items you do not need and click OK.
      How to get a list of file names in Excel 06
  6. Clicking OK will add the selected fields as columns. You can use these fields for filtering or sorting as well.
    How to get a list of file names in Excel 07
  7. Once you are satisfied with the result, click Close & Load on the upper left of the Power Query window.
    How to get a list of file names in Excel 08
  8. Excel will load the table in a new worksheet. Another advantage is that you can refresh the table or connections at any time to get the updated file names and information.
    How to get a list of file names in Excel 09