How to Divide Large Data into Multiple Excel Files?

An oversized Excel file slows down Excel, is hard to share, and gets trimmed when exceeding the Excel row limit. Splitting is the most obvious way to solve it. You can divide large Excel file into multiple files based on rows, columns, and the number of individual worksheets an Excel file has.

Moving further in this blog, we will discuss different methods to split large Excel files using the Excel built-in tools and professional Excel splitter software. But before that, let us see what it means to divide a large Excel file.

What Does It Mean to Divide Large Data in Excel?

Dividing an Excel file means separating the records and saving them in different Excel files. This helps to solve issues created by a large Excel file. You can divide an Excel file based on:

• By Rows: If your Excel file has 100,000 rows, you can break it into 100 new smaller Excel files, each having 1000 rows. The 100 files or 1000 rows is just for explanation, not a limit.
• By Columns: Saving data from column A to F in one Excel file and from column F to the last column in another file. Taking this approach to divide large Excel file into multiple files is less common.
• By Sheet: If your Excel file contains multiple worksheets, you can create a separate Excel file for each of the sheets. For example, creating 10 Excel files from an Excel workbook having 10 sheets.

Splitting the Excel files serves multiple benefits, like small Excel files are easy to share and do not crash the Excel application. In the next section, we will discuss the methods to divide large Excel file into multiple files.

Methods to Split Large Excel File into Multiple Files

There are three working manual methods to divide large Excel files. Let’s view them one by one.

Method 1: Selective Copy and Paste

This is the simplest method right now that requires no technical knowledge and is most suitable for someone who needs to break a few thousand rows into smaller data sets. The steps for splitting Excel rows with copy/paste are:

Step 1: Open the Excel file that you want to split.

Step 2: Copy a specific number of rows from the Excel file and paste the rows into a new blank Excel sheet.

For example, if your Excel file has 1000 rows and you want to divide it into 2 Excel files, each having 500 unique rows, then copy from row 1 to row 500 and save the data in a new black sheet. Similarly, copy row 501 to row 1000 and save the data to another new blank sheet. You can use this approach to either divide Excel by row or by column.

Method 2: Divide Large Excel File Based on Rows Using a VBA Script

If your Excel files have more than 100 thousand rows, manually dividing them will take a very long time. A faster way to divide rows based on number of rows is given on a Microsoft tech community discussion thread, where they used a VBA script, as shown in the steps below:

Step 1: Open the Excel file and press Alt + F11.

Step 2: Click Insert and select the Module option.

Step 3: A box will pop up. Paste the following script into the box.

Sub SplitByRows()
Dim ws As Worksheet, NewWb As Workbook
Dim LastRow As Long, SplitSize As Long
Dim i As Long, FileCount As Long
Dim FilePath As String

Set ws = ActiveSheet
LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
SplitSize = 500
FilePath = ThisWorkbook.Path

Application.ScreenUpdating = False

For i = 2 To LastRow Step SplitSize - 1
FileCount = FileCount + 1
Set NewWb = Workbooks.Add
ws.Rows(1).Copy NewWb.Sheets(1).Rows(1)

If i + SplitSize - 2 <= LastRow Then
ws.Rows(i & ":" & (i + SplitSize - 2)).Copy NewWb.Sheets(1).Rows(2)
Else
ws.Rows(i & ":" & LastRow).Copy NewWb.Sheets(1).Rows(2)
End If

NewWb.SaveAs FileName:=FilePath & Application.PathSeparator & "Split_" & FileCount & ".xlsx"
NewWb.Close
Next i

Application.ScreenUpdating = True
MsgBox "Done! Created " & FileCount & " files."
End Sub

Step 4: Edit the value for “SplitSize” as per your requirement. This is the value for the number of rows based on which the split will occur.

Step 5: To run the script, click Run and select Run Sub/UserForm F5.

Step 6: The current Microsoft Visual Basic for Applications window will close, and the script will be executed in the background. Do not perform any action until the script is executed completely. When the operation is successful, you will receive a pop up specifying the number of new smaller Excel files created.

Method 3: Divide the Excel File Based on Sheets

If you have a large Excel workbook that has multiple sheets and you want to create a new Excel file for each of those worksheets, you can do it manually or with a VBA script. Like, if a single Excel file has 5 sheets, you can create 5 separate Excel files for each sheet.

If you prefer manual approach, follow these steps:

• Open the Excel file with multiple sheets.
• Right-click on a sheet that you want to save in a new Excel file and select Move or Copy

• Select the dropdown and choose (new book) option. Then, click OK.

• The sheet will open in a new Excel file. Now, click File > Save as and save the file.

If there are multiple sheets in your Excel file, you can use the Microsoft Visual Basic script in Excel to split large Excel file into multiple files. For this, open the Excel file (if not open) and press Alt + F11. Then, click Insert > Module and paste the following script:

Sub SplitEachWorksheet()
' Define variables
Dim FPath As String
Dim NewWorkbook As Workbook
Dim CurrentWorksheet As Worksheet
' Set the file path to the location of the current workbook
FPath = Application.ActiveWorkbook.Path
' Add a backslash to the end of the path if it doesn't already have one
If Right(FPath, 1) <> "\" Then
FPath = FPath & "\"
End If
' Turn off screen updating and alerts for faster and smoother operation
Application.ScreenUpdating = False
Application.DisplayAlerts = False

' Loop through each worksheet in the active workbook
For Each CurrentWorksheet In ActiveWorkbook.Sheets
' Copy the current worksheet to a new workbook
CurrentWorksheet.Copy
Set NewWorkbook = ActiveWorkbook

' Save the new workbook with the sheet name as the filename and .xlsx format
NewWorkbook.SaveAs Filename:=FPath & CurrentWorksheet.Name & ".xlsx", FileFormat:=xlOpenXMLWorkbook
' Close the new workbook
NewWorkbook.Close SaveChanges:=False
Next CurrentWorksheet

' Turn alerts and screen updating back on
Application.DisplayAlerts = True
Application.ScreenUpdating = True

' Confirmation message
MsgBox "All worksheets have been successfully split into separate files in the folder: " & FPath
End Sub

After pasting the script, click Run > Run Sub/UserForm F5 or simply press the F5 button to run the script.

Note: Make sure the folder where the large Excel file is stored does not have other Excel files. Because running the above script may overwrite existing Excel files.

Method 4: Using Power Query (For Advanced Users Only)

If your requirement is to divide the Excel file based on different parameters, like creating a separate Excel file based on a specific column or a value, performing mathematical calculations, and then saving the analysis in a different Excel file, you can use Excel Power Query.

But these are highly advanced tools; only use them if you are technically skilled in them. And make sure to create a copy of the original Excel file before you run any operation on the dataset.

Common Problems When Splitting Large Excel Files

The manual methods we discussed above are free and work for most of the cases. However, some users may face the following problems when trying to divide large Excel file into multiple files, such as:

• Time Taking: For a few hundred rows, manual data selection and segregation is doable. But doing the same with an Excel file with thousands of rows will take a considerable amount of time. Even if you use the VBA scripts, dividing a huge data set will not happen instantly.
• Risk of Skipping Rows:Another risk with manual selection is skipping rows. While copying rows, it is hard to keep track of how much data is segregated. Moreover, the desktop version of Excel has a hard limit. It can only show up to 1,048,576 rows in a sheet. If your Excel file has more rows than the predefined limit, Excel will not display them.
• Requires Technical Expertise:Running and editing the macro scripts is not ideal for all. A regular user accessing the VBA for the first time may create a syntax error, resulting in failed query execution.
• Need an Active Microsoft Office License:The methods like running VBA scripts and using Power Query only work if you are using the desktop version of Microsoft Office. The free Web version does not have support for these two. Moreover, you need an active Office license to use Microsoft Excel on desktop.
• Cannot Split Corrupt Excel Files:You cannot open corrupt Excel files using the Excel, it will either crash or give errors. In this case, you must first repair Excel file and then split them using the manual methods.

In addition to the above issues, using the manual methods, you can only use one instance of Excel to open and split a single Excel file. For splitting multiple files, you have to either do it one by one. Or open multiple instances of Excel for each Excel file that you want to divide.

An Easy Way to Split Large Excel File in Bulk

If you have multiple large Excel sheets or you do not want the hassle of running complex scripts in Microsoft Excel, then a dedicated Excel splitting tool can help. You can use the Kernel Excel Splitter. It can accurately split large Excel file into multiple files.

The tool allows you to divide Excel files based on rows, file size, or the number of sheets in the file. Split single or multiple large Excel sheets at once without Excel. Every row is saved with original formatting, and you can choose which row will be the header in every new Excel file.

Which Method to Choose to Divide Large Excel File into Multiple Files?

It depends on your requirements. For a single Excel file with a few thousand rows, manual copy/paste is the simplest solution. To split an Excel file based on the sheets it has, run the VBA script, and power users can try the Power Query. Remember, you must have an active Microsoft Office subscription to use Excel to divide large Excel file into multiple files.

Moreover, the fastest and easiest way to divide multiple large Excel files is to use the Kernel Excel Splitter tool. You can download the trial version and test the tool for free.

• Download Free
• Buy Now