Bottom Line: Learn techniques for naming data files that contain the same structure to organize and sort files by name and/or date.
Skill Level: Beginner
Video Tutorial
Download the PowerPoint File
Here's a copy of the PowerPoint file I use in the video in case you'd like to use it as a basis for your standard operating procedures for file naming.
Naming Files
Naming your files properly is important for organization. This is especially true when you have a lot of files of the same type or structure. Examples might include budget files for various departments or exports of data for different times or dates.
This was a great question from Joe, a member of our Elevate Excel Training Program. He was curious about best practices for naming data files that he is combining with Power Query.
Of course, these “best” practices I'm sharing are subjective. You may have another system that works better for you, and if so, we'd love to hear about it in the comments below.
So let's dive in.
The Two File Name Components
Naming files (and folders) is all about sorting them in File Explorer. When we press the Name header in Detail view to sort the column of names, File Explorer considers the names to be alphanumeric values. It reads the text from left to right and sorts the letters and numbers in this order.
There are two main components that I usually use to name files: description and date.
You can use either or both of these components to keep your files organized. Let's take a look at each component separately, then see how we can combine them.
1. Description
Including a concise description of the file's contents makes it easy to find your files, especially if they are on a shared server with other users.
If there is a hierarchy of the different levels of data, including those levels can be extremely helpful for sorting and finding the files later. I like to separate the different components of my file name using a dash.
An example of this hierarchy might be: File Contents – Division – Department.
In this case, there are multiple divisions and multiple departments within each division. So we are creating this hierarchy from left to right with the file names.
Naming your files like this makes it very easy to sort and locate specific entries, especially if you have lots of similar files.
2. Date & Time
Sometimes you will have files with the same contents, but for different dates. An example of this is when you are exporting data files from a system. In this case we want to be able to sort the files by date.
When creating file names with dates, I like to use the YYYY-MM-DD format, making sure to use two digits for both month and day, even if you would typically use only one digit when writing it out. Adding a zero in front of single-digit months or days keeps the files in sequential order.
In the example above, the short description at the beginning, “Data Export”, is the same for each file. Therefore, the sort order is determined by the date at the end of the file name.
Some data files would benefit from a name that includes a timestamp as well, especially if you are exporting several of the same type of file per day. For files that use a time in the name, I prefer the format HHMMSS (hour, minute, second).
Combining both the date and time then, it would look like this YYYY-MM-DD HHMMSS.
You might prefer to put dashes between the hours, minutes, and seconds, and that is totally fine. I'm accustomed to leaving them out as that makes them more readily distinguished from date notations.
Combining the Description & Date
Now sometimes things get more complex with our files. Let's look back to the example of the budget files where we had files for each division and department. We might also have multiple iterations of these files that are created or updated on different dates.
In this case we can combine the description and date in the file name.
If it's more important to sort the files by description, then put that component first. In this case the files will be grouped together based on their description, then the date iterations. Here is an example.
If it's more important to sort the files by date then put that component first. In this case the files will be grouped together based on their date, then by the description.
You can also use the Date Modified or Date Created properties of the file in File Explorer to sort the files. However, I find those to be less reliable when working in a shared server environment. Other users might save or modify old files accidentally.
Using & Sorting Folders
Of course you can also use folders to keep the files organized by date or description/hierarchy. The examples above illustrate how the files will be sorted within a folder.
The same sorting rules are applied to folder names as well. So you can also use these techniques to keep your folders that contain file iterations organized.
What About Spaces in File Names?
We had quite a few comments about using or NOT using spaces in file names. Some prefer to use an underscore or dash instead of a blank space.
Some older operating systems or databases might require file names to not contain spaces. This can also be true for files stored on web servers. Most apps and systems will replace the space with a %20 in the web address for the file.
Spaces in file names aren't usually a problem for modern systems, but you might want to consider these potential issues when creating your file naming guidelines.
I try to balance this with ease of use and readability, and typically use spaces in file names. But again, this is a matter of personal preference.
Conclusion
Again, I want to stress that there are obviously many ways to go about naming files, and I'm sharing just one logical way of doing it that makes sense for me and many others. You may have other opinions about how files are best named and organized (tell us about them in the comments!)
The main thing to keep in mind is that the more systematic and consistent you are, the easier it will be to locate and sort files as your library of files grows over time—for both you and other users in your group.
I highly recommend that your team or organization adopt a guideline for naming files. Feel free to use the PowerPoint file I provided in the downloads section above as a starting point for this. You can modify the file as needed.
Along the same lines as today's post, I've got this tutorial for the Best Practices for Naming Excel Tables.
Also, I've created a macro that automatically names files with a date stamp, and you can access it at the end of this tutorial about saving and closing workbooks. Check it out!
I hope this has been helpful for you. See you next time!
Free Training Webinar on the Power Tools
If you'd like to learn how to combine similar files in Excel, then checkout my free training webinar on all of the new Power Tools. This includes Power Query, Power Pivot, Power BI, pivot tables, macros & VBA, and more.
It's called The Modern Excel Blueprint. During the webinar I explain what these tools are and how they can fit into your workflow.
You will also learn how to become the Excel Hero of your organization, that go-to gal or guy that everyone relies on for Excel help and fun projects.
The webinar is running at multiple days and times. Please click the link below to get registered and save your seat.
It is important to use leadding zeros when using numbers for a sequence or whatever. Because File Manger treats them as alpha characters which you mentioned and explained a bit with using two digits for mm and dd. There is nothing more confusing than have a series of files that are the same filename but updates of the previous one if you use … ‘filename’-1,’filename’-2,’filename’-3,… filename’-10, filename’-11,12,…20,21,22 sort out as:
‘filename’-1,10,11,12,2,20,21,22,3
instead of your expected/desired
‘filename’-1,2,3,10,11,12,20,21,22
so in this case the sequence numbers would be
01,02,03, … 10,11,12, … etc.
———–
And use the date format you suggest. I will normally use yymmdd without any dashes. Your yyyy-mm-dd is more understandable unless one gets familiar with the yymmdd and of course we don’t want anyone to have to go through Year 2000 when 3000 comes along. 😉
———-
One last little thing is when using the month common name in a filename. I will put the numeric month in front of the common name so they will sort in chronical order such as:
Stats-01Jan
Stats-02Feb
Stats-03Mar
…
Stats-12Dec
Format is mmxxx
And I would normally save this is a folder with a name for the year – 2020 (folder name)
—
All for now, but this is a GREAT topic and very significant when looking at files from a few months or years ago. Standards are important and to set them before you create hundreds of files is most helpful.
Great tips and suggestions! Thank you for taking the time to share your thoughts, Dan! I really appreciate it. 🙂
Jon, the hardest thing about deciding on a system is converting existing files mid-stream. If you spent the first half of the year sorting by description, for example, by the time you realize that you should have gone with dates, you’re six months in with a bunch of files that you now have to rename.
Multiply this by each level of folders you have and, if you decide to rename the folders as well as the files, you’ve got a mess on your hands.
I blame our reliance on the rigid insistence that we adhere to: physical files, folders and cabinets.
To combat this nightmare, I do away with Explorer in favor of a more powerful duo: Everything.exe and Xyplorer. the first is a desktop search utility, which I use instead of Windows 10 indexing. The second is my favorite Explorer replacement utility.
In addition to using alternative software for file management, I’ve adopted a tag-based scheme similar to your dash-delimited naming system. The difference is, I put every file into one folder!
I know that’s extreme but, with the two tools working in tandem, I spend less time looking for files. I actually sort by last modification time, since I’m the only one using my computer. This keeps my recent projects in view. That’s important when you have 100’s of files in one folder.
If I need to find an older project and I can remember at least part of the name, Everything.exe will find it instantly.
As for changing mid-stream, I use one more tool: Bulk Rename Utility. This portable software makes renaming files even more easy than Xyplorer’s batch renaming!
Cheers,
Mitch
Great points here, Mitch! I agree that changing file naming conventions can be challenging. Especially if you are using tools/features like Power Query, linked formulas, Power Pivot, VBA, etc. Changing existing file names can break links and cause errors.
I have not used those tools you mentioned, but will check that out.
I think Gmail has a decent implementation of this with email. No need to organize in folders or delete. Just archive and search.
I know this method works better for some people and not others, but happy to hear you’ve created a system that is quick and efficient for you. Awesome!
Thanks for taking the time to share. I appreciate it. Have a good one! 🙂
Oh, yeah, the PQ scripts are the biggest pain. I try to use parameter tables as much as possible, but those “quick and dirty” jobs where you get file from folder…arrgh!
I’ve just downloaded Everything, and it seems pretty awesome!
In a team environment, when naming files, we have found spaces to be a big problem so we avoid them by using the underscore.
Some team members like the text version of the month name. This presents a problem when sorting file and folder names. So the solution is to use a combination of number and text so the names are Operational_Data_2020_01_Jan, … _2020_02_Feb, … etc., ensuring the text part of the month is always three characters long. Of course the number part is always two digits and is always before the text name of the month.
This protocol is part of our culture for many years now. It has made file management very easy. Like you said in your note, the key is consistency.
Thanks for sharing, Nasser! I’m curious to know what issues you have found with the spaces in file names? Is it that sometimes people accidentally type two spaces instead of one?
Yes, you hit the nail on the head, spaces before or after are invisible and in the middle could be more than one. Best practices with any database is to not use spaces in file/field names and avoid leading zero’s in any field that may be used/imported as numeric especially in Excel (since it will automatically interpret as a number and eliminate the leading 0’s). Also, beware of special characters. Even though Windows might allow them, other data storage systems may not. I also avoid spaces in tab names for the same reasons.
Greetings. Regardless of what name is used, I have found that format consistency of the names is important, especially when moving the files among various systems that have different operating systems. The first is never to use blank spaces in the name. Use dashes or underbars instead. The other is that I always tag the name with the date and version. For example 20201015a, then 20201015b, and so on. This has come from my experiences years ago in airborne data collection. The date format easily expanded to include hours, minutes, seconds, milliseconds, and so on, and in that order. It made the automated decoding of the file header very easy – relatively speaking.
Peace,
Mike G.
Great tips! Thanks for sharing, Mike!
What issues did you have specifically with the spaces in the file names, if you don’t mind sharing?
early on various operating systems had issues parsing with spaces in directory and file names. It is not supposed to be a problem now, but…
Jon, always appreciate your tips. In my accounting practice, I rely entirely on file naming to organize individual client receipts. What has worked well for my Excel-based accounting system has been a two-component filename as you suggest that includes vendor name and date of transaction (YYMMDD), like:
Amazon_201002
Amazon_201006
Amazon_201007
MeridianPower_201001
Staples_101003
Walmart_201023
Walmart_201027
Walmart_201029
To additionally create a hyperlink after each accounting entry that links to the receipt file is a nice way to keep everything handy.
Thanks for the suggestions, Don! Great to hear from you! 🙂
Another thing to keep in mind when revising a naming system, is that it is fairly easy to do using VBA. If you were using date description you can flip this without too many problems.
I do this on an ongoing basis on files I download from one of my banks that always gives me dd mm yyyy for my files. I just rearrange it with simple VBA code.
Great tip, Mary! Thanks for sharing. I’ll add this to our list for future post topics.
Have a nice weekend! 🙂
Thanks Jon Great tips here and the suggestion of trying to get the organisation to adopt standardised practice is one worth following up on
Thanks Cheryle! 🙂
Naming standards are a great way of “simplifying” computing. I’ve actually been using exactly that naming standard for years.
I use a variation of that for urls, and documents that are not being used in data processing. I used to rely on url datestamps, but then Windows decided to keep updating them, so they were no longer useful. I had to embed the date in the url name. Same with Windows “tags”. I could never get them to work, so I use a high level abbreviation as a “tag” to group common articles, and if I have additional tags I want to use, I add them as suffixes to the end of the file name. This way when I want to search for a subject I can
For example
e pq- common descriptive name- item type- yy mm dd
e- for Excel
pq- for PowerQuery
Common descriptive name- ie webinar name
Item type- presentation notes, example (file), answers (file)
Everything file name search is wonderful at what it does. I’ve been using it for more than 10 years now. It does File Name searches as fast as you type by hooking directly into Windows NTFS. Beats the daylights out of Windows search. Of course, it does not index the contents, but I normally don’t need that level of search.
Hey Ron,
Great to hear from you! Thanks for sharing your techniques. I really like the abbreviations in place of tags.
I have not used Everything Search yet. I’ll check it out.
Thanks again and have a great weekend! 🙂
Nice ideas. I want to add that at my organization where our fiscal year differs from the calendar year I use the accounting period/month number of the fiscal year as part of the name.
Thanks, Shimshon! Great tip about the fiscal month number. I forgot to mention that in the post and video.
Thanks again and have a nice weekend! 🙂
Thank you for this video. It’s a very important matter that is to often overlooked. Until 2032, it’s a really good idea to have 4 digit years. I’m from Québec, therefore, I’m sometimes stock with too many date formats. It can become very confusing setting apart year, month and day. With a 4 digit year it takes at least one item out of the equation. You say at the end of the video that it’s subjective. It is to a certain extent but if anyone wants to communicate with others, it’s better to have a naming system that can be understood easily by anyone.
Thanks Jean-Sebastien! That’s a great point about the year format. I also try to use 4 digits for the year for that reason, but forgot to mention it in the video.
I think it’s also important where I’m at in the U.S. because our default format is mm-dd-yy. So there are some dates that can easily be misinterpretted, especially since we are now early in the century and a 2 digit year number could be a month or day.
Thanks again and have a nice weekend! 🙂
Thanks Jon for sharing this and all the comments shared by others. I’ve been using the Description_YYYY-MM-DD hhmm (24-hour clock) for about 5 years and it works great for me. I am at a new company and am now trying to get my team to adopt this as well.
Working Internationally, we recommend to use MMM format.
Adds Absolution to Dates: is 2020-10-09 10th Sept, or 9th Oct?
2020-OCT-09 is definite
Hi David,
The method you are using breaches one of the fundamental purposes of the naming convention.
Under the heading: “ The Two File Name Components” ->
“Naming files (and folders) is all about sorting them in File Explorer”
Month names do not sort in a logical order. Also starting with yyyy should be a prompt that it will be followed by mm, since according Wikipedia no country uses yyyy-dd-mm (not in written form)
https://en.wikipedia.org/wiki/Date_format_by_country.
At the end of the day being a standard still means it needs to be communicated.
PS: for folder names I do use 2020-10 Oct (yyyy-mm mmm). This works for the sorting and is quicker to read.