Guides
How to Split an Excel Sheet into Multiple Files by Column
Updated 2026-10-18
The master sheet holds the whole company's data and each department should get only its own rows. Or you split by region, supplier or customer. Doing one or two by hand is easy; doing forty, every month, is not.
Method 1: filter, copy, paste
- Select the header and turn on Data → Filter.
- In the column you split by, tick a single value.
- Copy the visible rows with the header, paste them into a new workbook, save.
- Repeat for the next value.
Fine for a handful of values. With many it takes a long time, it's easy to miss a value or the blank group, and column widths and frozen panes have to be set again in every file.
Method 2: PivotTable Show Report Filter Pages
A lesser-known trick that creates one sheet per value:
- Insert a PivotTable from the master sheet, put the split column in Filters and other fields in Rows and Values.
- PivotTable Analyze → PivotTable → Options drop-down arrow → Show Report Filter Pages.
- Confirm, and every value gets its own sheet named after it.
Limits: each sheet holds a PivotTable, not the original rows and formatting, and everything stays in one workbook; separate files still mean saving each sheet by hand.
Method 3: a VBA loop
If you write macros, loop over the distinct values, filter, copy to a new workbook and save. Flexible, but you have to write and debug code, and keep it in an .xlsm or a separate macro file.
Method 4: split online in one step
With xlszip's split tool:
- Upload the workbook.
- Under Split by column, enter the header text (e.g. Department) or the column letter (e.g. B).
- Press Split.
What you get:
- one xlsx file per distinct value, downloaded as a zip; tick the option to get one tab per value in a single file instead;
- the header and any title rows above it in every part, with fonts, fills, borders, number formats, column widths and frozen panes copied;
- rows keep their original order; rows where the split column is blank go into a part called
(空白 blank), so nothing is lost;
- the sheet shown when the file opens is the one that gets split; a header name is looked up in the first 10 rows of every sheet.
Note: formulas become their last calculated values, because rows move to new positions and the original references would point at the wrong cells.
Before sending the parts out
Each file may still carry the author, company and local file path. If you split into tabs, take the result straight to Clean for sharing; if you split into files, unzip them and clean them all at once there.
Need the reverse, combining the sheets that come back? See How to merge multiple Excel files into one.