Here are some best free Excel splitter to split Excel sheet into multiple sheets. In the following post, I have listed some software and some simple tools to split an Excel sheet into multiple sheets. The following tools can split an Excel sheet in various ways. Some of them extract all sheets from an Excel workbook and then save them as separate files. While some of these tools split an Excel file by the number of rows. You can specify how many rows you want in each file and then they will do that for you. In the following list, I have also added a VBA code to do the same.
Splitting Excel files manually can be tricky unless you have right tools. And here I will talk about 4 such tools that you can use to split an Excel sheet in to multiple sheets easily. And no matter if you have XLS or XLSX file, you can split them all easily and in just few seconds.
4 Free Excel Splitter to Split Excel Sheet into Multiple Sheets:
ASAP Utilities
ASAP Utilities is a very nice add-in for Excel which you can use on your PC to split an Excel Sheet. It has tons of utilities in it that allow you to manage an Excel sheet. You just have to invoke those utilities with a couple of clicks. However, the free version of ASAP Utilities is only meant for educational and home use.
ASAP Utilities adds a separate tab in Excel and you will see the list of utilities supported by it. In that list, one of the utility can split an Excel sheet. You can specify number of rows you want in each file and it will do that for you. Also, using this tool, you can either leave the sheets that you split in Excel itself, or you can export them as separate Excel files.
Here are a few steps to use ASAP Utilities to split an Excel sheet into multiple sheets.
Step 1: Get ASAP Utilities Setup using the above link and then install it. You will see a separate tab will be added in Excel. After that, open the target sheet in it that you want to split.
Step 2: Now, from the sheet, select the table you want to split. After that, you can use the “Split the selected range into multiple worksheets” option from the Range dropdown.
Step 3: Now, specify how many rows you want in each file. And then hit the “OK” button. It will immediately split the current file and you will see that it creates the separate sheets in Excel itself.
Step 4: Now, if you are okay with that data in Excel, then it is okay. Otherwise, you can opt to export those splitted sheets as separate Excel files. To do that, you can use the “Export worksheets as separate files” option from Sheets dropdown.
This way, you can use this powerful add-in pack for Excel to split an Excel sheets in to multiple sheets with ease. All it requires is a couple of clicks and then you can split an Excel sheet in to various sheets in a few seconds.
Splitexcel
Splitexcel is a handy tool that you can use in Windows to split an Excel workbook by exporting its inner sheets. It is basically a shell extension for Windows that you can use to split an Excel file from the context menu. You can easily install it and then use it wherever you want. When you use this, it saves the splitted files in the same directory where the target Excel sheet is. It works in a very straightforward manner and hardly takes a few seconds to do its work.
Using this tool for splitting an Excel file is very simple. You just have to download it from the above link and then run the “install.bat” file. It will add an extract option in the context menu. After installing this tool, you can right click on target Excel file and then select the “Split every sheet in a separate Excel file” option. As you do that, it will immediately extract all the sheets from the selected file and will save them in the same folder. You can see the above screenshot.
Pythonpyxl
Pythonpyxl is another free tool that you can try to split an Excel sheet into multiple other sheets. It is basically a Python script that takes an Excel file and then split it according to the various sheets that it has. And you just have to double click on the script to run it and split an Excel file. Just like the tool above, it works in a straightforward manner. Just run it once and split an Excel sheet in blink of an eye.
Using this script is very simple. You have to clone its repository from GitHub using the above link. After that, extract the ZIP file that you have downloaded and then save the “sample3_pyxl.py” file to some directory of your choice. Next, copy the Excel sheet that you want to split and then place that in the same folder where the “sample3_pyxl.py” file is. Make sure that you have Python Installed on your PC.
Now, rename the Excel file to “sample3.xlsx”. After that, just double click on the script and then wait for a few seconds. You’ll see that it will extract the sheets from the source EXE file one by one and will place them in the same folder. After getting the splitted Excel files, you can do whatever you want.
Split Excel Sheet into Multiple Sheets using VBA Code
This is the last method in this post to split an Excel sheet. Here I will use a VBA code snippet in Excel to split an Excel file. Basically, using this method, you can split an Excel sheet according to the number of rows in just one click. It saves the splitted files as separate Excel files in the same directory where the source Excel file is. If you know how to run VBA code inside Excel, then it will be too easy to use this method. And if you don’t know about that, then I will explain that.
Here are some steps to use this VBA code to split an Excel sheet into multiple sheets using VBA code.
Step 1: Open Excel and then open the target sheet in it that which you want to split. After that, open the VBA editor as well using Alt + F11 keyboard shortcut.
Step 2: Now, click on Insert > New Module. After that,paste the following code in the module that you have just created.
Sub Test()
Dim wb As Workbook
Dim ThisSheet As Worksheet
Dim NumOfColumns As Integer
Dim RangeToCopy As Range
Dim RangeOfHeader As Range 'data (range) of header row
Dim WorkbookCounter As Integer
Dim RowsInFile 'how many rows (incl. header) in new files?
Application.ScreenUpdating = False 'Initialize data
Set ThisSheet = ThisWorkbook.ActiveSheet
NumOfColumns = ThisSheet.UsedRange.Columns.Count
WorkbookCounter = 1
RowsInFile = 10 'as your example, just 10 rows per file 'Copy the data of the first row (header)
Set RangeOfHeader = ThisSheet.Range(ThisSheet.Cells(1, 1), ThisSheet.Cells(1, NumOfColumns))
For p = 2 To ThisSheet.UsedRange.Rows.Count Step RowsInFile - 1
Set wb = Workbooks.Add 'Paste the header row in new file
RangeOfHeader.Copy wb.Sheets(1).Range("A1") 'Paste the chunk of rows for this file
Set RangeToCopy = ThisSheet.Range(ThisSheet.Cells(p, 1), ThisSheet.Cells(p + RowsInFile - 2, NumOfColumns))
RangeToCopy.Copy wb.Sheets(1).Range("A2") 'Save the new workbook, and close it
wb.SaveAs ThisWorkbook.Path & "\test" & WorkbookCounter
wb.Close 'Increment file counter
WorkbookCounter = WorkbookCounter + 1
Next p
Application.ScreenUpdating = True
Set wb = Nothing
End Sub
Step 4: At the line number 16, specify the number of rows that you want to have in each file. After that, run the code by either pressing the F5 key or you can use the play button from the toolbar to run it. When the code runs successfully, it will leave the splitted files in the directory where the original Excel file was.
In this way, you can use a VBA code to split an Excel file in just a few seconds. And you don’t need to have any technical knowledge to run it. This VBA code can be used on any Excel file no matter how big it is.
Also see:
- How to Find Largest Sheet in Excel File By Size
- Automatically Fill PDF Forms in Bulk using Excel Sheet
- How to Remove Leading, Trailing Spaces from Entire Excel
Final thoughts
These are the best free Excel splitters that I have found so far. All the tools and methods that I have listed can split any Excel file easily. If you still split Excel files by manual copy-paste, then you may try any method listed here. I have added some tools that can split the Excel based on inner sheets and some of them can even split an Excel file based on number rows. So, depending on your needs, you can try any tool and save your time and effort.
