How to split a sheet manually in Excel
Excel has no single command that turns one sheet into several files. The usual routes all work, but they are slow and repetitive once you have more than a handful of values.
Filter, copy and save, once per value
- Click inside your data and choose Data › Filter.
- Open the drop-down on the column you want to split by, for example Region, and tick only one value, such as North.
- Select the visible data including the header row, then press Alt+; to select visible cells only. On the Home tab, Find & Select › Go To Special › Visible cells only does the same.
- Copy, create a new workbook with Ctrl+N, paste, and save it with a name like North.xlsx.
- Go back, change the filter to the next value, and repeat.
With four regions that is twenty-odd steps. With forty salespeople it is an afternoon, and it is easy to skip a value, save over the wrong file, or forget the rows where the region cell is empty. Also check spelling first: a filter lists “Pune” and “PUNE “ separately, so one branch can end up in two files.
Show Report Filter Pages
PivotTables have a hidden shortcut. Build a PivotTable, drag the split column (for example Region) into the Filters area, then go to PivotTable Analyze › Options (click the small arrow beside Options) › Show Report Filter Pages and choose the field. Excel adds one sheet per value in seconds.
The catch is that every new sheet contains a PivotTable, not your original rows. You get totals and counts, which is fine for a summary, but not the raw list you would send to a branch or import into another system. The sheets also stay inside one workbook rather than becoming separate files.
Power Query and VBA
In Power Query you can duplicate a query for each value and filter each copy, which works but must be repeated for every new value. A VBA macro can loop through the unique values, filter, and save a workbook for each one. That is the most automated option in Excel, but it needs a macro-enabled file, some coding, and permission to run macros, which many workplaces block.
When this tool helps
Sending each person only their rows. A sales manager has one sheet of all orders and wants to send each salesperson their own. Splitting by the Salesperson column gives one file per person, ready to attach, and nobody sees another person’s customers.
Import limits. Many CRMs, email platforms and accounting tools cap how many rows one upload may contain, for example 1,000 per file. Splitting every 1,000 rows gives files that each fit the limit, each with the header row the importer expects.
Per-region reports. Head office keeps a single master list, but regional teams only need their part. One workbook with a sheet per region is a tidy way to share it, while separate files suit people who should only see their own region.
Large CSV files. Some systems reject files above a certain size, and some older tools struggle to open very long CSV files. Cutting a big CSV into fixed-size parts produces smaller CSV files that open and upload without trouble.
What the tool does differently
- Every part keeps the header row. You never have to paste headings back in.
- Near-identical values go together. Capitals and stray spaces are ignored when grouping, so a branch is not split across two files because of inconsistent typing.
- No row is lost. Rows with an empty cell in the chosen column go into a “(blank)” part rather than being skipped.
- Raw rows, not summaries. Unlike Show Report Filter Pages, each part contains your original rows and columns.
- Clear names. Files are named after their value, and row-count parts are named by spreadsheet row numbers, so “Rows 2–1001” tells you exactly where that part came from in the original sheet.
- CSV in, CSV out. If you split a CSV file, the parts are CSV files too; other formats produce .xlsx files.
Before you split
Check the column you plan to split by. If the same value is written several ways, such as “North” and “Nth”, tidy it with Find and replace first, or the parts will not match what you expect. The summary confirms the result; for the sample file it reads: “Split 12 rows into 4 files, one for each value in Region. Every file keeps the header row.” If the number of parts surprises you, preview the list before downloading.
Because everything happens in your browser, this is a safe way to handle sensitive files such as payroll or commission sheets: each person’s rows are separated on your own computer, and nothing is uploaded to a server along the way.