How to Split Excel File into Multiple Sheets Using Python (xlwings)

If you have a workbook with multiple sheets and want to split each sheet into its own Excel file—preserving formatting, formulas, and charts—then this guide is for you. We’ll walk through the process step by step using Python and

Step 1: Install Python

First, ensure Python is installed on your system.

For Windows/macOS/Linux:

Download Python from the official website:

https://www.python.org/downloads/

Then verify the installation:

c:\user\username> python --version

Python 3.11.9

Step 2: Install Required Dependencies

We will use the xlwings library to manipulate Excel files while maintaining full formatting.

Open your terminal or command prompt and run:

c:\user\username> pip install xlwings

Tip: You can also create a virtual environment for better project management.

python -m venv env

source env/bin/activate # On Windows: env\Scripts\activate

pip install xlwings

Step 3: Prepare Your Excel File

Here’s a full script to export each worksheet as a separate Excel file:

import xlwings as xw

import os

# Open the original Excel file

wb = xw.Book('example.xlsx')

# Output folder

output_folder = "output_sheets"

os.makedirs(output_folder, exist_ok=True)

# Loop through sheets

for sheet in wb.sheets:

# Create new workbook

new_wb = xw.Book()

new_sheet = sheet.copy(after=new_wb.sheets[0])

# Delete default sheet

new_wb.sheets[0].delete()

# Save the new workbook

new_filename = os.path.join(output_folder, f"{sheet.name}.xlsx")

new_wb.save(new_filename)

new_wb.close()

wb.close()

print("Done: Sheets saved with full formatting and links.")

Step 5: Run the Script

Save the code above as split_sheets.py then run:

c:\user\username> python split_sheets.py

You’ll find each worksheet saved as a new Excel file in a folder named output_sheed

Results

Each sheet from example.xlsx is now exported as a separate .xlsx file with:

Original formatting
Embedded formulas
Charts and objects preserved

Conclusion

With just a few lines of Python, you can automate a tedious manual task and preserve all your worksheet details. This is especially useful for financial reports, client-specific data exports, or large multi-department Excel files.