Hi ,

Ever heard of the Excel FILES function? If you haven't, you're not alone.

This little-known function can list all file names in a folder and has been part of Excel for many versions. However, it's hidden as a legacy Excel 4 Macro function.

In this post I'll reveal how to use the FILES function and another way to list all file names in a folder without a formula and explore the various applications.

Prefer to read this on our website? 

Then click this link >>> Secret Excel Function

Video: Do You Know This Function?

Watch on YouTube Subscribe 609K

Getting Started with the Excel FILES Function

The FILES function is a legacy Excel 4 Macro function that lists all files contained within a specified folder.

Though not available in the conventional Excel functions list, you can still use it if you know how to enable it.

Setting Up the FILES Function

To demonstrate, let's create a list of files in a folder on my C:\ drive.

Note: You need to add a backslash and an asterisk \* at the end of the folder path for the function to work, like so:

C:\temp\Demo\*

To enable the FILES Function:

Define the FILES Function as a Named Formula:

Go to the Formulas tab and click on Define Name:

Name it fileList

In the Refers to section, input the formula: =FILES(Sheet1!$C$3) where $C$3 contains your folder path.

Using the FILES Function in Excel:

In versions of Excel that support dynamic arrays, simply type =fileList to spill the results across multiple columns.

For a column list, wrap it in the TRANSPOSE function:

If your Excel version doesn't support dynamic arrays, there are alternative methods available, which I'll discuss later.

Dynamic Folder Path

To dynamically update the folder path based on the location of your file:

Use the CELL function with TEXTBEFORE to return the file path of the current file and concatenate it with an asterisk:

Note: If you're using an earlier Excel version that doesn't support TEXTBEFORE, use this formula:

=LEFT(CELL("filename",C1),FIND("[",CELL("filename",C1))-1)&"*"

Filtering File Types

To filter the list to specific file types, modify the path to include the file extension, for example: C:\temp\Demo\*.xlsx for Excel files only.

For more dynamic filtering:

Create a dropdown list with common file types and an option to show all file types.

Use the IF function to check the file type selected in a cell (e.g. C5):

IF(C5="all files", "", C5)

Append this formula to the folder path with the ampersand for dynamic filtering.

Counting Files

To count the files in a folder, wrap the fileList formula in the COUNTA function:

=COUNTA(fileList)

Non-Dynamic Arrays Formula to List Files in a Folder

For users on Excel 2019 and earlier you can use the INDEX function to extract file names and wrap it in the IFERROR function to allow copying it down more rows than you currently have files for in the folder. This will automatically list any new files added to the folder.

I'll define a new FILES formula for non-dynamic arrays (e.g. fileListNonDA):

Extract File Names with INDEX and IFERROR:

=IFERROR(INDEX(fileListNonDA, ROW(A1)), "")

Copy the formula down to cover the expected number of files and allow for more files if required:

Generating a Hyperlinked List of Files

If you want to create a clickable list of file names that open the files directly from Excel, we can use the non-dynamic array technique above to extract the list of files in a folder and hyperlink them:

Create Hyperlinks

Modify the formula above to include the HYPERLINK function with the file path and file name as follows:

=IFERROR(HYPERLINK(LEFT($C$3, LEN($C$3)-1) & INDEX(fileListNonDA, ROW(A1)), INDEX(fileListNonDA, ROW(A1))), "")

Apply Conditional Formatting

The hyperlinks generated with a formula like this don't get automatically formatted in blue font with an underline, however we can use Conditional Formatting to do this for us.

Go to the Home tab > Conditional Formatting > New Rule:

Select the 'Use a formula to determine which cells to format' and in the formula field type:

=$C6<>""

Then click the 'Format…' button:

In the Format Cells dialog box apply a blue font and underline:

Enabling Macros

Since the FILES function is a macro, you need to save your file as .xlsm and enable XLM macros in Excel. Here's how:

1. Go to the File tab, click Options > Trust Center > Trust Center Settings.
2. Under Macro Settings, select Enable Excel 4.0 macros when VBA macros are enabled:
3. Adjust File Block Settings to allow opening Excel 4 Macrosheets in Protected View:

Using Power Query as an Alternative

For an alternate method that works in all versions of Excel and doesn't require macros, use Power Query to list file names in a folder and sub-folders:

Import File Names with Power Query

Go to the Data tab > Get Data > From File > From Folder:

Browse to your folder or paste the path.

Transform and Load Data

In the Power Query editor, click on the double headed arrow on the Attributes column and select the attributes you want (e.g. file size):

Filter out any folders you don't want the file names for.

Rename the query, remove unnecessary columns, and load the data.

Filter and Refresh

Filter the list based on file extensions.

Or insert a Slicer for easy filtering and refresh to update the list with new files.

Choose the field you want a Slicer for:

Select the file extension you want to display in the list:

Conclusion

The FILES function is a hidden gem in Excel, offering a powerful way to manage file lists. Whether you use the legacy macro approach or Power Query, you now have the tools to list file names in a folder in Excel effectively.

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub

Want to sponsor our newsletters?  Just reply to this email to get in touch with us.