Read time: 9 minutes
User Query: “It was Friday evening when almost everyone had departed from the office, and I was all alone in my bay trying to copy data from one of the Excel worksheets. After selecting the data from more than 80,000 entries, as I was about to press Ctrl + C, the Excel application froze. I had to share the sheet data with my senior in 30 minutes, and the cursor was still circling. I am looking for a solution to manage my large Excel files to avoid this situation in the future.”
Large Excel sheets and workbooks can often show errors like Workbook almost too large, Excel application crashing/ freezing, and Not enough system resources. This can cause delays in your performance and result in missed deadlines, which you surely do not want to happen. To avoid this issue, you can split large Excel file into multiple files, making the data more manageable.
With this article, you will learn why and how to split large Excel workbooks into multiple sheets or files for easy data organization and management. This will help you avoid corruption errors and optimize your performance.
Why should you break an Excel workbook into multiple files?
An Excel workbook can contain multiple worksheets, which are shown as tabs on the bottom of the file. Here are some pointers that explain how dividing a large Excel file helps to make the workflow smooth:
- Dividing Excel file into smaller files helps with quick loading of sheets, delivering better performance.
- When you fragment Excel files, you get ease at navigation and data management.
- Split a large file into multiple small Excel files and assign them to different users for enhanced team productivity.
- Helps to reduce clutter and organize data in a better way by splitting data into categories/ departments.
- Provides better data comparisons with easier side-by-side viewing of data in different Excel files.
- Easy sharing of files with other users without any limitations.
How to break a large Excel file into multiple files?
There are several methods to split the file, which are mentioned for your understanding in the sections below one by one. Go through them all to find which one best suits your requirements.
Solution 1: Use filters or categories to segregate
By applying the filters in the Excel files, you can sort out and save the data to different files:
Step 1: Launch the Excel file you want to break.
Step 2: From the right corner of the menu bar, apply the Sort & Filter on the column data to split.
Step 3: Using the filter dropdown, select the first category to split the data from the selected column.
Step 4: Copy all the columns showing as a result of the applied filter.
Step 5: Open a new workbook (Ctrl + N) and paste the copied data into it.
Step 6: Save it using a file name that describes the stored data.
Step 7: Now, perform this process again and again to split the file for each category needed.
Solution 2: Split Excel using Microsoft VBA
With the Microsoft VBA, you can split a large workbook into multiple workbooks or simply save each worksheet from the workbook as individual workbooks. Here is how to do this:
Step 1: In your Excel workbook, press Alt + F11 to open VBA editor.
Step 2: In the Project pane, double-click on the workbook/ worksheet name.
Step 3: It will open code window where you need to paste the below VBA Code.
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
Step 4: Save the mail Excel file using the extension .xlsm.
Step 5: Create a new folder at the destination where source file is stored to allow macro save new files in the same location.
Step 6: Now, go back to the worksheet and use Alt + F8 to open Macro dialog box.
Step 7: Select SplitEachWorksheet and click Run then Run Sub/ UserForm.
This code will split each worksheet from the workbook and save them individual Excel files.
Solution 3: Split Excel files using Save As option
In this method, we will use the Save As option from Excel, as explained in the following steps:
Step 1: Open your Excel file and select the data range as required.
Step 2: Go to File and then click Save As.
Step 3: Choose XLSX or CSV from the saving options under Save As list.
Step 4: Name the file and then delete the selected data from the sheet.
Step 5: Repeat the steps for more sections of data.
Solution 4: Move or Copy method to break Excel files
This method is a great choice if you want to split according to each worksheet of Excel Workbook. Follow the steps:
Step 1: Open your Excel workbook that contain multiple worksheets.
Step 2: Right-click on the Sheet tab below and select Move or Copy.
Step 3: From Move or Copy window, select new book under To Book dropdown.
Step 4: Save the new book as an independent Excel file on your system.
Solution 5: Use the Copy Paste method to split file
Step 1: Open Excel sheet and select the rows/ columns to split from the file.
Step 2: Use Ctrl+ C keys at once to copy the selected data.
Step 3: Create a new Excel sheet and paste the data using Ctrl + V.
Step 4: Once done, save the data to your computer and provide a name to the file.
You can repeat these steps to save the remaining data as individual files.
Solution 6: Splitting files with Power Query
This might be the last manual method in this guide but is quite effective in fragmenting Excel files as described further:
Step 1: Go to Data in Excel file and click on Get Data and then the From File option.
Step 2: Load the oversized Excel file into the Power Query.
Step 3: Use the required filters and then use Close & Load To.
Step 4: Select any of the new query and then from Home tab, select Close & Load, and Close & Load To.
Step 5: Select Table and click From Table/Range.
Step 6: Take data into the Power Query and mark the checkbox My table has headers. Click OK.
Step 7: Select Group By to proceed.
Step 8: Choose Close & Load from the right menu bar above and then Close & Load To.
Step 9: Select Only Create Connection radio button and then click OK.
Step 10: Right click to choose Add as New Query.
Step 11: Tap on Table then New Worksheet and click OK.
After that, follow the Solution 4 to save each sheet as a separate file.
What is the best solution to split large Excel files?
Even with so many manual methods available, you often face issues in splitting the Excel file. This is because some of these methods require technical expertise, while the others carry the risk of losing data during copy-paste or other operations. There is no guarantee that the integrity of the split data will be maintained.
To avoid such complications, you need a robust Excel splitter tool like Kernel Excel Splitter that help with easy worksheet management. It allows you to split a single or multiple Excel files at once without any file size limitations. With this tool, you can split and save the file to XLSX, XLS, CSV, ODS, and XML file formats. To split files using this tool, there is no need to install Microsoft Excel on your computer. It helps to reduce manual efforts that otherwise might be wasted to split Excel files.
Conclusion
The blog summarized some manual methods to split large Excel file, along with a professional solution to do that. The choice of methods between these completely depends upon you. In case of a few Excel files, you can use the manual solutions; however, for bulk Excel splitting, choose a professional tool like Kernel Excel Splitter. It is designed with advanced features to simplify the process to break large Excel files into smaller ones. With this tool, you can split Excel files by size, rows, and columns into the desired saving format. This will help you avoid corrupt Excel files and optimize your performance.
Frequently Asked Questions
Ans. The simplest trick to manage large Excel file within new files is to:
1. Highlight the rows/ columns you want to split from the file
2. Use Ctrl+ C together to copy the selected data.
3. Create a new Excel sheet and paste the data using Ctrl + V.
4. Once done, save the data to your computer.
Ans. Here are some efficient practices you can follow to keep your Excel files organized:
1. Use a standard naming system for your new Excel sheets (include original file name as prefix or suffix for easy data location).
2. Save all the output files to a single location on cloud storage (OneDrive, Google Drive, or SharePoint) or a network drive.
3. Use consistent formatting in all the workbooks to minimize the risk of input errors.
