Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Hide Gridlines and Set the Zoom Level with openpyxl

Control how a generated workbook looks the moment it opens: hide gridlines and headings, set the zoom, choose the selected cell and the first visible row, open in Page Layout view, hide zeros, set right-to-left sheets, and avoid the grouped-sheets trap when changing the active sheet.
Cell styling & layoutopenpyxl

The first seconds after someone opens a report decide whether it looks designed or dumped. Grid lines everywhere, a zoom level that shows three columns, the cursor sitting in some random cell halfway down the data, or — worse — the title bar saying [Group] because two sheets were left selected: none of these change the numbers, but all of them make the file feel unfinished. They are all sheet view settings, stored per worksheet, and openpyxl exposes them through ws.sheet_view. This page, part of Styling Excel Cells with openpyxl, covers each one and the trap that catches most scripts.

Prerequisites

Bash
pip install openpyxl

regional.xlsx has a Summary sheet laid out as a small report — a title in B2 and a table starting in B4 — and a Data sheet with 200 rows. Like every file Excel itself saves, its active sheet carries a tabSelected flag, which matters in Step 5.

Where the view settings live A workbook contains worksheets, each worksheet has a sheet view holding gridlines, zoom, view mode and zeros, and the sheet view holds a selection with the active cell. Workbook wb.active — which sheet opens first Worksheet sheet_view showGridLines, zoomScale, view, showZeros, tabSelected Pane and selection topLeftCell, activeCell, sqref

Step 1 — Hide gridlines and headings

Gridlines are the faint lines around every cell. A report that draws its own borders looks cleaner without them. Row and column headings — the A B C and 1 2 3 — can go too on a sheet that is meant to be read, not edited:

Python
from openpyxl import load_workbook

wb = load_workbook("regional.xlsx")
ws = wb["Summary"]
ws.sheet_view.showGridLines = False
ws.sheet_view.showRowColHeaders = False
print(ws.sheet_view.showGridLines, ws.sheet_view.showRowColHeaders)

These settings affect only the screen. Whether gridlines print is a separate page-setup option, ws.print_options.gridLines, which is off by default — so a sheet can show gridlines on screen and print without them, or the other way round. Keep headings on data sheets: people need them to refer to cells, to read the row count at a glance and to use filters sensibly.

Step 2 — Set the zoom

zoomScale is a percentage from 10 to 400. A summary with large fonts often reads best at 90–110%; a wide data sheet at 80–85% so more columns fit on a laptop screen:

Python
ws.sheet_view.zoomScale = 110
wb["Data"].sheet_view.zoomScale = 85
print({s.title: s.sheet_view.zoomScale for s in wb.worksheets})

Each sheet keeps its own zoom, and Excel remembers the one saved in the file until the reader changes it. The Page Layout and Page Break Preview views have their own zoom attributes, zoomScalePageLayoutView and zoomScaleSheetLayoutView; zoomScale applies to whichever view the sheet opens in.

The same sheet with default and designed view settings Two panels. The default view has gridlines, headings, 100 percent zoom and the cursor wherever it was left. The designed view hides gridlines and headings, uses 110 percent zoom, selects the title cell and starts scrolled to the top. Default view gridlines on every cell headings A B C and 1 2 3 zoom 100% cursor where it was last left Designed view no gridlines, own borders headings hidden on the summary zoom 110% B2 selected, scrolled to the top

Step 3 — Choose the selected cell and the scroll position

When the file opens, the cursor sits on the active cell and the window shows the sheet from its top-left cell. Point both at the start of the report:

Python
from openpyxl.worksheet.views import Selection

ws.sheet_view.selection = [Selection(activeCell="B2", sqref="B2")]
ws.sheet_view.topLeftCell = "A1"
data = wb["Data"]
data.sheet_view.selection = [Selection(activeCell="A2", sqref="A2")]
print(ws.sheet_view.selection[0].activeCell, ws.sheet_view.topLeftCell)

activeCell is where the cursor is; sqref is the highlighted range, which can be larger — sqref="B4:E8" with activeCell="B4" opens with the table selected. On a sheet with frozen panes the selection belongs to a pane: ws.freeze_panes = "A2" creates a bottom pane, and the selection should name it with pane="bottomLeft". Set freeze_panes first and openpyxl builds matching selections for you — see Freeze the Header Row in Excel with openpyxl.

Step 4 — Page Layout view, zeros and right-to-left

Three more settings are occasionally exactly what a report needs:

Python
ws.sheet_view.showZeros = False            # West's 0 sales show as a blank cell
wb["Data"].sheet_view.view = "pageLayout"  # opens showing pages, margins, header and footer
arabic = wb.create_sheet("تقرير")
arabic.sheet_view.rightToLeft = True       # column A on the right, for RTL languages
print(ws.sheet_view.showZeros, wb["Data"].sheet_view.view, arabic.sheet_view.rightToLeft)
  • showZeros = False hides every zero on the sheet, which tidies sparse tables but also hides genuine zeros — the West region's sales here. For more control, use a number format such as #,##0;-#,##0;;@, whose empty third section hides zeros in chosen cells only.
  • view is "normal", "pageLayout" or "pageBreakPreview". Page Layout suits sheets that are printed, because the reader sees the page boundaries and the header and footer.
  • rightToLeft mirrors the sheet for Arabic, Hebrew and other right-to-left languages.

Step 5 — Change the active sheet without grouping sheets

wb.active decides which tab is showing when the workbook opens. Setting it looks harmless:

Python
wb.active = wb["Data"]
print([(s.title, s.sheet_view.tabSelected) for s in wb.worksheets])

But Summary still has tabSelected = True, copied from the file as Excel saved it. openpyxl sets the new active sheet but does not clear the flag on the old one, so both sheets are selected when the file opens: Excel shows [Group] in the title bar, and anything the reader types lands on both sheets at once. It is an easy way to corrupt a report by accident. Clear the flag everywhere and set it only on the sheet that should be active:

Python
def set_active(wb, sheet):
    for s in wb.worksheets:
        s.sheet_view.tabSelected = False
    wb.active = wb[sheet]
    wb[sheet].sheet_view.tabSelected = True

set_active(wb, "Summary")
print([(s.title, s.sheet_view.tabSelected) for s in wb.worksheets])
wb.save("regional_view.xlsx")
Two tabs selected — the file opens in Group mode A workbook window whose title reads Group, with the Summary and Data tabs both highlighted as selected, which means edits apply to both sheets. regional.xlsx [Group] A B C 1 Region Sales Target 2 North 48,210 45,000 3 South 39,980 42,000 Summary Data تقرير Both tabs selected typing edits both sheets clear tabSelected everywhere first

The same applies after copy_worksheet or when reading a template whose author left several sheets selected. Running set_active just before saving is a cheap habit for any workbook that went through openpyxl.

Apply one house style to every sheet

Reports built by several scripts drift: one sheet at 100%, another at 70% because someone was looking at it on a big monitor, gridlines on here and off there. A single function run just before save gives every workbook the same finish. Treat presentation sheets and data sheets differently, and finish with the active-sheet fix:

Python
from openpyxl.worksheet.views import Selection

def finish_views(wb, first="Summary", presentation=("Summary",)):
    for s in wb.worksheets:
        is_report = s.title in presentation
        s.sheet_view.showGridLines = not is_report
        s.sheet_view.showRowColHeaders = not is_report
        s.sheet_view.zoomScale = 110 if is_report else 85
        s.sheet_view.topLeftCell = "A1"
        if not s.freeze_panes:
            s.sheet_view.selection = [Selection(activeCell="A1", sqref="A1")]
    set_active(wb, first)

wb = load_workbook("regional.xlsx")
finish_views(wb)
print([(s.title, s.sheet_view.zoomScale, s.sheet_view.showGridLines) for s in wb.worksheets])
wb.save("regional_finished.xlsx")

Sheets with frozen panes keep the selection openpyxl built for their pane, which is why the function only resets the selection when there is no freeze. Keeping the rules in one function also means changing the house style later — a different zoom for data sheets, say — is a one-line edit rather than a hunt through every report script. The same function fits naturally next to the other finishing steps in Apply a Reusable Style Theme Across an Excel Report.

The same settings in xlsxwriter

When the file is written from scratch with xlsxwriter, the settings are worksheet methods and the grouping problem does not arise, because activate() handles selection:

Python
import xlsxwriter

book = xlsxwriter.Workbook("regional_xw.xlsx")
summary = book.add_worksheet("Summary")
detail = book.add_worksheet("Data")
summary.write("B2", "Regional sales — September 2026")
summary.hide_gridlines(2)       # 2 = hide on screen and in print
summary.set_zoom(110)
summary.set_selection("B2")
detail.set_zoom(85)
detail.set_page_view()
summary.activate()
book.close()
print("written")

hide_gridlines(1) hides only printed gridlines, 2 hides both. The xlsxwriter method names map one-to-one to the openpyxl attributes above, so either library can produce the same finished view; Write a Formatted Excel Report with xlsxwriter puts them in context.

Common pitfalls

  • Grouped sheets. Clear tabSelected on every sheet before marking the active one.
  • Hidden sheet as active. Never make a hidden sheet active; set the active sheet first, then hide others.
  • Confusing screen and print gridlines. sheet_view.showGridLines is the screen, print_options.gridLines is paper.
  • showZeros hiding real data. It applies to the whole sheet; use a number format for targeted blanks.
  • Selections in frozen sheets. A selection without the right pane is ignored or moved by Excel; set freeze_panes before the selection.

Conclusion

Set the view once, just before saving: hide gridlines and headings on presentation sheets, give each sheet a sensible zoomScale, point the selection and topLeftCell at the start of the report, and choose Page Layout view for sheets that get printed. Then clear tabSelected on every sheet and set it — together with wb.active — only on the sheet the reader should see first, so the file never opens grouped.

Frequently asked questions

How do I hide gridlines in Excel with openpyxl? Set ws.sheet_view.showGridLines = False on each sheet. That hides them on screen; printed gridlines are controlled separately by ws.print_options.gridLines, which is off by default.

How do I set the zoom level with openpyxl? Set ws.sheet_view.zoomScale to a percentage between 10 and 400, for example 85. Each sheet has its own zoom, and Excel opens the sheet at that level.

Why does my workbook open with Group in the title bar? More than one sheet has tabSelected set. Setting wb.active in openpyxl does not clear the flag on the previously active sheet. Set tabSelected = False on every worksheet, then set wb.active and tabSelected = True on the one sheet that should open.

How do I make a workbook open at a specific cell? Set the sheet's selection to Selection(activeCell="B2", sqref="B2") and its topLeftCell to the cell that should appear at the top-left of the window, and make that sheet the active one.

More on cell styling & layout

More on cell styling & layout →