How to Split Large Excel Files: 5 Methods (Free Tools + VBA Code)

Managing a 50-sheet Excel workbook or a file with 2 million rows is a nightmare. Excel crashes. Emails bounce back ("File too large"). Collaborators complain about slow loading times.

This guide shows you 5 proven methods to split Excel files - from beginner-friendly tools to advanced VBA scripts - used by data analysts at Google,Amazon, and Fortune 500 companies.

Why Split Excel Files?

Before diving into methods, understand when splitting is necessary:

Method 1: Client-Side Web Tool (Fastest & Safest)

For 95% of users, this is the go-to method. No coding, no uploads, and supports files up to 500MB.

Step-by-Step: Splitting by Sheets

Use Case: You have a workbook with 20 regional sales reports (one sheet per region) and need to send each region their own file.

1. Open iloveexcel.co/split-excel
2. Upload your master file (e.g., "Sales_2025_All_Regions.xlsx")
3. Select mode: "Split by Sheets"
4. Click Split - You get a ZIP file containing 20 separate .xlsx files (one per sheet)

Step-by-Step: Splitting by Row Count

Use Case: You have a customer database with 2 million rows but need files under 100k rows for easier analysis.

1. Upload your large file
2. Select mode: "Split by Rows"
3. Enter: 100000 (rows per file)
4. Result: 20 files named "Part_1.xlsx", "Part_2.xlsx", etc.

Method 2: Excel Built-In (Move or Copy)

If you only need to split one or two sheets manually:

1. Right-click the sheet tab → "Move or Copy"
2. Select "(new book)" from the dropdown
3. Check "Create a copy"
4. Click OK → Save the new file
5. Repeat for each sheet

Limitation: Tedious for 10+ sheets. No way to split by rows.

Method 3: VBA Macro (Automate Repetitive Splits)

For recurring splits (e.g., monthly reports), use this VBA script:

Sub SplitWorkbookBySheets()
    Dim ws As Worksheet
    Dim newBook As Workbook
    
    For Each ws In ThisWorkbook.Worksheets
        ' Create new workbook for each sheet
        Set newBook = Workbooks.Add
        ws.Copy Before:=newBook.Sheets(1)
        
        ' Save with sheet name
        newBook.SaveAs ThisWorkbook.Path & "\" & ws.Name & ".xlsx"
        newBook.Close SaveChanges:=False
    Next ws
    
    MsgBox "Split complete!"
End Sub

How to Use:

1. Press Alt + F11 to open VBA editor
2. Insert → Module
3. Paste the code above
4. Press F5 to run

Method 4: Python (openpyxl) for Programmatic Splitting

For data scientists working with pandas + Jupyter:

import pandas as pd

# Read large Excel file
df = pd.read_excel('large_file.xlsx')

# Split into chunks of 50k rows
chunk_size = 50000
for i in range(0, len(df), chunk_size):
    chunk = df[i:i+chunk_size]
    chunk.to_excel(f'output_part_{i//chunk_size + 1}.xlsx', index=False)

Advantage: Can combine splits with data cleaning (remove duplicates, fix dates, etc.)

Method 5: Power Query (For Filtered Splits)

If you need to split based on criteria (e.g., "Create separate files for each Country in the Country column"):

1. Data → From Table/Range
2. In Power Query: Group by "Country"
3. Right-click grouped table → "Extract Values"
4. Load each group as a new sheet or export separately

Comparison Table: Which Method is Best?

Troubleshooting Common Issues