How to Split Excel Sheet into Multiple Sheets Based on Column Value

If you are looking for some of the easiest ways to split an Excel sheet into multiple sheets based on column value, then you will find this article useful.

Sometimes it becomes necessary to split a large set of data based on a column and work on multiple sheets after splitting up the main sheet. To know the ways of doing this job effectively let’s dive into the article.

Split Excel Sheet into Multiple Sheets Based on Column Value: 5 Simple Ways

We will use the following data table containing the result of different students at a college. Now, we will split this sheet into three sheets based on the Student Name column for the three students.

For this purpose, We are using Microsoft Excel 365 version, but you can use any other version at your convenience.

1. Use FILTER Function to Split Excel Sheet into Multiple Sheets

If you want to split the datasheet into multiple sheets based on the column Student Name, then you can use the FILTER function. Here, we will split the following sheet into three sheets containing the data for Daniel Defoe, Henry Jackson, and Donald Paul respectively.

Steps:

• Firstly, create three sheets named after the three students’ names.
• Then, select a cell, like Cell B3 in the sheet for the student Daniel Defoe.

• Next, type the following formula.

• After that, press Enter.
• Now, you will get the data for the student Daniel Defoe in the sheet for this student.

• Finally, let’s type the header name above all of the columns and format this data table.

• After following the above steps of this method for the other two sheets for the students’ Henry Jackson and Donald Paul, you will get the following two tables in their respective sheets.

2. Apply Pivot Table to Split Excel Sheet into Multiple Sheets Based on Column Value

You can split the following sheet into three sheets for the three students based on the column Student Name by using the Pivot Table.

Steps:

• In the beginning, go to Insert Tab>> click on PivotTable Option.

• Then, PivotTable from table or range Dialog Box will appear.
• Next, select the Table/Range.
• After that, click on New Worksheet (it’s a good practice to place Pivot Table into a new sheet).
• Lastly, press OK.

• Now, a new sheet will open up which has two parts; PivotTable1 and PivotTable Fields.

• Then, drag down the Student Name in the Filters area (any column on the basis of which you want to split the main sheet into multiple sheets) and Subject and Grade to the Rows area.

• Afterward, go to Design Tab >> click on Layout Group >> click on Report Layout Dropdown >> select Show in Outline Form Option.

• Again, from Design Tab >> click on Layout Group >> click on Grand Totals Dropdown >> select Off for Rows and Columns Option.

• After that, go to PivotTable Analyze Tab>> click on PivotTable Group >> click on Options Dropdown >> select Show Report Filter Pages Option.

• Now, the Show Report Filter Pages wizard will pop up.
• Next, select the column Student Name which was in the Filters area.
• Lastly, press OK.

• Finally, you will get three different sheets for the three students Daniel Defoe, Henry Jackson, and Donald Paul respectively.

3. Split Excel Sheet into Multiple Sheets Using Insert Slicer Option After Creating Table

For splitting the main sheet into multiple sheets based on the Student Name column you can use the Table Option.

Step-01: Create Multiple Sheets

We will create multiple sheets to split our dataset based on column value.

• Firstly, create three sheets named after the three students’ names.
• Secondly, copy the data table from the main sheet and paste it into these three different sheets.

Step-02: Insert Table

Next, we will show you how to insert a table to use Insert Slicer option.

• To start with, go to Insert Tab >> click on Table Option.

• Then, the Create Table dialog box will appear.
• Next, select the data for your Table.
• Afterward, turn on My table has headers option.
• Lastly, press OK.

• After that, the following table will be created.

Step-03: Use Insert Slicer Option

In the final step, we will use the Insert Slicer option from Table Design tab. Make sure you have clicked on any cell of the table to open this tab option.

• Then, go to Table Design Tab>> click on Tools Dropdown >> select Insert Slicer Option.

• Now, Insert Slicers dialog box will pop up.
• Next, select the Student Name column (the column based on which you want to split the sheet).
• Finally, press OK.

• After that, a Student Name box will appear which has three options (three student names).

• Now, click on Daniel Defoe for this sheet.
• Thus, You will get the data for the student Daniel Defoe.

• Similarly, you can create the other two sheets for Henry Jackson and Donald Paul as below.

4. Employ Filter Feature to Split Excel Sheet into Multiple Sheets

For splitting the main sheet into multiple sheets based on the Student Name column we will use the Filter option in this method.

Steps:

• To start with, create three sheets named after the three students’ names.
• After that, copy the data table from the main sheet and paste it into these three different sheets.

• Next, select the data table.
• Then, go to Data Tab >> click on Sort & Filter >> select Filter Option.

• Now, the Filter Option will be activated for this data table.
• After that, click on the Dropdown sign in the Student Name column.

• Lastly, select the name Daniel Defoe for this sheet and press OK.

• Finally, you will get the data for the student Daniel Defoe in the sheet for this student.

• Similarly, follow the above steps to get the other two sheets for Henry Jackson and Donald Paul as below.

Related Article: How to Split Excel Sheet into Multiple Worksheets

5. Use VBA to Split Excel Sheet into Multiple Sheets Based on Column Value

You can split a sheet into multiple sheets based on column value by using a VBA code like this method. To use this method, make sure your dataset starts from Cell A1 like the image shown below.

Steps:

• Firstly, go to Developer Tab >> click on Visual Basic Option.

• Then, the Visual Basic Editor will open up.
• Next, go to Insert Tab>> select Module Option.

• Now, write the following code in the module.

Sub Splitsheet1()
Dim lr1 As Long
Dim sheet As Worksheet
Dim verticle_colmn, i As Integer
Dim icolmn1 As Long
Dim dataset As Variant
Dim title1 As String
Dim titlerow1 As Integer
Dim title_range As Range
Dim verticle_range As Range
Dim datarange As Worksheet
On Error Resume Next
Set title_range = Application.InputBox("Select header row:", "", Type:=8)
If TypeName(title_range) = "Nothing" Then Exit Sub
Set verticle_range = Application.InputBox _
("Select the column range on the basis of which split data:", "", Type:=8)
If TypeName(verticle_range) = "Nothing" Then Exit Sub
verticle_colmn = verticle_range.Column
Set sheet = title_range.Worksheet
lr1 = sheet.Cells(sheet.Rows.Count, verticle_colmn).End(xlUp).Row
title1 = title_range.AddressLocal
titlerow1 = title_range.Cells(1).Row
icolmn1 = sheet.Columns.Count
sheet.Cells(1, icolmn1) = "Unique"
Application.DisplayAlerts = False
If Not Evaluate("=ISREF('title_rangeWs_Sheet!A1')") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "title_rangeWs_Sheet"
Else
Sheets("title_rangeWs_Sheet").Delete
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "title_rangeWs_Sheet"
End If
Set datarange = Sheets("title_rangeWs_Sheet")
title_range.Copy
datarange.Paste Destination:=datarange.Range("A1")
sheet.Activate
For i = (titlerow1 + title_range.Rows.Count) To lr1
On Error Resume Next
If sheet.Cells(i, verticle_colmn) <> "" And Application.WorksheetFunction. _
Match(ws.Cells(i, verticle_colmn), sheet.Columns(icolmn1), 0) = 0 Then
sheet.Cells(sheet.Rows.Count, icolmn1).End(xlUp).Offset(1) = sheet.Cells(i, verticle_colmn)
End If
Next
dataset = Application.WorksheetFunction.Transpose(sheet.Columns(icolmn1). _
SpecialCells(xlCellTypeConstants))
sheet.Columns(icolmn1).Clear
For i = 2 To UBound(dataset)
sheet.Range(title1).AutoFilter field:=verticle_colmn, Criteria1:=dataset(i) & ""
If Not Evaluate("=ISREF('" & dataset(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = dataset(i) & ""
Else
Sheets(dataset(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
datarange.Range(title1).Copy
Sheets(dataset(i) & "").Paste Destination:=Sheets(dataset(i) & "").Range("A1")
sheet.Range("A" & (titlerow1 + title_range.Rows.Count) & ":A" & lr1) _
.EntireRow.Copy Sheets(dataset(i) & "").Range("A" & (titlerow1 + title_range.Rows.Count))
Sheets(dataset(i) & "").Columns.AutoFit
Next
datarange.Delete
sheet.AutoFilterMode = False
sheet.Activate
Application.DisplayAlerts = True
End Sub

• Afterward, click on the save button to save the code and return back to your worksheet.

• Further, go to the Developer tab >> click on Macros.

• Now, the Macros box will appear.
• Next, select Splitsheet1.
• After that, click on Run.

• Then, Select header row: Dialog Box will open up.
• Select the range of the header row and press OK.

• After that, Select the column on the basis of which split data: Wizard will pop up.
• Select the Student Name column and press OK.

• Finally, you will get the three sheets for Daniel Defoe, Henry Jackson, and Donald Paul as below.

Here we have used the paste destination at the A1 cell, that’s why split datasets are started from that cell.

Read More: How to Split a Workbook to Separate Excel Files with VBA Code

Practice Section

For doing practice by yourself we have provided a Practice Section. Please do it by yourself.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

In this article, we tried to cover the easiest ways to split an Excel sheet into multiple sheets based on column values effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.

Related Articles

• How to Split Sheet into Multiple Sheets Based on Rows in Excel
• How to Split Excel Sheet into Multiple Files
• How to Split Screen in Excel
• How to Enable Side-by-Side View with Vertical Alignments in Excel
• How to View Excel Sheets in Separate Windows
• [Fix:] Excel View Side by Side Not Working
• Excel VBA: Split Sheet into Multiple Sheets Based on Rows

<< Go Back to Split Excel Cell | Excel Worksheets | Learn Excel