Excel: Split Data Into Multiple Sheets

Updated 10 November 2026 · Standard formula, shown below

Yes. Excel can split rows from one table into multiple worksheets, either by using a built-in PivotTable report filter, a short VBA macro, or Power Query for a repeatable transformation. The right method depends on whether you need a one-time copy or sheets that can be refreshed when the source data changes.

What splitting data into sheets means

Usually, “split data” means taking a single table and making a separate worksheet for each value in a chosen column. For example, a sales table with a Region column can become sheets for East, West, North, and South. Each sheet holds the rows matching its label, while the original table remains the source.

This is different from splitting text in a cell, such as separating a full name at a space, or dividing a workbook into separate files. Those tasks use other Excel features. Before splitting, confirm that your data has one header row, consistent columns, and a clear field to use as the grouping value.

Choose a method

PivotTable report filter: A convenient built-in option when each output sheet can contain a filtered PivotTable summary rather than a copied, editable list of source rows.
VBA macro: Useful for making separate worksheets that contain the original rows for each unique value. It is suited to one-time or repeatable desktop Excel tasks, but macros require permission to run.
Power Query: Strong for importing, cleaning, and refreshing data. It can filter and shape a query, but creating and maintaining many separate worksheet outputs may take additional setup.
Manual filtering and copying: Practical for a very small number of categories, but easy to repeat incorrectly as the number of groups grows.

For a quick, no-code split into PivotTable reports, use the first method. If you need actual filtered copies of the source rows on separate tabs, a macro is commonly the most direct option.

Use a PivotTable to create report sheets

Excel can create a separate PivotTable report page for each item in a report-filter field. This produces a worksheet per category, but the result is a PivotTable, not a plain copy of the original records.

Make sure the source range has a distinct header for every column and no completely blank rows or columns within the table.
Select a cell in the data, then choose Insert > PivotTable. Confirm the table or range and choose where to place the PivotTable.
In the PivotTable Fields pane, drag the field you want to split by, such as Region, into the Filters area. Put other fields into Rows, Columns, or Values as appropriate.
Select a cell in the PivotTable and use the PivotTable tab’s Options or Analyze controls to find Show Report Filter Pages. The tab name can vary by Excel version and platform.
Select the report-filter field. Excel creates a separate sheet for each item in that field.

Review the generated sheets and their names. If you need every underlying transaction in ordinary cells, use filtering and copying, Power Query, or a macro instead; a PivotTable summarizes and organizes data according to its layout.

Split source rows with a VBA macro

A macro can loop through distinct values in one column, create a worksheet for each value, and copy matching rows along with the header. Save a backup first, and confirm that macros are allowed in your work environment. The following example assumes the data is on the active sheet, headers are in row 1, and the grouping field is in column A.

Press Alt+F11 to open the Visual Basic Editor, then choose Insert > Module.
Paste the code below into the module. Change the worksheet name and column number to match your data.
Return to Excel, select the source worksheet, and run SplitByColumnA from the macro list.

Sub SplitByColumnA()
    Dim src As Worksheet, lastRow As Long, r As Long
    Dim key As String, ws As Worksheet, nextRow As Long
    Dim seen As Object

    Set src = ActiveSheet
    Set seen = CreateObject("Scripting.Dictionary")
    lastRow = src.Cells(src.Rows.Count, 1).End(xlUp).Row

    For r = 2 To lastRow
        key = Trim(CStr(src.Cells(r, 1).Value))
        If Len(key) > 0 And Not seen.Exists(key) Then
            seen.Add key, True
            Set ws = Nothing
            On Error Resume Next
            Set ws = ThisWorkbook.Worksheets(Left(key, 31))
            On Error GoTo 0
            If ws Is Nothing Then
                Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
                ws.Name = Left(key, 31)
            End If
            src.Rows(1).Copy Destination:=ws.Rows(1)
            nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
            For Each cell In src.Range("A2:A" & lastRow)
                If Trim(CStr(cell.Value)) = key Then
                    src.Rows(cell.Row).Copy Destination:=ws.Rows(nextRow)
                    nextRow = nextRow + 1
                End If
            Next cell
        End If
    Next r
End Sub

This example is intentionally basic. It does not resolve every possible worksheet-name conflict: Excel sheet names cannot exceed 31 characters, cannot contain certain characters, and must be unique. Two different category labels can become identical after shortening, for example. For dependable recurring use, add name-cleaning and duplicate-name handling, or use a tested organizational macro. Also declare the loop variable explicitly in production code, such as Dim cell As Range.

Keep the split accurate and maintainable

Check the output against the source before sharing it. Count the source rows and the rows across the output sheets; every nonblank category row should appear once, unless your rules intentionally exclude it. Decide how to handle blank categories, spaces at the start or end of values, and labels that differ only by capitalization.

Keep an untouched source: A split is often a snapshot. Editing a copied row does not update the original or other copies.
Account for new records: A macro-generated split will not automatically include later additions unless you run it again or build a refresh process.
Check names and limits: Worksheet names have length and character restrictions. Long or repeated category labels need safe, unique names.
Consider scale: A workbook worksheet has a limit of 1,048,576 rows. Creating a very large number of sheets can also make a workbook cumbersome.
Protect the source structure: Filters and formulas can behave unexpectedly if the data has merged cells, inconsistent headers, or totals mixed into the records.

Power Query and other alternatives

Power Query is useful when the source is refreshed from a file, database, or other supported source and the data needs repeatable cleaning. You can load a query to a worksheet, filter it to a category, and refresh the result. However, a separate query or output setup may be needed for each category; it is not simply a universal “make one tab for every value” button.

If you only need to inspect one group at a time, use the filter arrows on the source table instead of creating many tabs. If you need separate workbooks, first create the worksheet outputs, then copy or move sheets into new files—or automate that separate step. Keep in mind that separate copies can drift apart and are harder to reconcile than one well-structured table.

Frequently Asked Questions

Can Excel automatically put each category on its own sheet?
Yes. “Show Report Filter Pages” creates sheets for the items in a PivotTable filter. A VBA macro can instead copy the matching source rows to category-specific worksheets.

Can I split a table into sheets without a macro?
Yes. Use PivotTable report pages for separate PivotTable views, or filter the source and copy each category manually. Power Query can also create refreshable filtered outputs, though setting up many outputs may take extra steps.

Will the sheets update when I add rows to the source?
Not necessarily. Copied worksheet data is generally a snapshot. A PivotTable can be refreshed to include updated source data when its source range or table is set up appropriately, while a macro must usually be run again.

Can I split the data into separate Excel files instead?
Yes, but that is a different task. Create or identify the category sheets, then move or copy them into separate workbooks. A macro can automate both splitting and file creation, but should be tested on a backup.

Why did a macro fail to create a sheet?
Common causes include duplicate sheet names, labels longer than 31 characters, forbidden characters in a label, workbook protection, or macros being disabled. Check the category values and workbook permissions, then test with a small copy of the data.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.