Split Sheet by Column Value

What it does

Takes a single sheet and breaks it into multiple sheets, one per unique value in the column you pick. Each new sheet gets a copy of the header row plus only the rows that match its value. The reverse of Combine All Sheets Into One, for when you have one master file and need to send each region/rep/department their own slice.

Real-world example

An accountant at a regional retailer maintains a master ledger of vendor invoices: 6,000 rows, columns include Vendor | Region | Invoice # | Amount | Status. Every Monday the regional managers want to see only their region's vendors. The accountant has historically done this with five separate sheets, manually filtered, manually exported. It takes an hour and a half, and twice a year someone gets the wrong region's data because she copy-pasted to the wrong tab.

She switches to this macro. She opens the master ledger, clicks into the data sheet, runs SplitSheetByColumn, types 2 (the Region column), and 6 seconds later there are 5 new sheets: North, South, East, West, Central. Each has the proper header row at the top and only the rows for that region underneath. She right-clicks each tab, Move or Copy to a new workbook, saves five files, attaches them to five emails. The whole Monday morning ritual is done in 15 minutes instead of 90.

Realistic time saved per week: about 75 minutes. Across a 50-week year, that's roughly 60 hours. The bigger benefit is the wrong-region screw-up never happens again, because the split is deterministic. No copy-paste, no human deciding which tab is which.

How to use

First time? See How to run our tools for the 30-second setup.

1. Open the workbook with your data, alongside the macro workbook.
2. Click into the data sheet to make it active. Row 1 should be headers; data starts on row 2.
3. Press Alt + F8 / Option + F8, pick SplitSheetByColumn, click Run.
4. Type the column number to split by. Column A = 1, B = 2, C = 3, on through.
5. The macro creates one new sheet per unique value, each with the header row at the top.

FAQ

What happens to my original sheet?

Untouched. Only new sheets are added, the source stays as-is.

What if a sheet with the same name as a unique value already exists?

The existing sheet is deleted and replaced with the freshly split version. Save it under a different name first if you need to keep the old one.

What about blank values in the split column?

Rows with a blank value all go into a sheet called (blank). So if your column has a few rows where the user forgot to fill it in, those rows aren't lost, they're just gathered.

What about Excel's illegal sheet-name characters?

The macro sanitizes them. Colons, slashes, question marks, asterisks, and brackets get replaced with safe characters or stripped, and names longer than 31 characters get truncated. So if your split column has values like Q1/2026 or Stage [3], the resulting sheets get readable names that Excel will actually accept.

How fast is it on big sheets?

Fast. The macro reads all data into memory once, splits it, and writes each group in one shot. Even a 100,000-row sheet completes in a few seconds. Excel's row limit is the practical ceiling.