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
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 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.
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.
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.