Split an Excel sheet by column value
One file per unique value in a chosen column — per-client, per-region, per-employee — from a master workbook.
Free. Unlimited. No signup.
Runs entirely in your browser · No upload · No signup · Open the network tab and check
“Obviously, I know how to do this manually, by moving or copying the sheet. But sometimes I have to combine more than 20 files into one and don't want to be moving 20 or more sheets, one by one.”
— Microsoft Q&A
Copying sheets one by one is the same chore in both directions. This page does the outbound half — one master workbook into per-group files — in a single run.
How it works
- 1
Drop your workbook
A .xlsx file — the master sheet with everything in it. It is parsed right here in the browser; nothing uploads.
- 2
Pick the column to split by
The tool reads your header row and lists the columns. Choose Client, Region, Department — whichever column defines the groups.
- 3
Split and download
One .xlsx per unique value, each keeping the header row. More than three files arrive zipped, with every file also available individually.
A worked example: one master workbook, one file per client
Your master file is clients.xlsx: 240 rows, with a Client column holding 18 unique names. Split by Client and one run produces 18 files — clients-Acme Interiors.xlsx, clients-Bergman & Co.xlsx, and so on — each holding only that client's rows under the original header row, plus a zip holding all 18.
Every file is named from the source filename plus the group value, so the download folder sorts itself. Send each client their own file — and nothing but their own file.
Constraints & edge cases
My workbook has several sheets — which one is split?
+
The first worksheet. This version does not offer a sheet picker, so if the data lives on another tab, move it to the front (or copy it into its own file) before dropping the workbook. Everything else on the other tabs is left untouched — the tool never modifies your original file.
Does every output file keep the header row?
+
Yes. The header row is written at the top of every output file, so each per-client file opens as a self-contained, filterable sheet — not a headless fragment.
What happens to rows where the split column is blank?
+
They are not dropped. Blank-valued rows are collected into their own output file named after the source with a -blank suffix, so you can see exactly which rows were missing a value instead of silently losing them.
Are formulas and formatting preserved?
+
Values are preserved — including numbers and dates as real types, not text. Formulas are replaced by their last calculated result, and cell formatting (colors, borders, conditional formats) is not carried over. If a split file must keep live formulas that reference other rows, a values-only split is the honest answer: those references would break anyway.
My file is .xls — will it work?
+
Legacy .xls is not readable in this version. Open it in Excel, save as .xlsx, and drop it again — the tool tells you exactly this if you try.
How big can the file be?
+
Intake caps at 100 MB with a 50 MB per-file processing guard, because the whole workbook is parsed in browser memory. Files beyond that should be broken up first — or exported as CSV and split with the CSV variant, which handles text more compactly.
How to split an Excel sheet by column value, by hand
Excel has three ways to do this and none of them is a 'split' command, which is why the search results are full of forum threads. Here is each one, what it actually produces, and the point at which it stops being worth it.
Filter, copy, pasteAbout two minutes per group — so twenty minutes for ten, a morning for sixty
- 1Click any cell in your data and press Ctrl+Shift+L (Cmd+Shift+F on Mac) to switch on filters.
- 2Open the dropdown on the column you want to split by and tick a single value.
- 3Select the visible rows, copy, and paste into a new workbook. Excel copies only visible rows, so the filtered-out ones are left behind correctly.
- 4Save that workbook under the group's name.
- 5Go back, change the filter to the next value, and repeat.
Where it breaks. It is purely linear: sixty groups means sixty repetitions of the same five steps, and the only thing standing between you and a mispaste is your own attention at 4pm. It also has to be redone from scratch every time the source data changes.
PivotTable → Show Report Filter PagesFive minutes total, regardless of how many groups
- 1Select your data and choose Insert → PivotTable.
- 2Drag the column you want to split by into the Filters box, and drag the fields you want to keep into Rows.
- 3Click any cell in the PivotTable, then on the PivotTable Analyze tab open the Options dropdown and choose Show Report Filter Pages.
- 4Confirm the field. Excel creates one worksheet per distinct value, in one workbook.
Where it breaks. It produces worksheets, not files — you still have to move each tab into its own workbook by hand if files are what you owe someone. The output is also a PivotTable rather than your original rows, so formatting, column order and any text that Excel decides to aggregate all change on the way through.
Power QueryTwenty to forty minutes to build, then it re-runs on new data
- 1Select your data and choose Data → From Table/Range to load it into Power Query.
- 2Right-click the split column and choose Group By, then set the aggregation to All Rows. You now have one row per group with a nested table in each.
- 3For each group you want out, click the table cell to drill in, then Close & Load To… a new sheet.
Where it breaks. Power Query is genuinely the right tool for repeatable transformation, but it loads results into sheets — it has no ability to write a separate file per group. Getting files still means either exporting each result by hand or dropping into VBA, which is where most of the forum threads on this query end up stuck.
A VBA macroAn hour the first time if you are comfortable with VBA, minutes to re-run
- 1Press Alt+F11 to open the Visual Basic editor, then Insert → Module.
- 2Write a loop that reads the distinct values in your split column, filters the sheet to each one, copies the visible rows to a new workbook, and calls SaveAs with the group name as the filename.
- 3Run it, and grant the file macro permission — which means saving your workbook as .xlsm.
Where it breaks. It works, and it is the answer most forum threads eventually arrive at. The costs are real though: the workbook becomes macro-enabled, which many corporate mail gateways strip or quarantine outright; filenames need sanitising for characters Windows rejects (a group called “Acme / West” will fail SaveAs); and you now own a piece of code that only you can maintain.
Windows and Mac
Windows. Power Query is on the Data tab and has been fully featured for years.
Mac. Power Query on Excel for Mac arrived late and is still narrower than the Windows build — several connectors and the full editor UI are missing depending on your version. The VBA editor also differs enough that macros copied from Windows tutorials frequently need adjusting before they run.
For a handful of groups, once, the filter-and-copy route is genuinely fine and you do not need a tool. The moment it is dozens of groups, or it is monthly, every manual route above either multiplies linearly or hands you a macro to maintain.
Split it here instead — free, no signup, and your file never leaves your machine.