This tutorial explains how to split Excel sheet into multiple files based on column. Here I will talk about two different methods to split Excel sheet into different sheets. These methods basically go for a specified column and then group the unique values. After that, they extract the data for these values and then put it in different sheets. Later you can save those sheets as separate Excel files. In the following tutorial, I will do the same using an Excel plugin and through VBA code.
If you have a single Excel file which contains repeated data about an entity then you can split that. For instance, if you have record about different clients in your sheet with multiple records, then you can split that in separate sheets. This will result in an organized data and you will end up having data about different clients in different sheets. All you have to do is just hit a few clicks to execute VBA code.
How to Split Excel Sheet into Multiple Files Based on Column?
Method 1: Split Excel Sheet into Multiple Files Based on Column using VBA Code
If you know how to execute VBA code inside Excel, then this is one of the best methods to easily split an Excel sheet. Here I will list two VBA snippets that you can run in the target Excel sheet. The fist snippet extracts data from the specified column and will place the data in different sheets. The second snippet will save those sheets as separate files in the same directory where the source Excel File is.
Here are the steps to use VBA code to split Excel sheet based on column.
Step 1: Open Excel and then load the sheet in it that you want to split. After that, open VBA editor by pressing Alt + F11 key combination.
Step 2: In the VBA editor, you can right click in left pane to insert a new module. See the screenshot below. And then in the new module, you can paste this code.
Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
vcol = 1
Set ws = Sheets("Sheet1")
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1:C1"
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
End Sub
Step 3: Now, run this code by hitting the F5 key. Or, you can click on the play button from the toolbar to do the same. As you do that, you will see that it will create different sheets with the different unique values from the column.
Step 4: Now, at this point, we still have sheets saved in Excel File. To export them as separate Excel files, you have to run another VBA code. Delete all the code from the current window and then paste the following code. Run it and then it will create separate files in the same folder where the source Excel file was.
Sub Splitbook()
'Updateby20140612
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
In this way, you can split an Excel sheet into multiple sheets based on column. All you have to do is run two different code snippets and get the job done at your fingertips.
Method 2: Split Excel Sheet into Multiple Files Based on Column using an Excel Plugin
Data XL is a free Excel addin which you can use to split an Excel sheet into multiple files based on column. You can simply install it and then use it to split any Excel sheet. It works in exactly same manner like the above code does. You can install it through its XALM file and then start using it to split sheets based on the column. It provides you with a simple GUI to handle the sheet splitting operation. It just takes the range that you want to split along with the column that will be used to split the sheet. After that, just a couple of clicks you have to press and you will end up having multiple Excel files in the same directory.
Here are steps to use this plugin to split an Excel sheet into multiple files based on column.
Step 1: Download the Data XL plugin for Excel from above link and then you can open it by just double clicking on it.
Step 2: Open the Excel sheet that you want to split through this plugin. After that, you can click on “Split Data Tool” from the “DATA XL” tab of the Excel ribbon. After that, fill out the details like data range, specify the column that will be used to split the data, etc.
Step 3: Finally, hit the “Split Now” button to split the sheet according to the column that you have specified. It will immediately split the sheet and will create separate worksheet into different tabs of Excel. See this screenshot.
Although there is an option to directly specify a folder where you want to save split data as separate Excel files. But this option doesn’t seem to be working in Windows 10. So, after you have the split data in different sheets, you can save them to separate Excel files using VBA code that I have mentioned in the above method.
Final thoughts
The methods that I have explained above can easily split Excel sheet based on a column. It depends on you which method you want to use to split an Excel. Both the methods work fine and can easily split an Excel sheet in just a few seconds. So, if you are looking for ways to split Excel sheet according to one of its column, then this tutorial will be helpful.
