How to split an Excel file into multiple files
- Open the file. For a workbook with several sheets, pick the sheet to split.
- Choose how to split it: by a column (one file for each value, such as each State or each sales rep), by number of rows, or each sheet as its own file.
- Check the list of files on the right: their names and how many rows each one gets.
- Choose Separate files (downloaded together as one ZIP) or Tabs in one workbook, then download.
Splitting by a column
Every different value in the column gets its own file, named after the file and the value, such as customers TX.xlsx. Rows keep their original order inside each file. Columns that look like groups, such as State, Region, Department or Status, are suggested first, and each column in the list shows how many files it would make.
With Ignore capital letters and extra spaces on, “TX”, “tx” and “TX ” go in the same file. Rows with nothing in the column are collected in a file called (blank), so no row is lost.
Splitting by number of rows
Useful when a system only accepts a certain number of rows per upload, or when a file is too big to email or open comfortably. Set the rows in each file, or set how many files you want and the rows are shared out evenly. Parts are numbered so they sort in order: orders part 01 of 12.csv.
The header row goes at the top of every part by default, which is what most import screens expect.
A worked example
A customer list that each regional manager should only see their own part of:
customers.xlsx
| Row | A | B | C |
|---|---|---|---|
| 1 | Name | City | State |
| 2 | Dana Whitfield | Columbus | OH |
| 3 | Marcus Bell | Austin | TX |
| 4 | Priya Raman | Tampa | FL |
| 5 | Ellen Ortiz | Dayton | OH |
| 6 | Andre Wallace | Houston | TX |
The download is one ZIP with three files:
customers FL.xlsx, 1 rowcustomers OH.xlsx, 2 rowscustomers TX.xlsx, 2 rows
The sample list in the tool has 24 customers in 6 states; try it to see the file list update as you change the column.
Before you split
- Check the number of values. The column list shows how many files each column would make. If State says 9 values and you have customers in 6 states, something is spelled two ways, such as “Texas” and “TX”. Fix those in the file first; the tool won't guess that they're the same.
- Remove repeats first. If the list has duplicate rows, they'll be split along with everything else. Remove duplicates cleans them out in one step.
- No sorting needed. Rows don't have to be grouped together in the file; each file collects its rows from wherever they are, in their original order.
- Odd characters in values. A value like
East/Westcan't be part of a file name, so it becomesEast Westin the name. The rows inside are unchanged.
Separate files or tabs?
Separate files suit sending: each manager, branch or client gets only their file. They come in one ZIP; on Windows and Mac, double-click it to open the folder.
Tabs in one workbook suit keeping: one file to save or print, with a tab per group. Tab names are shortened to Excel's 31-character limit when needed.
Splitting in Excel itself
For a few groups, Excel's filter works: turn on Data > Filter, pick one value, copy the visible rows into a new workbook, and repeat for each value. It's slow and easy to miss a value once there are more than a handful.
A PivotTable can create one sheet per value with Show Report Filter Pages, but each sheet is a pivot summary, not the original rows. Splitting the rows themselves inside Excel usually means a VBA macro.
Going the other way, combining many files into one, is what Merge Excel and CSV files does.