Split One Sheet into Many Files and Format the Output

Here is a job many office workers dread: one big sheet has every region, client or teacher's data, and each person needs only their own part. Doing it by hand means filtering, copying, pasting and saving dozens of times. A script does it in one go.

In this lesson you will split one sheet into separate files (or separate sheets), then make the output look professional with bold headers, sensible column widths and number formats.

What You'll Learn

• How to split one table into one file per group
• How to put each group on its own sheet instead
• How to download many files at once as a zip
• How to format headers, widths and numbers with openpyxl
• How to add a total row and freeze the header

The idea: filter, save, repeat

Splitting is just a loop: for each region, filter the rows for that region and save them. Here is the idea in the browser. The playground cannot save Excel files, so it prints what each file would contain.

Ask for the split script

The script will use a loop like the one above, plus a step to zip the files. Downloading one zip is much easier than clicking "download" 15 times.

Check the split: the row counts of all the files should add up to the rows in the original. If they do not, some rows probably have an empty or misspelled Region. Clean that column first, as in the last lesson.

One file with many sheets instead

Sometimes you want one workbook with a sheet per region instead of many files. Just ask:

"Instead of separate files, save one file called sales_by_region.xlsx with one sheet per Region, named after the region."

Note: Excel sheet names can be at most 31 characters and cannot contain characters like / or ?. If your groups have long names, ask the AI to shorten or clean the sheet names.

Make the output look professional

A file saved by pandas has plain headers and narrow columns. A few formatting touches make it ready to send. This is where openpyxl comes in. Ask for it like this:

A typical formatting step looks like this. You do not need to memorize it; just recognize the pieces:

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

wb = load_workbook("sales_north.xlsx")
ws = wb.active

# Bold header with a light grey background
for cell in ws[1]:
    cell.font = Font(bold=True)
    cell.fill = PatternFill("solid", start_color="DDDDDD")

# Keep the header visible when scrolling
ws.freeze_panes = "A2"

# Revenue in column E: thousands separator, no decimals
for cell in ws["E"][1:]:
    cell.number_format = "#,##0"

wb.save("sales_north.xlsx")

Two things to notice:

• ws[1] means row 1, the header row. ws["E"] means column E. These match Excel's own row numbers and column letters.
• A formula like =SUM(E2:E20) written by the script stays a live Excel formula. If someone changes a number in Excel, the total updates. That is often better than writing a fixed number.

Check the formatted file

Open one output file in Excel and check:

• Is the header bold and frozen?
• Do the numbers show separators, and are they still numbers (right-aligned, can be summed), not text?
• Does the TOTAL row's formula cover all the rows, including the last one? An off-by-one range like E2:E19 instead of E2:E20 is a classic mistake.

Google Sheets users: you can open these .xlsx files in Google Sheets and most formatting carries over.

Key Takeaways

• Splitting is a loop: filter one group, save it, repeat for each group.
• Check that the split files' row counts add up to the original.
• Ask for a zip download, or one workbook with a sheet per group.
• openpyxl adds bold headers, column widths, number formats, frozen headers and live SUM formulas.
• Always open one output and check the formatting, number types and formula ranges.