Listing the files in a folder is one of the activities which cannot be achieved using normal Excel formulas. I could tell you to turn to VBA macros or PowerQuery, but then any non-VBA and non-PowerQuery users would close this post instantly. But wait! Back away from the close button, there is another option.
For listing files in a folder we can also use a little-known feature from Excel version 4, which still works today, the FILES function.
The FILES function
If you search through the list of Excel functions, FILES is not listed. The FILES function is based on an old Excel feature, which has to be applied in a special way. The instructions below will show you step-by-step how to use it.
Create a named range for the FILES function
The first step is to create a named range, which contains the FILES function. Within the Excel Ribbon click Formulas -> Define Name
Within the New Name window set the following criteria:
- Name: listFiles
Can be any name you wish, but for our example we will be using listFiles. - Refers to: =FILES(Sheet1!$A$1)
Sheet1!$A$1 is the sheet and cell reference containing the name of the folder from which the files are to be listed.
Click OK to close the New Name window.
Apply the function to list files
The second step is to set-up the worksheet to use the named range.
In Cell A1 (or whichever cell reference used in the Refers to box) enter the folder path from which to list the files, followed by an Asterisk ( * ). The Asterisk is the wildcard character to find any text, so it will list all the files in the folder.
Select the cell in which to start the list of files (Cell A3 in the screenshot below), enter the following formula.
=INDEX(listFiles,1)
The result of the function will be the name of the first file in the folder.
To retrieve the second file from the folder enter the following formula
=INDEX(listFiles,2)
It would be painful to change the file reference number within each formula individually, especially if there are hundreds of files. The good news is, we can use another formula to calculate the reference number automatically.
=INDEX(listFiles,ROW()-ROW(A$2))
The ROW() function is used to retrieve the row number of a cell reference. When used without a cell reference, it returns the row number of the cell in which the function is used. When used with a cell reference it returns the row number of that cell. Using the ROWS function, it is possible to create a sequential list of numbers starting at 1, and increasing by 1 for each cell the formula is copied into.
If the formula is copied down further than the number of files in the folder, it will return a #REF! error.
Finally, wrap the formula within an IFERROR function to return a blank cell, rather than an error.
=IFERROR(INDEX(listFiles,ROW()-ROW(A$2)),"")
Listing specific types of files
The FILES function does not just list Excel files; it lists all file types; pdf, csv, mp3, zip, any file type you can think of. By extending the use of wildcards within the file path it is possible to restrict the list to specific file types, or to specific file names.
The screenshot below shows how to return only files with “pdf” as the last three characters of the file name.
The wildcards which can be applied are:
- Question mark ( ? ) – Can take the place of any single character.
- Asterisk ( * ) – Represents any number of characters
- Tilde ( ~ ) – Used as an escape character to search for an asterisk or question mark within the file name, rather than as a wildcard.
The screenshot below shows how to return only files with the name of “New York.“, followed by exactly three characters.
Advanced uses for the FILES named range
Below are some ideas of how else you could use the FILES function.
Count the number of files
The named range created works like any other named range. However, rather than containing cells, it contains values. Therefore, if you want to calculate the number of files within the folder, or which meet the wildcard pattern use the following formula:
=COUNTA(listFiles)
Create hyperlinks to the files
Wouldn’t it be great to click on the file name to open it automatically? Well . . . just add in the HYPERLINK function and you can.
The formula in Cell A3 is:
=IFERROR(HYPERLINK(LEFT($A$1,LEN($A$1)-1)&INDEX(listFiles,ROW()-ROW(A$2)), INDEX(listFiles,ROW()-ROW(A$2))),"")
Check if a specific file exists within a folder
It isn’t necessary to list all the files to find out if a file exists within the folder. The MATCH function will return the position of the file within the folder.
The formula in cell B3 is:
=MATCH(A3,listFiles,0)
In our example, a file which contains the text “New Yor*” exists, as the 7th file, therefore a 7 is returned. Cell B4 displays the #N/A error because “Seattle” does not exist in the folder.
Find the name of the next or previous file
The files returned are in alphabetical order, therefore it is possible to find the next or previous file using the INDEX / MATCH combination.
The next file after “Denver.xlsx” is “New York.pdf“. The formula in Cell B3 is:
=INDEX(listFiles,MATCH(A3,listFiles,0)+1)
Retrieve values from each file with INDIRECT
The INDIRECT function can construct a cell reference using text strings. Having retrieved the list of files in a folder, it would be possible to obtain values from those files.
The formula in Cell B3 is:
=INDIRECT("'"&LEFT($A$1,LEN($A$1)-1)&"["&A3&"]Sheet1'!$A$1")
For INDIRECT to calculate correctly the file does need to be open, so this may be a significant flaw in this option.
Usage notes
When working with the FILES function there are a few things to be aware of:
- The file path and file name is not case sensitive
- Files are returned in alphabetical order
- Folders and hidden files are not returned by the function
- The workbook must be saved as a “.xlsm” file format
Further reading
There are variety of other Excel 4 functions available which still work in Excel. Check out this post to find out how to apply them and download the Excel 4 Macro functions reference guide.
If you decide to use a VBA method, check out this post.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
Hi there, this is what I was looking for for many years. I spent an awfull amount of money on books looking for some info similar to this. I wasted all my money. This is what I am looking for. Thank you so much for this and all the examples. I’ll always have a look on your web site for more of these.
Kind regards
Neill
Hi Neill,
It is certainly a useful feature, which I make use of from time to time.
PowerQuery (Get & Transform) is also a good alternative, but it’s a shame that Microsoft hasn’t created another more intuitive way of achieving the same result.
Hopefully there were some other useful tips in the Excel books you purchased.
Hi Neill
Thanks for all the guidance…..very/very helpful…..One question though, Is there a way to add the modification date along with with the list of files? Again focus is without the help of VBA. If there is, it will help me a lot since I am managing more than 600 files in the folder and the modification date can save at least 15% of my time. Thanks.
Syed
I’m not aware of a simple method. You could try a VBA User Defined Function:
Or you could try PowerQuery as an option for the whole process.
This is just what I need, it works well although my list does not refresh if an additional file is added to the folder.
How would I be able to refresh the list?
Excel has been designed to be efficient with calculations and it will only recalculate a cell when required to do so. Adding or removing files from a folder does not trigger any recalculations.
Adding a volatile function might help to force it to recalculation more often.
Yes, use a volatile function, for example =INT(COUNTA(LISTFILES)+RAND())
This formula will return the number of entries in the directory matching your mask.
And every time any recalculation anywhere in the workbook is done, the list will be updated. (So this will slow down recalculation, but it will work.)
What’s cool with the new Dynamic Arrays, is you can enter this into just one cell:
=TRANSPOSE(ListFiles)
and Excel will build a dynamic list of the files starting in that cell and extending down as far as needed.
Thanks to all contributing to this. It helped me with similar task. The difference is that I need to check if all files listed in excel are present in dedicated folder. There will be 50 spreadsheets with file names and 50 folders with files, some in .zip and some in subfolders.
=TRANSPOSE(ListFiles) good one, works fine, though.
Two questions.
1. how to search also in archives (zip) in same directory?
2. how to search in subfolders?
I don’t believe there is an easy way to search inside zip folders. To search inside subfolders you can try these methods:
Power Query – https://exceloffthegrid.com/power-query-file-attributes/
VBA – https://exceloffthegrid.com/vba-code-loop-files-folder-sub-folders/
*quote: Files are returned in alphabetical order
This is not the case anymore! I’ve been using this formula successfully for years, but suddenly it is no longer returning the file list in order. It is driving me crazy! Please help.
Anyone?
Its driving me nuts too. I thought I struck gold with this formula just to be stopped dead on my tracks by something so simple as files not being in alphabetical order.
If you have dynamic arrays in your version of Excel, you can wrap the SORT function around it to put the items in alphabetical order.
If you don’t have dynamic arrays yet, you can try traditional formulas. Check out this post as a example:
https://exceljet.net/formula/basic-numeric-sort-formula
Excellent soluction, easy to list all files from excel.
It work with folder path in my PC
I would like to know how to do the formula using a Folderpath in onedrive
Yes… OneDrive is a bit of a problem.
Even when using the CELL function, it gives the URL, rather than a file path. At present, I don’t have a good simple solution for this. But I should look into it a bit more as it’s becoming an issue for more people.
I never knew about the FILES function. Thank you for sharing this useful information.
Thanks for this. It is really useful. Is there a similarly easy way to list Folders in a Directory?
I think Power Query is your best option for this:
https://exceloffthegrid.com/power-query-file-attributes/
This works fine when I enter the formula in a new excel file & save the xlsm file. But when I reopen, it gives a #NAME? Error and the filenames are gone. Tried refreshing the formula, changing the named range etc. but nothing works.
I again tried saving a new excel(xlsm) and same story.
That’s strange. It’s been several years since I’ve used this technique.
Maybe the Power Query technique would be a better option, as it uses it current technology
https://exceloffthegrid.com/power-query-file-attributes/
I have encountered two difficulties lately using Excel 4 macros.
I have Office 365, version 2112, so the latest hoi polloi version pushed out at the moment.
MS is taking harassing steps to ease people off these macro commands. A week ago, on 2111, I did one of them, saved as an XLSM file, then opened it later and cells my macro had been referenced in were destroyed, given a #BLOCKED error, and not recoverable. Looking into it, quickly, I was told that there is now a Trust Center setting to allow them to be used. Maybe six months ago MS began allowing organizations the ability to deny their users use of the macro commands, but that didn’t do enough I guess so they addressed it directly. Anyway, File|Options|Trust Center|Trust Center Settings button|Macro Settings took me to find a new checkbox one must check to “Enable Excel 4.0 macros when VBA macros are enabled.”
Your error is the #NAME? error not the #BLOCKED error so clearly it’s not that checkbox, or at least, fairly clearly. BUT, I do not know the error received if the other half of the above is operative: your organization has denied their users the use of Excel 4 macro commands, or, if the recent bit is a guide, maybe MS set that as a default and they have to take a step to enable them for you. Against that last is that I am part of an organization and no one addressed it AT ALL but it did not affect me so it would not seem unenabled is the default. But if they chose it on purpose, the error it would seem you’d get would be exactly the #NAME? error since they would not be available to your copy and therefore Excel’d have no idea how to resolve the name.
Against that is “How would they have worked at all, before saving too, not just after saving?” But I’d point out that the reopening process might be where Excel implemented things rather than reaching much deeper and actually unenabling them altogether on you. So using them would be a little akin to using several Immediate Window commands to accomplish something each time when you can’t save macros with your file.
I used to be able, until about that six months or so ago, to save a file with these in it, get the warning I had to save it as an .XLSM, reject that idea and continue on to an .XLSX file and all was good when I reopened the file. And I would never be put through the nannying again on any particular file I’d rejected the idea on. Then one day, that all ended. Next time I opened one, all the Named Ranges using them were ruined and their cell-side formulas blasted, irretrievably, like when saving something with macros in it as an .XLSX. #NAME? errors.
So MS is definitely doing at least a half-court press, likely in preparation for the day when they just cut them out completely. (Though I suspect, the two are literally linked, that they are not cutting them (might be really hard, eh?) but that these are decided steps to cut ACCESS to them, see what people do to keep finding access, and then cutting into those techniques, as well as some pushing you out too.)
So maybe what you have experienced is a further cut-us-off-from-them technique that I have not yet been hurt by, but now can look forward to… yay… Especially if your copy is on an Insider (or whatever they call alpha and beta testers now) track and more advanced than my hoi polloi version.
Hi,
I’ve tried as you said and I don’t get the same results, unfortunately…
I’ve created a folder C:\Teste and some .xlsx and .doc files inside it (1.xlsx; 2.xlsx,…).
I’ve defined a name as you said (LstF).
On file 1.xlsx, on Sheet1, on cell A1 I’ve copied the folder path (C:\Teste\*)
On file 1.xlsx, on Sheet1, on cell A3 I’ve wrote the formula =IFERROR(INDEX(LstF;ROW()-2);””) and the result I get is the content of the cell A1 (C:\Teste\*).
Am I doing something wrong?
Thanks.
Hi every body,
If the names of files in folder that i want to create their name list are 1 2 3 and so on… And i want to print just the name of last!!! (Max) File name, so how the formula will be????
Excuse me for bad english
🤔🤔🤔