Python Excel AutomationPractical guides for developers
How-to
Getting Started With Python Excel AutomationDeep dive

Set the Active Sheet, Selected Cell and Tab Colours with openpyxl

Decide what a reader sees when a generated workbook opens: which sheet is in front, which cell is selected, where the view is scrolled to, and which tabs are coloured. Includes the trap that leaves two sheets grouped after wb.active changes, and the xlsxwriter equivalents.
Report layouts & templatesopenpyxlMany sheets & many files

The first second after a reader opens a report decides a lot. A workbook that opens on the raw data tab, scrolled to row 4,000 because that is where the script last wrote, feels like a dump; one that opens on the summary, top-left, with the key tabs coloured, feels designed. openpyxl controls all of that, but one of the settings has a trap that leaves sheets grouped together — and edits made to grouped sheets apply to all of them. This page, part of Using openpyxl for Excel File Manipulation, covers the active sheet, the selected cell, the scroll position and tab colours.

A workbook set up to open on its summary The Summary sheet is in front with cell A1 selected, the view scrolled to the top, and three tabs along the bottom. Summary is the active tab; Data and Notes follow. The Summary tab is coloured to stand out. What the reader sees on open report.xlsx A B 1 Metric Value 2 Revenue 48,210 3 Orders 1,310 Summary Data Notes

Prerequisites

Bash
pip install openpyxl xlsxwriter

The examples use report.xlsx, which has three sheets — Data, Summary and Notes — and was last "saved" with the Data tab selected, the way a file saved by Excel would be.

Step 1 — Put a sheet in front with wb.active

wb.active accepts a sheet's index or the worksheet object itself. Using the object is clearer and does not break when sheets are reordered:

Python
from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
wb.active = wb["Summary"]
print(wb.active.title)
# Summary

Reordering the tabs is separate from choosing the active one. To make Summary the first tab as well, move it — Rename, Reorder and Delete Excel Sheets with openpyxl shows wb.move_sheet.

Step 2 — Clear tabSelected so sheets are not grouped

Each worksheet also has a tabSelected flag, which Excel writes for every sheet that was selected when the file was saved. openpyxl does not touch it when you change wb.active, so a file last saved on the Data tab keeps Data selected as well as the new active sheet:

Python
wb.save("grouped.xlsx")
print([(ws.title, ws.sheet_view.tabSelected) for ws in load_workbook("grouped.xlsx").worksheets])
# [('Data', True), ('Summary', None), ('Notes', None)]

Opening such a file, Excel treats the selected sheets as a group — the title bar shows "Group", and typing into one sheet types into all of them. LibreOffice reads it the same way: converting this file and inspecting the result shows both Data and the active sheet marked as selected. Clear the flag on every sheet except the one in front:

Python
def activate(wb, name):
    target = wb[name]
    for ws in wb.worksheets:
        ws.sheet_view.tabSelected = ws is target
    wb.active = target

wb = load_workbook("report.xlsx")
activate(wb, "Summary")
wb.save("summary_first.xlsx")
print([(ws.title, ws.sheet_view.tabSelected) for ws in load_workbook("summary_first.xlsx").worksheets])
# [('Data', False), ('Summary', True), ('Notes', False)]

Use a helper like activate everywhere instead of assigning wb.active directly; the problem only shows up with files that went through Excel at some point, which makes it easy to miss in testing with freshly generated workbooks.

Setting wb.active alone versus clearing tabSelected Two panels. Setting wb.active on a file last saved on the Data tab leaves Data selected too, so the file opens with Data and Summary grouped and edits go to both. The activate helper sets tabSelected to true only on the target sheet, so the file opens with Summary alone selected. wb.active = wb["Summary"] only Data keeps tabSelected two sheets selected on open "[Group]": typing edits both invisible with fresh files activate(wb, "Summary") tabSelected only on Summary one sheet selected on open safe with files from Excel one helper for every job

Step 3 — Choose the selected cell and the scroll position

Each sheet remembers its own selection and scroll position in sheet_view. Set the active cell and its selection range, and topLeftCell for the first visible cell:

Python
wb = load_workbook("summary_first.xlsx")
data = wb["Data"]
sel = data.sheet_view.selection[0]
sel.activeCell = "A1"
sel.sqref = "A1"
data.sheet_view.topLeftCell = "A1"

summary = wb["Summary"]
summary["A1"], summary["B1"] = "Metric", "Value"
summary.sheet_view.selection[0].activeCell = "B1"
summary.sheet_view.selection[0].sqref = "B1"
wb.save("summary_first.xlsx")
print(load_workbook("summary_first.xlsx")["Summary"].sheet_view.selection[0].activeCell)
# B1

Scripts that append to a sheet often leave the view far down the data, especially when the file was last saved by someone scrolled to the bottom. Resetting topLeftCell to A1 on every sheet is a cheap habit. If the sheet has frozen panes, the selection lives in the pane below and to the right of the freeze; the first entry of selection is still a reasonable place to set it, and Freeze the Header Row in Excel with openpyxl explains how panes and selections interact.

Step 4 — Colour the tabs

Tab colours help readers find their way in workbooks with many sheets — summary tabs in one colour, detail tabs in another, notes greyed out. Set tabColor on each sheet's properties with an RGB hex string:

Python
TAB_COLOURS = {"Summary": "0F9488", "Data": "5B5CF0", "Notes": "9AA3B5"}

wb = load_workbook("summary_first.xlsx")
for name, colour in TAB_COLOURS.items():
    wb[name].sheet_properties.tabColor = colour
wb.save("coloured.xlsx")
print({ws.title: ws.sheet_properties.tabColor.rgb for ws in load_workbook("coloured.xlsx").worksheets})
# {'Data': '005B5CF0', 'Summary': '000F9488', 'Notes': '009AA3B5'}

openpyxl stores the colour with an alpha prefix, which is why "0F9488" reads back as "000F9488". Keeping the colours in one dictionary, keyed by sheet name, makes the scheme easy to change and to apply consistently across every workbook a job produces. For sheets created per group (per region, per customer), colour by category rather than individually — three or four colours in a workbook are useful; twenty are noise.

Step 5 — Hidden sheets and the active tab

A hidden sheet cannot be the one in front. If a job hides the sheet that is currently active, openpyxl moves the active tab to another visible sheet when it saves, which may not be the one you intended:

Python
wb = load_workbook("coloured.xlsx")
wb["Notes"].sheet_state = "hidden"
activate(wb, "Summary")          # set the active sheet after hiding
wb.save("final.xlsx")
final = load_workbook("final.xlsx")
print(final.active.title, [(ws.title, ws.sheet_state) for ws in final.worksheets])

Hide first, then activate, then check wb.active.title before saving. Hide Sheets, Rows and Columns with openpyxl covers the hidden and very-hidden states.

Finishing a workbook before it is saved Five finishing steps in order: reorder tabs, hide sheets that readers should not see, activate the front sheet with tabSelected cleared elsewhere, reset each sheet's selection and scroll to A1, then colour the tabs by category. Reorder tabs wb.move_sheet Hide helpers sheet_state Activate clear tabSelected elsewhere Reset views selection and topLeftCell Colour tabs by category

Check the opening state in a test

Because none of these settings shows up in the data, they regress silently — a refactor drops the activate call and nobody notices until a reader complains. A short check, run in the job's tests or just before the file is sent, keeps them honest:

Python
def check_opening_state(path, front="Summary"):
    wb = load_workbook(path)
    problems = []
    if wb.active.title != front:
        problems.append(f"opens on {wb.active.title!r}, expected {front!r}")
    selected = [ws.title for ws in wb.worksheets if ws.sheet_view.tabSelected]
    if selected != [front]:
        problems.append(f"selected tabs {selected} — sheets would open grouped")
    for ws in wb.worksheets:
        if ws.sheet_state == "visible" and (ws.sheet_view.topLeftCell or "A1") != "A1":
            problems.append(f"{ws.title} opens scrolled to {ws.sheet_view.topLeftCell}")
    return problems

print(check_opening_state("grouped.xlsx"))
print(check_opening_state("final.xlsx"))
# [] — opens on Summary, one tab selected, every visible sheet at A1

The first file opens on the right sheet but fails the grouping check: Data is still marked as selected, exactly the state Step 2 warned about, and nothing in the data would have revealed it. The finished file passes all three checks. Test Excel Output with pytest shows how to wire checks like this into a test suite.

The same settings in xlsxwriter

When the workbook is created with xlsxwriter — directly or as pandas' engine — the equivalents are worksheet methods. activate() sets the active sheet and handles the selection for you, so the grouping trap does not arise:

Python
import xlsxwriter

book = xlsxwriter.Workbook("xw_front.xlsx")
data = book.add_worksheet("Data")
summary = book.add_worksheet("Summary")
data.set_tab_color("#5B5CF0")
summary.set_tab_color("#0F9488")
summary.activate()
summary.set_selection("B2")
book.close()

wb = load_workbook("xw_front.xlsx")
print(wb.active.title, [(ws.title, ws.sheet_view.tabSelected) for ws in wb.worksheets])

With pandas, call the same methods on writer.sheets["Summary"] inside the with pd.ExcelWriter(..., engine="xlsxwriter") block, after to_excel has created the sheet.

Common pitfalls

  • Assigning wb.active without clearing tabSelected. Files from Excel open grouped.
  • Using indexes after reordering. wb.active = 1 points at whatever is second now; pass the sheet object.
  • Hiding the active sheet last. The active tab moves; activate after hiding.
  • Leaving the view scrolled down. Reset topLeftCell on sheets your script appended to.

Performance notes

These are workbook-level settings stored in a few XML attributes; setting them costs nothing measurable, and looping over every sheet to reset views is instant even for workbooks with hundreds of sheets. The opening-state check loads the whole workbook, so for very large files run it in tests against a sample output rather than on every production run.

Conclusion

Put the right sheet in front with an activate helper that sets wb.active and clears tabSelected on every other sheet, set each sheet's selection and topLeftCell so it opens at the top, colour tabs through sheet_properties.tabColor, and activate after hiding sheets. With xlsxwriter, activate(), set_selection() and set_tab_color() do the same jobs.

Frequently asked questions

Why does Excel show Group in the title bar after I set wb.active? The sheet that was selected when the file was last saved still has tabSelected set, and openpyxl does not clear it when you change wb.active. Two sheets are then selected together. Set tabSelected to False on every sheet except the active one.

How do I make a workbook open with a particular cell selected? Set activeCell and sqref on the first entry of the sheet's sheet_view.selection to the cell address, and make that sheet active. Setting topLeftCell as well scrolls the view so the cell is visible.

What colour format does tabColor take? An RGB hex string such as "1072BA" (or with an alpha prefix, "FF1072BA"), or an openpyxl Color object. It is set on ws.sheet_properties.tabColor.

Can the active sheet be hidden? No. A hidden sheet cannot be the one in front. When the active sheet is hidden, openpyxl moves the active tab to another visible sheet when saving, so set the active sheet after hiding sheets and check it.

More on report layouts & templates

More on report layouts & templates →