Formatting & presenting reports
Protection & sharing
Locked cells, passwords, hidden sheets and comments for workbooks other people will open.
Start with the full guides
The long-form treatment of protection & sharing, with the reasoning the shorter pages assume.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toAdd Comments and Notes to Excel Cells with PythonAnnotate generated Excel reports from Python: attach openpyxl comments, size them, read existing notes back, and use document properties to record where the numbers came from.
- How-toHide Sheets, Rows and Columns with openpyxlTidy a generated Excel report from Python: hide working sheets, use veryHidden, collapse rows and columns, add outline grouping, and avoid the last-visible-sheet corruption.
- How-toLet Different People Edit Different RangesBuild a shared budget workbook in which each team can edit only its own column: Excel's Allow Edit Ranges written with XlsxWriter's unprotect_range, one range and password per team on a protected sheet, what users see when they type in someone else's range, why openpyxl silently deletes these ranges when it saves the file, and a small function that puts them back.
- How-toLock Cells and Protect a Sheet with openpyxlStop readers overwriting formulas in a generated report: unlock the input cells, enable sheet protection, choose which operations stay allowed, and verify it on the way out.
- How-toMake an Excel File Open Read-Only RecommendedStop colleagues from accidentally editing a published report: set the read-only recommended flag with xlsxwriter, add it to openpyxl workbooks with a small post-save patch, mark a workbook as final, keep the flag from being dropped when the file is re-saved, and understand what these settings do and do not protect.
- How-toMake an Excel Report Accessible with PythonGenerate workbooks that screen-reader users and people with low vision can use: meaningful sheet names and a title, data in real tables with header rows, alternative text on charts and images, no merged cells, meaning that does not rely on colour alone, colours that pass contrast checks, and an automated audit that flags the problems in any workbook.
- How-toOpen a Password-Protected Excel File with PythonDecrypt an encrypted workbook in memory with msoffcrypto-tool, read it with pandas or openpyxl, handle wrong passwords, and tell encryption from sheet protection.
- How-toPassword Protect an Excel File with PythonEncrypt an .xlsx so it cannot be opened without a password, using msoffcrypto-tool — write, read back, handle wrong passwords, and keep the secret out of your source code.
- How-toProtect Workbook Structure with openpyxlStop readers adding, deleting, renaming, moving, hiding or unhiding sheets in a generated report by locking the workbook structure with openpyxl — combined with very hidden lookup sheets and sheet protection — and understand how weak the stored password is and what the lock does not cover.
- How-toRemove Hidden Data Before Sharing an Excel FileA workbook sent outside the team often carries more than the visible numbers: hidden and very hidden sheets, hidden rows and columns, comments with names in them, formulas that point at a salary sheet, named ranges and author details. Audit a workbook for all of it with openpyxl, then build a clean copy with values only, and check the copy before it goes out.
- How-toRemove Sheet Protection from a Workbook You Own with openpyxlUpdate your own protected report templates from Python — turn off sheet and workbook structure protection, make changes, and put the original protection back exactly as it was, including Excel's SHA-512 password hashes. Covers what openpyxl keeps, what "unprotecting" does and does not change, and batch maintenance of many files.
- How-toAnonymise Personal Data in an Excel File with PythonPrepare a workbook of customer or employee records for sharing: drop direct identifiers, replace IDs with keyed pseudonyms that stay consistent across files, generalise ages and postcodes, scrub emails and phone numbers out of free text, check how many people share each combination of details, and scan the output for anything left behind.
- How-toPassword-Protect a PDF Exported from ExcelEncrypt the PDF your Excel report becomes: AES-256 with pypdf after conversion, or encryption set directly in the LibreOffice export filter (and why it is weaker), the difference between open and permissions passwords, one password per recipient, verifying the file really is locked, and what PDF permissions can and cannot stop.
- How-toSign a PDF Report Digitally with PythonAdd a cryptographic signature to a PDF exported from an Excel report with pyHanko, so readers can prove who issued it and that nobody changed it afterwards: load a certificate from a PKCS#12 file, sign with a reason and location, add a visible signature box, validate the result in Python and with pdfsig, and see exactly what a one-byte change does to it.
- How-toDefine Names and Protect Sheets with XlsxWriterBuild an input template with XlsxWriter in which formulas use defined names instead of cell addresses and only the input cells can be edited: workbook and sheet-scoped names for ranges and constants, a dropdown fed by a named list on a hidden sheet, unlocked input cells, hidden formulas, protect() options that still allow sorting and filtering, and a check of the result with openpyxl and LibreOffice.
Follow it through a track
Prefer an ordered route? These tracks cover protection & sharing along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
Protection & sharing is part of Formatting & presenting reports.