How to Split a Sheet Into Separate Sheets by Column in Excel

The "one tab per category" problem

You have a flat data block. Customer in column A, product in column B, region in column C, amount in column D. You want one sheet per region, each containing only that region's rows. Excel has filters and pivots, but neither directly produces "one sheet per filter value."

TL;DR — Key takeaways

• Manual filter and copy works one category at a time and is tedious for many categories.
• Pivot table with Show Filter Pages can split into sheets but uses pivot format, not raw data.
• A VBA macro scans a category column, creates one sheet per unique value, and copies matching rows to each.

Method 1: Manual filter and copy

1. Apply a filter on the category column.
2. Set the filter to one value.
3. Copy visible rows.
4. Insert new sheet, paste.
5. Repeat for each category.

For 12 categories, expect about 8 minutes of clicking with several "did I copy headers" mistakes.

Method 2: Pivot Table with Show Filter Pages

If your data is in a pivot table:

1. Drag the category field to the Filter area (top of the field list).
2. Click the pivot.
3. PivotTable Analyze > Options > Show Report Filter Pages.

Excel creates one sheet per filter value, each with a copy of the pivot filtered to that value.

The catch: result is pivot format, not raw data. To get raw rows, you'd then convert the pivots back to flat data.

Method 3: The free VBA macro

Download Split Sheet by Column. Free .xlsm with one macro.

1. Open the workbook with your flat data block.
2. Alt + F8, pick the macro, click Run.
3. The macro asks which column to split by. Pick the category column.
4. Macro creates one new sheet per unique value, copies headers + matching rows to each.

A common scenario: monthly sales pack by region

Sales operations needs a workbook where each region's sales team gets their own tab. Source data is flat: 12,000 rows, region in column C.

1. Open the source data.
2. Run Split Sheet by Column.
3. Specify column C as the split key.
4. The macro creates 18 region tabs (one per unique region) with the matching rows.
5. Send to each region's team.

Versus manual filter-and-copy: 30 seconds versus 12 minutes.

What about reverse direction (combining multiple sheets back into one)?

Use Combine All Sheets Into One. The two macros are companions:

• Split for one-to-many distribution (one source, many tabs by category).
• Combine for many-to-one consolidation (many tabs, one consolidated dataset).

Frequently asked questions

What happens if a category value contains characters illegal in sheet names?

Sheet names can't contain / \ : * ? [ ]. The macro sanitizes these to underscores. So a category value North/East becomes a sheet named North_East.

What if a category value would create a sheet name longer than 31 characters?

Excel sheet names max at 31 characters. The macro truncates. If multiple long category names truncate to the same 31-character prefix, the macro appends a suffix to disambiguate.

Does it preserve formulas in the source rows?

Formulas with relative references will reference different cells when copied to a new sheet. Convert formulas to values first with Convert Formulas to Values if you want consistent values across split sheets.

What about merged cells?

Merged cells in the source can confuse the row copy. Run Unmerge Cells and Fill Down first.

Can I undo it?

The macro adds new sheets. To remove them, right-click each new tab and Delete. Or undo with Ctrl+Z (which usually catches sheet creation as a single step).

What to do next

After splitting, generate a navigation index with List All Sheet Names. For the reverse operation, Combine All Sheets Into One.