Split Excel

A typical case is a monthly report with one sheet per branch, region or salesperson, where each person should receive only their own sheet. Choose One file per sheet, tick the sheets, and you get files such as report-Seoul.xlsx, report-Busan.xlsx and so on, each holding exactly one sheet with its original name.

Sheets with no data start unticked, and hidden sheets are marked with a crossed-out eye so you do not send out a lookup table by accident. Sheet names that are not allowed in file names (with / or : in them, for example) are cleaned up, and two sheets that would end up with the same file name get (2) added.

Formulas. By default each formula is replaced by the value Excel last calculated, which is what most people want when sending a sheet to someone else. Switch on Keep formulas if the files will be edited further. Formulas that only use cells on their own sheet keep working; formulas that point to another sheet, such as =SUM('Orders'!C3:C500), break, because that sheet is no longer in the file, and Excel usually shows #REF! once it recalculates.

Choose By rows in one sheet when a single list is too long for an import screen, a mail merge or a colleague's patience. The tool picks the longest sheet for you; change it if needed.

Header rows to repeat is the number of rows at the top that are copied into every part. One row suits a plain table with column names. If your sheet starts with a report title above the column names, choose 2 so both the title and the column names appear in each file. A title merged across columns stays merged.

Example: a sheet with a title row, a column-name row and 50,000 orders, split with 2 header rows and 15,000 rows per file, gives four files. The first three hold 15,000 orders each, the last one 5,000, and all four start with the same two header rows. This is the exact case we used to test the tool, opening every part afterwards to check the headers, the row counts and the values.

In this mode formulas are always converted to values, because moving rows to a new position would make relative references point at the wrong cells.

Kept:

• All cell values: text, numbers, dates, true/false.
• Number formats such as #,##0.00, percentages and date formats, so 1234.5 still shows as 1,234.50.
• Text that looks like a number, such as postcode 01001 or an account number, stays text with its leading zero.
• Column widths and merged cells (in the header rows and within each part).

Not kept:

• Cell colours, fonts, bold, borders and conditional formatting. The files are clean, unstyled sheets.
• Charts, images, shapes, pivot tables and data validation lists.
• Macros. An .xlsm file comes out as macro-free .xlsx files.

Input in the old .xls format, LibreOffice .ods or binary .xlsb is accepted, and the output is always .xlsx, which every current version of Excel, Google Sheets, Numbers and LibreOffice opens.

The whole workbook is read into memory, which takes much more room than the file size suggests. On a computer, workbooks of tens of megabytes are fine. On a phone, a very large workbook may fail to open; try a computer in that case.

Password-protected workbooks cannot be read. Remove the password in Excel (File, Info, Protect Workbook), save, and try again.

If a part looks shorter than expected, check whether the sheet has blank rows at the bottom that Excel still counts as used: the tool splits the range Excel reports, so blank rows at the end are included in the last part. If a sheet is very long and only needs to become CSV pieces, Excel to CSV followed by Split CSV is faster and lighter.

To print or share a split sheet as a document, use Excel to PDF. To split a CSV file of any size, use Split CSV. To bundle files of your own into a ZIP, use Create ZIP.