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 the multiple sheets after splitting up the main sheet. To know the ways of doing this job effectively let’s dive into the article.

Download Workbook

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

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

For this purpose, I am using Microsoft Excel 365 version, but you can use any other versions at your convenience.

split Excel sheet into multiple sheets based on column value

Method-1: FILTER Function to Split Sheet into Multiple Sheets Based on Column Value

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

split Excel sheet into multiple sheets based on column value

Step-01:
➤Create three sheets named after the three students’ names.
➤Select a cell, like B3 in the sheet for the student Daniel Defoe.

using FILTER function

➤Type the following formula

=FILTER(Filter!B5:D16,Filter!B5:B16="Daniel Defoe")

Filter!B5:D16 is the data range without header in the main sheet which is named as Filter and Filter!B5:B16 is the range of the Student Name in the main sheet and it would be equal to “Daniel Defoe”.

using FILTER function

➤Press ENTER
Now, you will get the data for the student Daniel Defoe in the sheet for this student.

using FILTER function

➤Let’s type the header name above all of the columns and create a border for this data table.

using FILTER function

Step-02:
After following Step-01 of this method for the other two sheets for the student’s Henry Jackson and Donald Paul you will get the following two tables in their respective sheets.

using FILTER function
split Excel sheet into multiple sheets based on column value

Read More: Excel VBA: Split Sheet into Multiple Sheets Based on Rows

Method-2: Pivot Table to Split 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 Pivot Table.

split Excel sheet into multiple sheets based on column value

Step-01:
➤Go to Insert Tab>>PivotTable Option

split Excel sheet into multiple sheets based on column value

Then PivotTable from table or range Dialog Box will appear.
➤Select the Table/Range
➤Click on New Worksheet (it’s a good practice to place Pivot Table into a new sheet)
➤Press OK

using Pivot Table

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

using Pivot Table

➤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.

using Pivot Table

➤Go to Design Tab>>Layout Group>>Report Layout Dropdown>>Show in Outline Form Option

using Pivot Table

➤Follow Design Tab>>Layout Group>>Grand Totals Dropdown>>Off for Rows and Columns Option.

using Pivot Table

➤Then go to PivotTable Analyze Tab>>PivotTable Group>>Options Dropdown>>Show Report Filter Pages Option.

using Pivot Table

Then the Show Report Filter Pages Wizard will pop up.
➤Select the column Student Name which was in the Filters area.
➤Press OK.

using Pivot Table

Result:
Afterward, you will get three different sheets for the three students Daniel Defoe, Henry Jackson, and Donald Paul respectively.

using Pivot Table
using Pivot Table
split Excel sheet into multiple sheets based on column value

Read More: Split Excel Sheet into Multiple Sheets Based on Rows

Method-3: Using Table Option

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

split Excel sheet into multiple sheets based on column value

Step-01:
➤Create three sheets named after the three students’ names.
➤Copy the data table from the main sheet and paste it into these three different sheets.

using Table Option

Step-02:
➤Go to Insert Tab>>Table Option

split Excel sheet into multiple sheets based on column value

Then the Create Table Dialog Box will appear.
➤Select the data for your Table.
➤Click on My table has headers
➤Press OK

using Table Option

Then, the following table will be created.

using Table Option

➤Go to Table Design Tab>>Tools Dropdown>>Insert Slicer Option

using Table Option

After that Insert Slicers Dialog Box will pop up.
➤Select the Student Name column (the column based on which you want to split the sheet)
➤Press OK.

using Table Option

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

split Excel sheet into multiple sheets based on column value

➤Click on Daniel Defoe for the sheet of this student.

Result:
You will get the data for the student Daniel Defoe in the sheet for this student.

using Table Option

Step-03:
➤Follow Step-02 for the other two sheets.
In this way, you will create the other two sheets for Henry Jackson and Donald Paul like below.

using Table Option
split Excel sheet into multiple sheets based on column value

Read More: How to Split Excel Sheet into Multiple Files (3 Quick Methods)


Similar Readings


Method-4: Using Filter Option

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

split Excel sheet into multiple sheets based on column value

Step-01:
➤Create three sheets named after the three students’ names.
➤Copy the data table from the main sheet and paste it into these three different sheets.

using Filter option

Step-02:
➤Select the data table
➤Go to Data Tab>>Filter Option

split Excel sheet into multiple sheets based on column value

Then, the Filter Option will be activated for this data table.
➤Click on the Dropdown sign in the Student Name column.

using Filter option

➤Select the name Daniel Defoe for this sheet and Press OK.

using Filter option

Result:
Afterward, you will get the data for the student Daniel Defoe in the sheet for this student.

using Filter option

Step-03:
➤Follow Step-02 for the other two sheets.
Then, you will get the other two sheets for Henry Jackson and Donald Paul like below.

using Filter option
split Excel sheet into multiple sheets based on column value

Read More: How to Separate Sheets in Excel (6 Effective Ways)

Method-5: VBA Code to Split 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.

split Excel sheet into multiple sheets based on column value

Step-01:
➤Go to Developer Tab>>Visual Basic Option

split Excel sheet into multiple sheets based on column value

Then, the Visual Basic Editor will open up.
➤Go to Insert Tab>> Module Option

VBA Code

After that, a Module will be created.

VBA Code

Step-02:

➤Write the following code

Sub Splitsheet()
Dim lr As Long
Dim sheet As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWSTRg As Worksheet
On Error Resume Next
Set xTRg = Application.InputBox("Select the header row:", "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox _
("Select the column on the basis of which split data:", "", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set sheet = xTRg.Worksheet
lr = sheet.Cells(sheet.Rows.Count, vcol).End(xlUp).Row
title = xTRg.AddressLocal
titlerow = xTRg.Cells(1).Row
icol = sheet.Columns.Count
sheet.Cells(1, icol) = "Unique"
Application.DisplayAlerts = False
If Not Evaluate("=ISREF('xTRgWs_Sheet!A1')") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
Else
Sheets("xTRgWs_Sheet").Delete
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
End If
Set xWSTRg = Sheets("xTRgWs_Sheet")
xTRg.Copy
xWSTRg.Paste Destination:=xWSTRg.Range("A1")
sheet.Activate
For i = (titlerow + xTRg.Rows.Count) To lr
On Error Resume Next
If sheet.Cells(i, vcol) <> "" And Application.WorksheetFunction. _
Match(ws.Cells(i, vcol), sheet.Columns(icol), 0) = 0 Then
sheet.Cells(sheet.Rows.Count, icol).End(xlUp).Offset(1) = sheet.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(sheet.Columns(icol). _
SpecialCells(xlCellTypeConstants))
sheet.Columns(icol).Clear
For i = 2 To UBound(myarr)
sheet.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
xWSTRg.Range(title).Copy
Sheets(myarr(i) & "").Paste Destination:=Sheets(myarr(i) & "").Range("A1")
sheet.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr) _
.EntireRow.Copy Sheets(myarr(i) & "").Range("A" & (titlerow + xTRg.Rows.Count))
Sheets(myarr(i) & "").Columns.AutoFit
Next
xWSTRg.Delete
sheet.AutoFilterMode = False
sheet.Activate
Application.DisplayAlerts = True
End Sub

Here, Splitsheet() is the Sub procedure name and the variables lr, sheet, vcol, i, icol, myarr, title, titlerow, xTRg, xVRg, xWSTRg are declared as different data types by using the Dimension parameter.

Here, multiple IF and FOR loops have been used for splitting up the sheet into multiple sheets.

VBA Code

➤Press F5

Select the header row: Dialog Box will open up.
➤Select the range of the header row and Press OK.

split Excel sheet into multiple sheets based on column value

Then Select the column on the basis of which split data: Wizard will pop up.
➤Select the Student Name column and press OK.

VBA Code

Result:
Finally, you will get the three sheets for Daniel Defoe, Henry Jackson, and Donald Paul like below.

split Excel sheet into multiple sheets based on column value
VBA Code
split Excel sheet into multiple sheets based on column value

Here we have used the paste destination at the A1 cell, that’s why split data 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 like below in a sheet named Practice. Please do it by yourself.

Practice

Conclusion

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


Further Readings

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

3 Comments
  1. Hi

    Really useful tool – is there a limit on character length for the data being used. I’m having an issue where some tabs are not being prepared and that’s the only thing I can think of.

    Thanks

    • Hello Richard,
      Thanks for your question. The maximum number of sheets in a workbook is 255. So, if you have values for more than 255 rows in a sheet on the basis of which you will create multiple sheets then you may face a problem.

  2. Method 5 works like a charm. Very easy to use. Did not have to amend a single line.
    “Hardest” part was getting Excel to show Developer Menu on the band. 😀

Leave a reply

ExcelDemy
Logo