This guide will explain how to create multiple folders at once using Microsoft Excel.
When working on a project, there may be situations where you need to create multiple folders with a particular set of names. Creating a folder for each employee or a folder for every month in a given year.
This process can be tedious to do yourself, therefore, automating this task would be a big help.
One way to do this is by using an Excel spreadsheet to create a Windows Batch file that you can run inside any directory to generate your desired folders.
In this guide, we will provide a step-by-step tutorial on how to create multiple folders and subfolders using Microsoft Excel and the Notepad application.
A Real Example of Creating Multiple Folders at Once in Excel
Let’s explore a few examples where we can use Excel to help create multiple folders at once.
In the table above, we have a list of folder names we want to use for the folders we plan on creating. Our goal is to convert each value into a valid command that can be executed by the Windows operating system through a batch file.
A batch file is simply a list of commands stored in plain text that can be executed by an operating system’s command line interpreter. In Windows, we can specify that a text document is a batch file by saving it as a .bat file. Once saved as a .bat file, users can double-click on the file in Windows Explorer to run all commands specified in the file.
In the image above, we’ve created a second column that builds the text command to create a folder with a certain folder name. The MD batch command can be used to create a new folder in a specified directory. If only a folder name is provided, Windows will create the folder in the current directory.
To create the second column, we’ll use the following formula:
="MD "&""""&A1&""""
If your desired folder name includes spaces, you must add double quotes before and after the folder name.
After creating all the batch commands, we can copy the data and paste it into a text editor. While any editor will do, the simplest and most accessible option would be the Notepad application.
We’ll save our Notepad file as a .bat file rather than a .txt file so that Windows will recognize that our file contains a sequence of batch commands. Double-clicking on our .bat file will initiate the execution of all the commands that will generate our desired folders.
Aside from generating folders under a specific name, we can also use the MD command to generate a folder and one or more subfolders.
We can indicate a new subfolder within a folder by separating the folder and subfolder name by a backslash (\). For example, the command MD 2021\Q4\Extra will create three new folders, with the 2021 folder being the root or main folder.
We can modify our earlier formula to create an MD command that will create our desired folders and subfolders:
="MD " & TEXTJOIN("\",TRUE,A2:C2)
The TEXTJOIN function allows us to combine multiple text values into a single string separated by a delimiter. In our formula, we’ll use the backslash character as our delimiter by adding the character in the first argument. The second argument is set to TRUE to ignore empty cells. Our third argument A2:C2 refers to the range of values we want to combine.
In the image above, we can see the hierarchy of our folders after running the MD 2023\Q1\Extra command.
Create your own copy of our examples by clicking on the link below.
Head to the next section to read our step-by-step tutorial on how to create multiple folders at once in Excel.
How to Create Multiple Folders at Once in Excel
Creating Multiple Folders
- Type the formula =”MD “&””””&A1&”””” to create the batch file command that creates a folder under that name.
Ensure that there is a space after the MD command and that the filenames themselves have no illegal characters such as an asterisk (*) or question mark (?).
- Hit the Enter key to evaluate the formula.
Use the AutoFill feature to automatically fill the remaining values in column B.
- Use your cursor to select the entire range of commands. Type Ctrl+C to copy the data to your clipboard.
- Open the Notepad application and paste the results into the blank editor.
- Save the text file as a .bat file. To do this, add the extension ”.bat” after your desired filename in the Save As dialog box. In the Save as type dropdown menu, select “All Files (.)” option. This prevents Notepad from adding a .txt extension automatically.
Ensure that the file is saved in the directory where you want the batch file to run.
- Windows may ask the user if they are sure about changing the file extension from .txt to .bat. Click on Yes to proceed.
- Click on the .bat file to execute all the MD commands inside our batch file. Your desired folders should now appear in the current directory.
Creating Multiple Folders with Subfolders
- Create a table that shows the folders and subfolders to generate.
In this example, we’ll be creating three main folders (2021, 2023, 2023) in our directory. Each folder will contain four subfolders labeled Q1 through Q4. Additionally, each of these subfolders will have another subfolder labeled Extra.
- We’ll use the formula =”MD ” & TEXTJOIN(“\”,TRUE,A2:C2) to build our MD command that creates our folder and subfolders.
The TEXTJOINfunction allows us to create a string that combines our folder and subfolder names (in columns A to C) with a backslash separating them.
- Hit the Enter key to evaluate the function.
We’ll then use the AutoFill feature to fill the remaining values in the column.
- Similarly with the previous example, we’ll copy the commands and paste them into a text editor to save as a .bat file.
Click on the file to generate your desired folders and subfolders.
Each folder in our example contains four subfolders.
Each of these subfolders contain another subfolder labeled Extra.
These are all the steps you need to know to start creating multiple folders at once using Microsoft Excel!
After reading our tutorial, you should now be able to easily automate creating multiple folders from a list of filenames. If you want to learn more about automation, read our guide on how to create your own Excel macros using ChatGPT.
That’s all for this guide! Try browsing our library of spreadsheet resources, tips, and tricks!
