Fit an Excel Sheet to One Page Wide When Exporting to PDF

A wide monthly report exported to PDF comes out as nine pages: the first three months on page one, the next months on page four, and nobody can match a number to its row. Fit-to-page fixes the split in three lines of openpyxl, and creates a new problem: the text shrinks until everything fits, possibly to a size nobody can read. This guide, part of Exporting Excel Reports to PDF, sets the sheet to one page wide, predicts the printed text size before converting, and uses the layout changes that keep it readable. Each layout below was exported through LibreOffice and measured.

Prerequisites

pip install openpyxl pypdf

The PDF conversion uses LibreOffice on Linux or macOS, as described in Convert Excel to PDF on Linux with LibreOffice; Excel itself honours the same settings. A wide report to work with — twelve months plus five summary columns:

from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.page import PageMargins
from openpyxl.worksheet.properties import PageSetupProperties

def make_report():
    wb = Workbook()
    ws = wb.active
    ws.title = "Sales"
    months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
    ws.append(["Region"] + [f"{m} 2026" for m in months] + ["Total", "Target", "Variance", "Share", "Notes"])
    for r in range(120):
        ws.append([f"Store {r + 1:03d}"] + [1000 + r * 13 + c * 7 for c in range(12)] + [0, 0, 0, 0.1, "ok"])
    for c in range(1, 19):
        ws.column_dimensions[get_column_letter(c)].width = 12
    ws.page_setup.paperSize = ws.PAPERSIZE_A4
    return wb

wb = make_report()
ws = wb.active

Step 1 — Fit to one page wide, any number of pages down

Three settings work together. fitToWidth = 1 asks for one page across; fitToHeight = 0 means no limit downwards, so a long report still flows onto as many pages as it needs; and fitToPage = True switches the sheet from percentage scaling to fit mode — without it the other two are ignored:

ws.sheet_properties.pageSetUpPr = PageSetupProperties(fitToPage=True)
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0
ws.print_title_rows = "1:1"                         # repeat the header on every page
wb.save("sales_fit.xlsx")

Leaving fitToHeight at its default of 1 squeezes the entire sheet onto a single page, which for 120 rows makes the text microscopic. Repeating row 1 keeps the month headings above the numbers on every page, since only the first page would otherwise show them. The other page-setup options — print areas, paper size, orientation — are covered in Set Print Area and Page Setup with openpyxl.

Exported to PDF, the unscaled sheet took 9 pages; the fitted one took 2. But the heading text measured 4.4 points: the sheet was scaled to about 40% to get 18 columns across a portrait A4 page.

Step 2 — Predict the scale before converting

The scale is simple arithmetic: printable width divided by the total width of the printed columns. Column widths are in characters of the default font; for 11-point Calibri one character is 7 pixels plus 5 pixels of padding per column, and a pixel is 0.75 points:

PAPER_WIDTH_PT = {9: (595.3, 841.9), 1: (612.0, 792.0)}   # A4, Letter (portrait w, h)

def column_points(ws, letter, default=8.43):
    dim = ws.column_dimensions[letter] if letter in ws.column_dimensions else None
    if dim is not None and dim.hidden:
        return 0.0
    width = dim.width if dim is not None and dim.width else default
    return (int(width * 7) + 5) * 0.75

def estimated_scale(ws, pages_wide=1, repeat_cols=0):
    paper_w, paper_h = PAPER_WIDTH_PT[ws.page_setup.paperSize or 9]
    if ws.page_setup.orientation == "landscape":
        paper_w = paper_h
    printable = paper_w - (ws.page_margins.left + ws.page_margins.right) * 72
    letters = [get_column_letter(c) for c in range(1, ws.max_column + 1)]
    total = sum(column_points(ws, l) for l in letters)
    total += (pages_wide - 1) * sum(column_points(ws, l) for l in letters[:repeat_cols])
    return min(1.0, printable * pages_wide / total)

scale = estimated_scale(ws)
print(f"scale {scale:.0%}, 11 pt text prints at {11 * scale:.1f} pt")
# scale 41%, 11 pt text prints at 4.5 pt

The estimate of 4.5 points matched the measured 4.4 in the PDF. For every layout below it came within half a point, and always on the high side, so set the threshold with a small margin — treat an estimate of 7.5 points as the floor if 7 is the real limit. That makes it a cheap gate: run it while building the workbook and change the layout automatically instead of discovering unreadable PDFs later.

Step 3 — Buy width: landscape, then margins

Landscape is the first and biggest gain, turning 487 points of printable width into 734. Narrower margins add more; a quarter inch on each side is safe for office printers and irrelevant for PDFs read on screen:

ws.page_setup.orientation = "landscape"
print(f"landscape: {11 * estimated_scale(ws):.1f} pt")
ws.page_margins = PageMargins(left=0.25, right=0.25, top=0.5, bottom=0.5)
print(f"narrow margins: {11 * estimated_scale(ws):.1f} pt")   # 6.7, then 7.4
wb.save("sales_landscape.xlsx")

Measured, those were 6.7 and 7.4 points — better, still small. Past this point the paper has no more width to give; the remaining gains come from the columns.

Step 4 — Print fewer columns

Hidden columns are not printed. Working columns that help build the report — targets, shares, notes — can stay in the workbook for anyone who opens it and still drop out of the PDF:

for letter in "OPQR":                               # Target, Variance, Share, Notes
    ws.column_dimensions[letter].hidden = True
print(f"helpers hidden: {11 * estimated_scale(ws):.1f} pt")   # 9.5 estimated, 9.2 measured
wb.save("sales_hidden.xlsx")

With four columns hidden the measured size was 9.2 points: comfortably readable, still one page wide. When hiding is not appropriate, narrow the columns instead — numbers in a #,##0 format rarely need twelve characters — or set a print area that covers only the range the PDF should show.

Step 5 — When every column must stay, go two pages wide

Some reports cannot lose a column. Then print them two pages wide on purpose, and repeat the label column so each page stands alone:

wb = make_report()
ws = wb.active
ws.sheet_properties.pageSetUpPr = PageSetupProperties(fitToPage=True)
ws.page_setup.fitToWidth = 2
ws.page_setup.fitToHeight = 0
ws.page_setup.pageOrder = "overThenDown"            # left half, right half, next rows
ws.print_title_rows = "1:1"
ws.print_title_cols = "A:A"                         # store names on every page
ws.page_margins = PageMargins(left=0.5, right=0.5, top=0.5, bottom=0.5)
print(f"two pages wide: {11 * estimated_scale(ws, pages_wide=2, repeat_cols=1):.1f} pt")   # 9.1 estimated, 8.6 measured
wb.save("sales_two_wide.xlsx")

The export measured 8.6 points in portrait, on 4 pages, and the right-hand pages opened with the "Region" heading and the store names. overThenDown puts the two halves of the same rows next to each other in the PDF; the default, downThenOver, prints all the left halves first, which is awkward to read on screen.

Step 6 — Check the PDF, not the settings

Settings can be right and the export still surprising: a column width set later in the code, a print area left over from a template. Convert, then check the page count and the size of the heading text with pypdf:

import subprocess
from pypdf import PdfReader

subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "sales_hidden.xlsx"],
               check=True, timeout=120)

def heading_size(page, heading="Region"):
    sizes = []
    def visit(text, cm, tm, font, size):
        if text.strip() == heading:
            sizes.append(size * abs(tm[0]))
    page.extract_text(visitor_text=visit)
    return round(sizes[0], 1) if sizes else None

pdf = PdfReader("sales_hidden.pdf")
print(len(pdf.pages), "pages, heading at", heading_size(pdf.pages[0]), "pt")

pypdf reports the font size and the text matrix separately, and the matrix carries the scaling; their product is the printed size. Failing the job when the size drops below a threshold turns "the PDF is unreadable" from a complaint into a test.

Common pitfalls

• fitToPage missing. fitToWidth without pageSetUpPr.fitToPage = True changes nothing. It is the most common reason the setting "does not work".
• fitToHeight left at 1. The default fits the whole sheet on one page. Set it to 0 for long reports.
• Unset column widths. Columns without an explicit width use the default of about 8.43 characters, but openpyxl reports 13 for a column it has not seen. The estimate above checks in ws.column_dimensions to avoid creating one.
• Assuming the default font. The estimate assumes 11-point Calibri. A report in a wider font, or with larger header text, prints smaller than predicted; check the PDF.

Conclusion

fitToWidth = 1, fitToHeight = 0 and fitToPage = True put a wide sheet on one page across. Estimate the resulting scale from the column widths before converting; when text would print below about 7 points, use landscape and narrower margins, hide helper columns, or print two pages wide with the label column repeated, and confirm the result by measuring the PDF.

Frequently asked questions

How do I make an Excel sheet print one page wide from Python? With openpyxl, set ws.page_setup.fitToWidth = 1, ws.page_setup.fitToHeight = 0 and ws.sheet_properties.pageSetUpPr.fitToPage = True. The zero height means as many pages down as needed. Without fitToPage the two numbers are ignored.

Why is the text tiny after fitting the sheet to one page? Fitting scales the whole sheet down until every column fits. An 18-column report on portrait A4 printed at 40%, turning 11-point text into 4.4 points. Switch to landscape, narrow the margins or remove columns before fitting.

How small can the scale go before a PDF report is hard to read? Body text below about 7 points is hard to read on paper and needs zooming on screen. For 11-point cells that means a scale of roughly 65% or more; below that, change the layout instead of fitting harder.

Do hidden columns appear in the PDF? No. Hidden columns are not printed, so hiding helper or working columns is a quick way to make a sheet narrower for export without deleting data from the workbook.

Related

• Up one level: Exporting Excel Reports to PDF — turning workbooks into PDF reports.
• Add Headers, Footers and Page Numbers to an Excel PDF — "page 2 of 4" on the pages you just laid out.
• Convert Only Selected Sheets of a Workbook to PDF — leaving data sheets out of the export.
• Merge Several Excel PDF Exports into One File — combining the results into one document.