How to split a large Excel file into separate sheets or files

Work out which split you need

Four different needs hide behind the same search, and picking the wrong one wastes an afternoon.

By worksheet: the workbook has many tabs and you want one file per tab. Forty tabs in, forty files out, each keeping the tab name.

By row, one file per row: every line becomes its own file, usually with the header row copied on top so the columns still have names. This is the one for portals that accept a single record per upload, or a mail merge that has to produce separate attachments.

By block of rows: one big sheet cut into files of 50,000 rows each. Nobody wants 100,000 files, they want 2 files of 50,000 rows, or 20 of 5,000.

By the value in a column: all the EMEA rows in one file, all the APAC rows in another. This is a group-by, and it is what most people actually mean when they say split by customer.

The first two have browser tools. The last two are scripting jobs, with code further down.

Split a workbook into one file per sheet, in the browser

The Split Excel Sheets tool at anyfilekit.com/split-excel-sheets/ reads the workbook inside the browser tab, writes each worksheet out as its own .xlsx and hands the lot back as a ZIP. Nothing is uploaded, so the ceiling is whatever your own machine can hold.

Drop in an .xlsx, .xlsm or .xls file, or several workbooks at once: each one gets split. Leave the skip empty sheets box ticked unless you want the blank tabs too, and note that a sheet counts as empty only if every cell is blank and no cell holds a formula. Press Start. One sheet out gives a single file, more than one gives a ZIP.

Files are named after the workbook and the tab, so budget-Q1 Actuals.xlsx rather than sheet1.xlsx. Characters Windows rejects are swapped for underscores, colliding names get -2 and -3 after them, names over 80 characters are cut, and reserved names like CON or LPT1 get an underscore in front.

What survives the trip: cell values, formulas, number formats such as #,##0.00, column widths, row heights, merged cells and comments. What does not: fonts, text colour, cell fill, borders, alignment, charts, conditional formatting, data validation dropdowns and macros. An .xlsm goes in, an .xlsx comes out, and the macros stay behind.

The useful part is the warning. Before the download appears, the page counts the formulas on the sheets it just wrote that point at another sheet. Those will show #REF! when you open the file, because the sheet they refer to is not in it. Zero means a clean split. 340 means a workbook of linked tabs, and you should fix that before sending anything to anyone.

Split a sheet into one file per row, in the browser

The Split Excel Rows tool at anyfilekit.com/split-excel-rows/ does the one file per row job. It takes .xlsx, .xlsm, .xls and .csv, always writes .xlsx, and handles one workbook per run: drop several and only the first is used.

Four settings, all optional. Put the first row at the top of every file is on by default, which gives each output file the header row plus one data row. Which sheet takes a sheet name or a position number, and defaults to the first sheet. Which rows takes the row numbers Excel shows down the left edge, in the form 2-500, 900, and an open-ended 700- means row 700 to the end. Name the files after a column takes a column letter or the heading text, so an invoice column gives you INV-2041.xlsx instead of row-0037.xlsx.

Blank rows inside the range are skipped and counted. Past 200 files the tool asks first, with a size estimate. The hard limit is 2,000 files per run: for a 10,000 row sheet, put 2-2001 in the row box, then 2002-4001, and so on.

Formulas are deliberately flattened here. A cell holding =B7+C7 lands in row 2 of a one-row file, where the reference would point at an empty row 7 and quietly produce the wrong number, so the tool writes the last saved result instead. There is a catch worth knowing: a file written by a program rather than by Excel, openpyxl output for instance, often stores no cached result at all. Those cells come out blank, and the page tells you how many. Opening the file in Excel once and saving it fills the cache and fixes the run.

Merged cells are handled in two halves. A merge that sits inside a single row travels with that row. A merge spanning several rows cannot, since the rows are being separated on purpose, so it is dropped and the count is reported. Column widths come across, number formats and comments come across, and the styling list dropped is the same one as for the sheets tool.

What the browser tools will not do

They run entirely on your machine, which is the point of them and also their ceiling. A 60 MB workbook takes as long as your laptop needs, and on a phone a few thousand output files will hurt. No server is doing the heavy lifting, so the speed is down to the hardware in front of you.

There is no queue and no automation: no watch folder, no schedule, no command line, no API. If this has to happen every Monday morning, use the VBA or the Python below instead.

The Split Excel Rows tool also cuts a sheet into blocks of N rows: leave the rows-per-file box empty for one file per row, or put 500 in it for one file every 500 rows. The 2,000-file ceiling counts output files rather than rows, so a 100,000-row sheet cut into blocks of 1,000 is well inside it. What the browser tools still cannot do is run unattended on a schedule, which is where the scripts below earn their place.

Doing it by hand in Excel

For a one-off with a handful of tabs, the manual route keeps every bit of formatting. Right-click the sheet tab, choose Move or Copy, pick (new book), tick Create a copy and click OK. Excel opens a new workbook holding that sheet, and File, Save As finishes it. Ctrl-click several tabs first and they move together into one file.

One behaviour surprises people. When a copied sheet holds formulas pointing at tabs left behind, Excel does not break them, it rewrites them as external links to the original file, something like ='C:\Reports\[budget.xlsx]Summary'!B4. The numbers look right until somebody moves or renames the source workbook, and the copies carry your file path to whoever opens them. If they are going outside the team, use Data, Edit Links, Break Link, which converts them to values.

Reckon on 20 to 30 seconds per sheet. At 40 tabs that is a quarter of an hour of clicking, which is roughly the point where a macro pays for itself.

A VBA macro that saves every sheet as its own file

Open the workbook, press Alt and F11 for the editor, then Insert, Module, and paste this in. F5 runs it. The workbook has to be saved somewhere first, because the macro writes the new files next to it.

It skips hidden sheets, strips the characters Windows will not take in a filename, and names each file after the workbook and the tab. Because it uses Excel's own sheet copy, formatting, charts and conditional formatting survive, and cross-sheet formulas become external links exactly as in the manual method.

Sub SplitSheetsIntoFiles()
    Dim src As Workbook
    Dim ws As Worksheet
    Dim folder As String, base As String, nm As String
    Dim bad As Variant
    Dim i As Long, made As Long

    Set src = ThisWorkbook
    If Len(src.Path) = 0 Then
        MsgBox "Save the workbook first, then run this again."
        Exit Sub
    End If

    folder = src.Path & Application.PathSeparator
    base = src.Name
    If InStrRev(base, ".") > 0 Then base = Left(base, InStrRev(base, ".") - 1)
    bad = Array("", "/", ":", "*", "?", """", "<", ">", "|")

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For Each ws In src.Worksheets
        If ws.Visible = xlSheetVisible Then
            nm = ws.Name
            For i = LBound(bad) To UBound(bad)
                nm = Replace(nm, bad(i), "_")
            Next i
            ws.Copy
            ActiveWorkbook.SaveAs Filename:=folder & base & "-" & nm & ".xlsx", _
                                  FileFormat:=xlOpenXMLWorkbook
            ActiveWorkbook.Close SaveChanges:=False
            made = made + 1
        End If
    Next ws

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox made & " files written to " & folder
End Sub

A VBA macro that cuts a sheet into blocks of rows

This is the one the browser tools do not cover. Select the sheet to cut, then run the macro. It assumes data starting in cell A1 with a single header row, and writes one file per 1,000 rows next to the workbook. Change ROWS_PER_FILE for a different block size, and HEADER_ROWS if your headings take two rows.

It copies values rather than pasting cells, which is the safe choice: pasted formulas would shift their references and recalculate against rows that are no longer there. Formatting is not carried over, so if the blocks have to look like the original, style the output or use Power Query instead.

Sub SplitRowsIntoBlocks()
    Const ROWS_PER_FILE As Long = 1000
    Const HEADER_ROWS As Long = 1

    Dim src As Worksheet
    Dim book As Workbook, nb As Workbook
    Dim lastRow As Long, lastCol As Long
    Dim startRow As Long, endRow As Long, part As Long
    Dim folder As String, base As String

    Set src = ActiveSheet
    Set book = src.Parent
    If Len(book.Path) = 0 Then
        MsgBox "Save the workbook first, then run this again."
        Exit Sub
    End If

    folder = book.Path & Application.PathSeparator
    base = book.Name
    If InStrRev(base, ".") > 0 Then base = Left(base, InStrRev(base, ".") - 1)

    lastRow = src.UsedRange.Row + src.UsedRange.Rows.Count - 1
    lastCol = src.UsedRange.Column + src.UsedRange.Columns.Count - 1
    startRow = HEADER_ROWS + 1

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Do While startRow <= lastRow
        endRow = startRow + ROWS_PER_FILE - 1
        If endRow > lastRow Then endRow = lastRow
        part = part + 1

        Set nb = Workbooks.Add(xlWBATWorksheet)
        With nb.Worksheets(1)
            .Range(.Cells(1, 1), .Cells(HEADER_ROWS, lastCol)).Value = _
                src.Range(src.Cells(1, 1), src.Cells(HEADER_ROWS, lastCol)).Value
            .Range(.Cells(HEADER_ROWS + 1, 1), _
                   .Cells(HEADER_ROWS + endRow - startRow + 1, lastCol)).Value = _
                src.Range(src.Cells(startRow, 1), src.Cells(endRow, lastCol)).Value
        End With

        nb.SaveAs Filename:=folder & base & "-part" & Format(part, "000") & ".xlsx", _
                  FileFormat:=xlOpenXMLWorkbook
        nb.Close SaveChanges:=False
        startRow = endRow + 1
    Loop

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox part & " files written to " & folder
End Sub

Python: one file per worksheet

For anything that has to run on a schedule or on a server, pandas is 15 lines. Install it with pip install pandas openpyxl, and add xlrd if your inputs are old .xls files.

The detail worth remembering is that pd.read_excel with sheet_name=None returns a dict of sheet name to DataFrame, reading every tab in one pass. dtype=object keeps values as they were instead of coercing an ID column of digits into floats.

One thing to watch: pandas treats the first row of each sheet as the column headings, so a notes tab with a single line in it reads back as an empty frame. Pass header=None for sheets with no headings. Formulas are read as their cached result, and no formatting comes across.

import re
from pathlib import Path
import pandas as pd

src = Path("workbook.xlsx")
out = Path("sheets")
out.mkdir(exist_ok=True)

sheets = pd.read_excel(src, sheet_name=None, dtype=object)

for name, frame in sheets.items():
    if frame.empty:
        print("skipped empty sheet:", name)
        continue
    safe = re.sub(r'[\/:*?"<>|]', "_", name).strip(" .") or "sheet"
    target = out / f"{src.stem}-{safe}.xlsx"
    frame.to_excel(target, index=False, sheet_name=safe[:31])
    print(target.name, len(frame), "rows")

Python: cut a huge CSV into blocks

This is the answer to the 2.4 million row export. A worksheet holds 1,048,576 rows and 16,384 columns, so a file above that cannot be opened in Excel in one piece: Excel loads the first million and tells you the file was not loaded completely.

read_csv with chunksize returns an iterator, so the file is never fully in memory and the script runs in roughly constant RAM whatever the input size. dtype=str with keep_default_na=False preserves leading zeros in postcodes and stops the string NA becoming an empty cell. The utf-8-sig encoding writes a BOM, which is what makes Excel open the result with accented characters intact.

from pathlib import Path
import pandas as pd

src = Path("orders.csv")
rows_per_file = 50000
out = Path("chunks")
out.mkdir(exist_ok=True)

reader = pd.read_csv(src, chunksize=rows_per_file, dtype=str, keep_default_na=False)

for part, chunk in enumerate(reader, start=1):
    target = out / f"{src.stem}-part{part:03d}.csv"
    chunk.to_csv(target, index=False, encoding="utf-8-sig")
    print(target.name, len(chunk), "rows")

Python: one file per value in a column

All the EMEA rows in one file, all the APAC rows in another, one file per customer, one per cost centre. A groupby does it in four lines. The same filename cleaning applies, because a value like N/A or a customer called Smith/Jones otherwise produces a path that does not exist.

dropna=False keeps the rows whose key is blank instead of silently dropping them, which matters when you are meant to hand out every row you received. Swap to_excel for to_csv if the recipients prefer CSV.

import re
from pathlib import Path
import pandas as pd

frame = pd.read_excel("orders.xlsx", sheet_name="Q1 Actuals")
out = Path("by-region")
out.mkdir(exist_ok=True)

for value, group in frame.groupby("Region", dropna=False, sort=False):
    label = re.sub(r'[\/:*?"<>|]', "_", str(value)).strip(" .") or "blank"
    target = out / f"{label}.xlsx"
    group.to_excel(target, index=False)
    print(target.name, len(group), "rows")

Which method fits which job

One-off, formatting matters, under about 10 sheets: do it by hand in Excel. Nothing to install, nothing lost.

Every week, always the same shape of workbook, and you live in Excel anyway: VBA. It stays with the file, colleagues can run it without installing anything, and it keeps the formatting.

Part of a pipeline, or a file too big for Excel to open: Python. It runs headless, handles a 5 GB CSV without opening it, and is the only option here that can be scheduled.

The file cannot leave the machine: the browser tools. Contracts, payroll, patient data, anything under an NDA. The work happens in the tab and there is no account to create. Same answer on a locked-down laptop where macros are disabled by policy and Python is not installable.

Formatting is the one thing the browser route costs you. If the recipients care what the files look like, split inside Excel.

Where splits go wrong

Cross-sheet formulas. This is the big one. A workbook where the summary tab sums the twelve month tabs does not survive being split, in any tool. Excel's own copy turns those into external links that work until the original moves. Every other method leaves #REF! or a flattened value. If the split files are meant to stand alone, convert the formulas to values first with Copy, Paste Special, Values.

Merged cells. A merge spanning rows cannot follow those rows into separate files, though merges across columns inside one row are fine. A two-line merged title above the data will arrive damaged or not at all.

Pivot tables. A pivot carries a cached copy of its source data, so an Excel copy of the sheet still shows the numbers until the source range goes missing. Tools that rebuild the file cell by cell, the browser tools and pandas included, leave a static picture of what the pivot showed.

Defined names and data validation. Named ranges pointing at other sheets break the same way formulas do, and dropdown lists whose source lives on a hidden lookup tab come out empty or gone.

The row ceiling. 1,048,576 rows per sheet is a limit of the file format, not a setting. If your CSV is bigger, split it with the Python above, or load it with Power Query, which reads past a million rows into the data model even though a worksheet cannot show them.

Filenames from data. Naming files after a column works right up to the row where the value is blank, duplicated, 200 characters long or holds a slash. Handle all four, or you will overwrite files silently and wonder why 300 rows became 287 files.

Before you hand the files out

Open two or three of the output files, not just the first. The first is usually fine, and the interesting failures sit in the middle where a blank row or an odd value shifted something.

Then count them and search for #REF! and for blank cells where numbers belong. Forty tabs should give forty files unless empty sheets were skipped, and both symptoms are cheaper to find now than after the files are sent.