How to Split an Excel File by Column While Preserving Formatting with Python

1. Introduction

A user in a Python community asked how to split an Excel file into separate worksheets according to a specific column while keeping the original formatting and inserting a pre‑designed summary sheet with formulas.

2. Discussion

Another participant suggested that pandas cannot retain cell formatting, so it is unsuitable for this task.

A third member recommended using openpyxl instead of pandas for handling the formatting and inserting the summary sheet.

The suggested workflow is to first use pandas to perform the data split (if convenient) and then employ openpyxl to open each generated workbook, add the summary sheet, and preserve all formatting and formulas.

3. Solution Overview

1) Use pandas to read the original Excel file and split the data based on the target column. 2) Save each subset as a temporary workbook. 3) Open each temporary workbook with openpyxl. 4) Insert the prepared summary sheet (which contains the required formulas) into the workbook. 5) Save the final workbook, now containing both the split data and the formatted summary sheet.

4. Conclusion

The combination of pandas for data manipulation and openpyxl for format‑preserving operations effectively solves the problem of splitting Excel files while maintaining styles and adding a summary page.