How to Split an Excel Sheet into Separate Files Using Python

Introduction

Hello, I am a Python enthusiast.

Background

While helping a follower, I encountered a simple requirement that is worth sharing: split an Excel file containing 20 rows into separate files, each containing three rows.

Requirement Clarification

The manual approach would involve opening the Excel file, copying three rows at a time, pasting into a new file, saving, and renaming. This works for a few files but becomes impractical when dealing with hundreds or thousands of files.

Therefore, we use Python to automate the process.

Initial Code Example

import pandas as pd

df = pd.read_excel('res.xlsx')
df.set_index(["A"]).reset_index()
df.iloc[: 3].to_excel('1.xlsx')
df.iloc[3: 6].to_excel('2.xlsx')
df.iloc[6: 9].to_excel('3.xlsx')
df.iloc[10].to_excel('4.xlsx')

Implementation

The following concise script uses a for loop with a step size of three to generate the split files automatically.

import pandas as pd

df = pd.read_excel('res.xlsx')
# df.set_index(["A"]).reset_index()
for i in range(len(df) // 3 + 1):
    df.iloc[3 * i: 3 * (i + 1)].to_excel(f'{i}.xlsx')

Running this code creates a separate Excel file for every three rows, eliminating the need for manual copying.

The last file contains the remaining row when the total number of rows is not a multiple of three.

Conclusion

This example demonstrates a practical Python automation case for handling Excel files in everyday work. The approach can be adapted to other file‑processing tasks, encouraging you to explore and extend it further.