Split an Excel sheet into multiple sheets by column value

Splitting an Excel sheet into several sheets by column value means moving table rows so that each city, manager or month gets its own sheet. Excel has no built-in button for this. The fastest way is the !SEMTools procedure Join/Split → Split → Active sheet into several sheets by column value: select the key column, answer whether to copy headers, and the sheets are created for you. Without the add-in, a PivotTable, the FILTER function or a short macro will do.

Below are all the methods on one example: a sales log that needs to be split by city.

Split a sheet into sheets with !SEMTools

1. Open the sheet you want to split.
2. On the !SEMTools tab click Join/Split → Split → Active sheet into several sheets by column value.
3. In the range dialog, select the cells of the key column without the header, e.g. B2:B6, and click OK.
4. When asked “Do you want to include headers?”, answer Yes if the first row of the sheet is the table header.

The add-in creates one sheet for each unique value — “London”, “Paris”, “Rome” — and copies all the rows with that value onto it, with every column of the table. The source sheet stays unchanged.

What to know about the procedure:

• Values are copied, not formulas or formatting: formulas become their results, and you’ll need to set up formatting and column widths again.
• The sheet name comes from the cell value, up to 30 characters. Blank cells go to a sheet called “blank value”.
• Values with characters not allowed in sheet names (: \ / ? * [ ]) and values matching existing sheet names cause an error. Replace such characters or rename the sheets first — bulk replace helps.
• To split by dates, first add a text column with =TEXT(A2,"yyyy-mm-dd") or =TEXT(A2,"mmmm yyyy") and split by it: a date like 10/12/2026 contains slashes, which sheet names don’t allow.

Without the add-in: PivotTable “Show Report Filter Pages”

Excel has a little-known command that creates a sheet for every value of a field. It works with PivotTables:

1. Select the table and choose Insert → PivotTable.
2. Drag the field you split by (City) to the Filters area, and the columns you need to Rows and Values.
3. On the PivotTable Analyze tab, click the arrow next to Options → Show Report Filter Pages and pick the field.

Excel creates a sheet for each city with its own PivotTable. The limitation: the sheets hold PivotTables, not the source rows. To make them look like the source table, choose Report Layout → Show in Tabular Form, Repeat All Item Labels and turn off subtotals.

FILTER formula: a sheet that updates itself

In Excel 365 and 2021 you don’t have to copy the data — you can pull it with a formula. Create a sheet, type the key value in A1 and enter =FILTER(Sales!A2:D100,Sales!B2:B100=A1) in A2. The rows for that city spill below and update when the source sheet changes.

The downside: you have to create the sheets and formulas for every value by hand. Fine for 3–5 values, not for 50.

Filter and copy manually

The classic no-formula way: turn on the filter (Ctrl+Shift+L), select one value in the column, select the visible rows, copy them (Excel copies only the visible rows of a filtered range), paste them onto a new sheet and repeat for the next value. Works when there are two or three values.

VBA macro to split a sheet

If you split tables regularly and don’t have the add-in, use a macro. It creates a sheet for each value in column B and copies the header and the rows there, formatting included. The key column number is set in the KEY_COL line.

Sub SplitSheetByColumn()
    ' Moves the rows of the active sheet onto new sheets by the values in column B
    Dim src As Worksheet, ws As Worksheet, lastRow As Long, r As Long, key As String
    Const KEY_COL As Long = 2          ' the column to split by
    Set src = ActiveSheet
    lastRow = src.Cells(src.Rows.Count, KEY_COL).End(xlUp).Row
    Application.ScreenUpdating = False
    For r = 2 To lastRow
        key = Left(CStr(src.Cells(r, KEY_COL).Value), 31)
        If key = "" Then key = "Blank"
        Set ws = Nothing
        On Error Resume Next: Set ws = Worksheets(key): On Error GoTo 0
        If ws Is Nothing Then
            Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
            ws.Name = key
            src.Rows(1).Copy ws.Rows(1)       ' header row
        End If
        src.Rows(r).Copy ws.Cells(ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1, 1)
    Next r
    Application.ScreenUpdating = True
End Sub

Paste the code into a module (Alt+F11 → Insert → Module) and run it on the sheet you need. To use it in every workbook, store it in your Personal Macro Workbook.

How to save sheets as separate files

If each sheet then has to be sent as a separate file, save the workbook and run this macro — it saves every sheet as its own .xlsx workbook next to the original file:

Sub SaveSheetsAsFiles()
    ' Saves every sheet of the workbook as a separate file next to it
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & ws.Name & ".xlsx", FileFormat:=51
        ActiveWorkbook.Close SaveChanges:=False
    Next ws
    Application.ScreenUpdating = True
End Sub

Manually, use Move or Copy on the sheet tab: choose “(new book)” under “To book” and tick “Create a copy”.

Split a table into pages and a sheet into panes

Sometimes “split a sheet” means something else:

• Split a table into pages for printing: select the row where a new page should start and choose Page Layout → Breaks → Insert Page Break. View → Page Break Preview shows the page borders and lets you drag them.
• Split the window into panes to see different parts of the sheet at once: select a cell and click View → Split. The sheet is divided into two or four scrollable panes; click again to remove the split. To just keep the header visible, use View → Freeze Panes.
• Split a table into two parts by rows or columns: cut the part (Ctrl+X) and paste it onto another sheet — or use the methods above if the boundary is defined by a column value.

The reverse task

To combine the sheets back into one, use Combine all sheets into one. And if you need to split cell contents rather than a table, see how to split a cell in Excel and how to split a cell into rows.

Why the sheet won’t split: common mistakes

• An error while creating a sheet. The value contains : \ / ? * [ ] or matches an existing sheet name — fix the values or rename the sheet.
• A sheet named after the column header appeared. The header row was included in the key range — start from the second row.
• Formulas and formatting are gone. The procedure copies values; if you need formats, use the macro from this article.
• Different sheets for the same city. The values differ by spaces or case (“London” and “London ”) — remove extra spaces first.
• Too many sheets. The key has hundreds of unique values — check that you selected the right column or group the values first.

How to split an Excel sheet by column value: FAQ

How do I split an Excel table into multiple sheets based on a column value?

In !SEMTools: Join/Split → Split → Active sheet into several sheets by column value. Without the add-in — a PivotTable with Show Report Filter Pages, a FILTER formula on each sheet or a VBA macro.

Does Excel have a built-in way to split a sheet by column value?

There is no dedicated button. The closest is the PivotTable command Show Report Filter Pages, but it creates PivotTables, not copies of the rows.

How do I save Excel sheets as separate files?

Use Move or Copy → (new book) for each sheet, or a macro that saves every sheet as a separate file.

How do I split an Excel table into pages?

Page Layout → Breaks → Insert Page Break; adjust the borders in Page Break Preview.

How do I split an Excel window into panes?

View → Split: the window is divided into scrollable panes at the selected cell.

Summary: how to split an Excel sheet into multiple sheets

I hope you found what you were looking for on this page and managed to split your table into sheets. Other tools for splitting and joining data are in the Join/Split section.