How to use the split excel by column value
- Choose an Excel file (XLSX), up to 25 MB, and pick the sheet.
- Type the column to split by, either a heading from the first row or a letter such as B.
- Choose to save each part as Excel or CSV, and set whether the first row is a heading and whether A and a count as the same value.
- Check the table of parts with their row counts and file names, then download all the parts as a ZIP file.
Formula
Rows are grouped by the exact text in the chosen column, in the order each value first appears
The heading row is copied into every part
Rows with an empty value go to a part named blank
File names are cleaned of characters that are not allowed, and repeats get a number such as (2)
The parts always add up to the original: the number of data rows in all the parts equals the number of data rows in the sheet.
A sheet of orders split by region
A sheet has the headings Order, Region, Rep, Amount and Date, and 12 orders. Splitting by Region gives East with 4 rows, West with 4 rows, North with 3 rows and a blank part with 1 row whose region is empty, which is 12 rows in all. The files are named East, West, North and (blank).
Each part opens as a workbook with the same headings and only its own rows, and each part holds a single region. Dates and numbers stay as dates and numbers. Choosing CSV gives the same parts as text files. Splitting by the letter C instead groups the rows by Rep.
Splitting for sharing
Splitting is useful when each person, team or region should see only its own rows, such as a sales list for each rep, a class list for each teacher or an order list for each supplier. Choose a column with a manageable number of different values, since each value becomes its own file, and the page allows up to 200 parts.
To join CSV files or split one by size, use the CSV merge and split tool. To convert a sheet to CSV, use the Excel to CSV converter. To compare two versions of a sheet, see the compare two Excel sheets tool.
What carries over
The tool copies cell values, not the look of the workbook. A formula is replaced by the value it last calculated, and formatting, column widths set by hand, charts, filters and other sheets are not carried over. The new workbooks have a bold heading row, a frozen heading and widths set to fit. Hidden sheets are marked in the sheet list.
A workbook with a password, an old XLS file or a binary XLSB file is not accepted, and the page tells you what to do instead.
Assumptions and limits
- The file is an XLSX workbook. Older XLS, binary XLSB and password protected files are not read.
- One sheet is split at a time, using the values the cells show.
- The first row is the heading, unless you turn that off.
- There can be at most 200 different values in the column.
Frequently asked questions
How do I split an Excel sheet into separate files?
Choose the file, type the column to split by and download the ZIP. You get one file for each value in that column.
Does each file keep the headings?
Yes. The heading row is copied into every part.
What happens to empty cells in the column?
Those rows go together into a part named blank.
Do formulas and formatting carry over?
No. Values are copied, with formulas replaced by their last calculated results, and formatting is not kept.