Apply the Same Change to Every Sheet in a Workbook

A workbook with forty monthly sheets needs the same fix on each one: bold the headings, freeze the top row, show the amounts with thousands separators, put the sheet name and page number in the footer. In Excel that is forty repetitions — or grouping the sheets and hoping nothing goes wrong, since grouped edits silently apply to every selected sheet, including the ones that should have been left alone. With openpyxl it is one loop. The interesting part is not the loop but the guards around it: workbooks contain chart sheets, hidden lookup sheets and the odd summary sheet with a different layout, and a change applied blindly to those breaks them. This page applies a set of changes to every suitable sheet, skips the rest with a reason, logs what happened on each sheet, and makes the whole run safe to repeat. It belongs to Working with Multiple Excel Sheets in Python.

Prerequisites

pip install openpyxl

The workbook has nine monthly sheets with the same columns — Date, Region, Units, Revenue, Margin % — plus a Summary sheet with a different layout, a hidden Lookup sheet and a chart sheet. The changes should reach the nine monthly sheets and nothing else.

Step 1 — Decide which sheets to touch

from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

EXPECTED = ["Date", "Region", "Units", "Revenue", "Margin %"]

def eligible(sheet):
    if not isinstance(sheet, Worksheet):
        return False, "chart sheet"
    if sheet.sheet_state != "visible":
        return False, f"{sheet.sheet_state} sheet"
    headers = [c.value for c in sheet[1]]
    if headers[:len(EXPECTED)] != EXPECTED:
        return False, f"unexpected headings {headers[:3]}…"
    return True, ""

wb = load_workbook("monthly_sales.xlsx")
for s in wb.worksheets + wb.chartsheets:
    ok, why = eligible(s)
    print(f"{s.title:10} {'apply' if ok else 'skip: ' + why}")

wb.worksheets contains only ordinary sheets; chart sheets live in wb.chartsheets, and they have no cells, so any cell operation on them would fail. Checking the type explicitly keeps the rule in one place. Hidden and very hidden sheets are skipped because they are usually lookups or calculations that should not be restyled. The most useful guard is the last one: a sheet is changed only if its first row has the expected headings. The summary sheet, which starts with three blank rows and has different columns, is left alone — exactly the sheet that would have been damaged by a grouped edit in Excel.

Step 2 — The change itself, written once

from openpyxl.styles import Alignment, Border, Font, PatternFill, Side

FORMATS = {"Date": "dd mmm yyyy", "Units": "#,##0", "Revenue": "£#,##0.00", "Margin %": "0.0%"}
header_font, header_fill = Font(bold=True, color="FFFFFF"), PatternFill("solid", fgColor="1F3864")
rule = Border(bottom=Side(style="thin", color="1F3864"))

def standardise(ws):
    changes = []
    for cell in ws[1]:
        cell.font, cell.fill, cell.border = header_font, header_fill, rule
        cell.alignment = Alignment(horizontal="center", vertical="center")
    changes.append("header styled")
    if ws.freeze_panes != "A2":
        ws.freeze_panes = "A2"
        changes.append("top row frozen")
    for cell in ws[1]:
        fmt = FORMATS.get(cell.value)
        if fmt:
            for (c,) in ws.iter_rows(min_row=2, min_col=cell.column, max_col=cell.column):
                c.number_format = fmt
            changes.append(f"{cell.value} → {fmt}")
        ws.column_dimensions[cell.column_letter].width = max(12, len(str(cell.value)) + 4)
    ws.oddFooter.left.text = "&A"                       # sheet name
    ws.oddFooter.right.text = "Page &P of &N"
    ws.page_setup.orientation = "landscape"
    ws.sheet_properties.pageSetUpPr.fitToPage = True
    ws.page_setup.fitToHeight = 0
    ws.print_title_rows = "1:1"
    changes.append("footer and print setup")
    return changes

All the changes live in one function that takes a worksheet and returns a list of what it did. Number formats are chosen by column heading, not by letter, so a sheet where Units sits in a different column still gets the right format. The print setup — landscape, fit to one page wide, heading row repeated on every page — and a footer with the sheet name (&A) and page numbers (&P of &N) make every month print the same way. Keeping the change in a function also makes it testable on its own, and reusable for a single sheet created later — next month's sheet can be standardised the moment it is added.

Step 3 — Apply, log and save to a new file

log = []
for ws in wb.worksheets:
    ok, why = eligible(ws)
    if not ok:
        log.append({"Sheet": ws.title, "Result": "skipped", "Detail": why})
        continue
    log.append({"Sheet": ws.title, "Result": "changed", "Detail": "; ".join(standardise(ws))})
for cs in wb.chartsheets:
    log.append({"Sheet": cs.title, "Result": "skipped", "Detail": "chart sheet"})
wb.save("monthly_sales_standardised.xlsx")

import pandas as pd
print(pd.DataFrame(log).to_string(index=False, max_colwidth=70))

The loop goes through the sheets, applies the function where the guards allow and records a line for every sheet either way. The log answers the question someone will ask later — did the change reach every month? — and it shows the skipped sheets with their reason, so a monthly sheet that was skipped because someone retyped a heading is noticed rather than silently left unformatted. The result is saved to a new file, which keeps the original as a fallback; overwriting in place is only safe once the script has been run against a copy and checked.

Choosing the guard

The heading check is the guard that matters most, and it is worth thinking about how strict it should be. Requiring the first five headings to match exactly, as here, is strict: a sheet where someone renamed Revenue to Sales is skipped and shows up in the log. A looser check — say, that the first row contains Date and Revenue somewhere — would format more sheets, but it would also format a sheet that merely happens to contain those words. For a one-off clean-up the looser check is often fine; for a script that runs every month on files other people edit, the strict check and a log that is actually read are the safer pair. Either way, put the rule in the eligible function so there is one place to change it.

Step 4 — Run it again safely, and across many files

wb2 = load_workbook("monthly_sales_standardised.xlsx")
again = [standardise(ws) for ws in wb2.worksheets if eligible(ws)[0]]
print("second run, first sheet:", again[0])

from pathlib import Path
def standardise_file(path, out_dir):
    book = load_workbook(path)
    changed = sum(1 for ws in book.worksheets if eligible(ws)[0] and standardise(ws))
    Path(out_dir).mkdir(exist_ok=True)
    book.save(Path(out_dir) / Path(path).name)
    return changed
print("sheets changed:", standardise_file("monthly_sales.xlsx", "standardised"))

A change script should give the same result however many times it runs. Every operation here sets a value rather than adding to one — the footer is replaced, not appended to; the freeze is set to A2 — so a second run leaves the workbook as it was. The freeze check even reports nothing when the panes are already frozen, which makes the log of a repeat run shorter: the second run on the first sheet reports the styling and formats it re-applied, but no top row frozen, because there was nothing to do. Run against the original file, the folder version reports nine sheets changed — the nine months — and leaves the summary, the lookup and the chart sheet as they were. Wrapping the whole thing in a function that takes a path makes it easy to apply to every workbook in a folder, skipping the ~$ lock files Excel leaves behind, as described in Ignore Excel Lock Files When Processing a Folder. Remember that openpyxl does not keep everything when it saves an existing file — pivot tables, slicers and some charts are lost or simplified — so test on a copy of any workbook that contains them.

Common pitfalls

• Treating every sheet the same. Skip chart sheets, hidden sheets and sheets with a different layout.
• Formats by column letter. Choose them by heading so moved columns still get the right format.
• No record of what changed. Log every sheet, including the skipped ones and why.
• Changes that accumulate. Set values instead of appending, so the script can run twice safely.
• Overwriting the original. Save to a new file until the script is trusted.

Conclusion

To apply one change to every sheet, put the change in a function that takes a worksheet, guard it with a check that skips chart sheets, hidden sheets and sheets without the expected headings, and loop over the workbook logging what happened on each sheet. Choose formats by heading, make every operation idempotent, save to a new file, and wrap it in a per-file function to run it across a folder.

Frequently asked questions

How do I loop over all sheets in a workbook with openpyxl? Iterate over wb.worksheets for normal sheets; chart sheets are in wb.chartsheets.

How do I skip hidden sheets in openpyxl? Check ws.sheet_state, which is "visible", "hidden" or "veryHidden".

How do I add the sheet name and page number to a footer with openpyxl? Set ws.oddFooter.left.text = "&A" and ws.oddFooter.right.text = "Page &P of &N".

How do I make sure a formatting script can run twice? Have every step set a value rather than add to one, so a second run leaves the workbook unchanged.

Related

• Up one level: Working with Multiple Excel Sheets in Python — many sheets and files.
• Rename, Reorder and Delete Excel Sheets with openpyxl — changing the sheets themselves.
• Search Every Sheet and Workbook for a Value — reading across sheets.
• Freeze the Header Row in Excel with openpyxl — one of the changes.