How to split an Excel sheet into multiple files by column value

One sheet, one column with a person, branch or customer, and each of them should get a file with only their own rows. I found no built-in command for that. Here are the built-in routes, where they stop, and one option.

Hullspark · updated 2026-10-29

Short answer

Few values: filter the column to one value, copy the visible rows to a new workbook, save, repeat.
PivotTable: PivotTable Analyze > Options > Show Report Filter Pages makes separate worksheets for the items of a report filter [1]. They are worksheets in one workbook, not separate files.
Many values: community answers use a macro that loops over the values in a column and saves one workbook per value. That means writing and maintaining code.

What Excel gives you out of the box

Excel's built-in ways to split a sheet by a column
Way	Good for	Catch
Filter, copy, paste into a new workbook	A handful of values	One round per value. A wrong paste puts someone else's rows in a file
PivotTable > Show Report Filter Pages [1]	One sheet per item of a report filter	Worksheets in one workbook, not files. The sheets hold PivotTable reports, not your original rows
A macro that saves one workbook per value	Many values, repeated work	You write, test and keep the code. Macros need a macro-enabled workbook

Doing it by hand

Copy the workbook first. You will delete rows from copies.
Make sure the column you split by has the same spelling for the same person. Extra spaces or different capitals make two values.
Select the table and turn on the filter (Data > Filter).
Filter the column to one value. Select the visible rows with their header and copy them.
Paste into a new workbook, keep the column widths, and save with the value as the file name.
Clear the filter, pick the next value and repeat. Leave out any rows whose cell is empty or give them their own file.
Open one finished file and check that no other person's rows, hidden sheets or totals made it in.

Where the built-in way stops

One round per value. 40 branches means 40 rounds of copy, paste, save.
Easy to get wrong. One wrong paste shows someone the wrong figures.
Formatting and totals. Pasting can lose column widths or carry a total that adds up everyone's rows.
Pivot pages are not files. They are worksheets in the same workbook [1].

One option: our own app

I make a Windows app for this, Spreadsheet Splitter by Hullspark. What it does, as the current code and the product page describe it:

One file per value. In step 2 pick By column value and the column. The files that will be made are listed with their row counts before anything is written.
Header rows stay on top of every file, up to the row you set. A row with an empty value goes into a (blank) file instead of being dropped.
Formatting stays. In .xlsx output colours, borders, column widths, frozen rows, filters and most conditional formatting are kept.
Other ways to cut. By sheet makes one file per sheet. Every N rows cuts files for an import limit. CSV can be written in UTF-8 with BOM or in Shift-JIS.
A check before you hand the files out. The result screen lists values calculated from other rows, such as running totals, which stay as values and may still hold everyone's total. Open one file and look.

It reads .xlsx only. A workbook with, for example, a table, pivot table, chart, picture, shape, comment or array formula is refused with the reason on screen and nothing is written. Examples are a table, pivot table, chart, picture, shape, comment or array formula, and newer-style conditional formats. CSV output still works for those. Macro files (.xlsm), .xls, .xlsb and password-protected files are not read. Keys that differ only by spaces, full or half width or case become separate files. An existing file is never overwritten. Windows only. Measured on the author's PC with made-up data: 100,000 rows split by person into 6 files in about 6 to 8 seconds.

Get Spreadsheet Splitter on the Microsoft Store Product page

Questions

Can Excel save one file per value in a column by itself?

I did not find a built-in command that does. Show Report Filter Pages makes worksheets, not files [1]. The usual answer in community threads is a macro.

Does the split keep my formatting?

In .xlsx output, yes: colours, borders, column widths, frozen rows, filters and most conditional formatting are kept. In CSV output only the values are written.

Will other people's rows end up in a file?

Rows from the split sheet, no: each file holds only its own. If you turn on the option to put the other sheets into every file, those sheets go in whole, so check them. Values calculated from other rows, such as running totals, stay as values and may hold everyone's total. The app lists the ones it finds, so open one file before you send it.

Do I need Excel?

No. The app reads and writes .xlsx itself.

Does it change my original?

No. The original is only read, and nothing goes over the network.

What I have not verified

I checked the PivotTable claim against Microsoft's article on filtering PivotTable data on 2026-10-29. I did not re-test the steps on every version of Excel.

The statement about macros comes from reading community threads, not from testing a macro. The app description comes from its current code and product page. Files saved by Excel in real work were not tested, only files the app wrote from made-up data. I have not tested other vendors' tools and name none.

Sources

[1] Microsoft Support: Filter data in a PivotTable, section Display report filter pages on separate worksheets (fetched 2026-10-29): https://support.microsoft.com/en-us/excel/get-started/filter-data-in-a-pivottable
[2] Microsoft Store listing of the app: https://apps.microsoft.com/detail/9P8XQ4M9ZS6J

Checked 2026-10-29. Menu names follow Microsoft's support articles for Microsoft 365 and were not checked on every version. Microsoft, Windows and Excel are trademarks of Microsoft Corporation. This page and the app are not affiliated with, made by or endorsed by Microsoft.

Questions? Support or support@hullspark.com. Privacy policy