Remove Hidden Data Before Sharing an Excel File

A 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.

Protection & sharing openpyxl

Hiding is a layout tool, not a privacy tool. A workbook that looks clean on screen can carry a hidden sheet of workings, a very hidden salary sheet that does not even appear in Excel's Unhide menu, columns of cost prices someone hid before the meeting, comments with names and opinions in them, and formulas that point straight at all of the above. Sent to a customer or a supplier, every one of those travels with the file, and anyone can reveal them in seconds. This page, part of Protecting and Sharing Excel Workbooks, audits a workbook for hidden data with openpyxl and builds a clean, values-only copy to share instead.

Prerequisites

Bash

pip install openpyxl

q3_results.xlsx shows one sheet, Summary, with revenue and margin by region. Behind it are a hidden Workings sheet, a very hidden Salaries sheet, a hidden column of cost prices, a hidden draft note, a cell whose value is concealed with a ;;; format, a comment, a hidden defined name and internal document properties. It was last saved by a spreadsheet application, so its formulas have calculated values stored with them.

Step 1 — Audit the workbook

Collect everything that is present but not visible into one list of findings. Each check is short; together they cover what a reader could uncover:

Python

import re
from openpyxl import load_workbook

def audit(path):
    wb = load_workbook(path)
    found = []
    for ws in wb.worksheets:
        if ws.sheet_state != "visible":
            found.append(("sheet", ws.title, f"{ws.sheet_state}, {ws.max_row} rows"))
        for letter, dim in ws.column_dimensions.items():
            if dim.hidden and any(c.value is not None for c in ws[letter]):
                found.append(("hidden column", f"{ws.title}!{letter}", "contains data"))
        for idx, dim in ws.row_dimensions.items():
            if dim.hidden and any(c.value is not None for c in ws[idx]):
                found.append(("hidden row", f"{ws.title}!{idx}", "contains data"))
        for row in ws.iter_rows():
            for c in row:
                if c.comment:
                    found.append(("comment", f"{ws.title}!{c.coordinate}", f"by {c.comment.author}"))
                if c.number_format == ";;;" and c.value is not None:
                    found.append(("concealed value", f"{ws.title}!{c.coordinate}", "format ;;;"))
                if isinstance(c.value, str) and c.value.startswith("=") and "!" in c.value:
                    found.append(("cross-sheet formula", f"{ws.title}!{c.coordinate}", c.value))
    for name, dn in wb.defined_names.items():
        found.append(("defined name", name, f"{dn.attr_text}{' (hidden)' if dn.hidden else ''}"))
    if wb._external_links:
        found.append(("external links", str(len(wb._external_links)), "links to other files"))
    for field in ("creator", "lastModifiedBy", "title", "subject", "keywords", "description"):
        value = getattr(wb.properties, field)
        if value:
            found.append(("property", field, value))
    return found

for kind, where, detail in audit("q3_results.xlsx"):
    print(f"{kind:20} {where:14} {detail}")

The audit finds both hidden sheets — including the very hidden Salaries sheet, which Excel's Unhide dialog would not even list — the cost price column, the draft note, the concealed internal code, the comment, the two formulas that pull the margin rate from the hidden Workings sheet, the hidden name pointing at it, and the author and title properties. A formula such as =B2*Workings!B1 is itself a disclosure: it tells the reader that a Workings sheet exists and where the rate lives.

Step 2 — Build a clean copy with values only

Removing each finding from the original is error-prone, because it is easy to miss one. Build the shared copy by subtraction from a values-only load instead. data_only=True replaces every formula with the value last calculated, so nothing in the copy can refer to the sheets about to be deleted:

Python

from openpyxl.workbook.properties import CalcProperties

def clean_copy(src, dst, keep_sheets):
    wb = load_workbook(src, data_only=True)
    for ws in list(wb.worksheets):
        if ws.title not in keep_sheets or ws.sheet_state != "visible":
            wb.remove(ws)
    for ws in wb.worksheets:
        for idx in sorted((i for i, d in ws.row_dimensions.items() if d.hidden), reverse=True):
            ws.delete_rows(idx)
        for letter in sorted((l for l, d in ws.column_dimensions.items() if d.hidden), reverse=True):
            ws.delete_cols(ws[f"{letter}1"].column)
        for dim in list(ws.column_dimensions.values()) + list(ws.row_dimensions.values()):
            dim.hidden = False
        for row in ws.iter_rows():
            for c in row:
                c.comment = None
                if c.number_format == ";;;":
                    c.value = None
                    c.number_format = "General"
    for name in list(wb.defined_names):
        del wb.defined_names[name]
    wb._external_links = []
    wb.properties.creator = "Finance"
    wb.properties.lastModifiedBy = "Finance"
    wb.properties.title = "Q3 results"
    wb.properties.subject = wb.properties.keywords = wb.properties.description = None
    wb.save(dst)

clean_copy("q3_results.xlsx", "q3_results_shared.xlsx", keep_sheets={"Summary"})
shared = load_workbook("q3_results_shared.xlsx")
print(shared.sheetnames)
for row in shared["Summary"].iter_rows(values_only=True):
    print(row)

The copy has one sheet with the region, revenue and margin — the margin now a number, 8,676 for North, rather than a formula. Hidden rows and columns are deleted rather than unhidden, working from the bottom and the right so the positions of those still to delete do not shift. The concealed value is cleared, not just made visible; its "Internal code" heading stays, so delete that column as well if even the heading should not be seen. One detail is easy to miss: delete_rows and delete_cols shift the cells but not the row and column settings, so the hidden flag stays on column D and would now hide whatever moved into it — the audit in the next step catches exactly that if the flags are not cleared, which is why the function resets every hidden flag after deleting. Deleting a column with openpyxl also does not adjust formulas elsewhere, which is one more reason to work from values: after data_only=True there are no formulas left to break.

Step 3 — Audit the copy before it goes out

Run the same audit on the file you are about to send. The only acceptable findings are ones you chose to keep:

Python

remaining = audit("q3_results_shared.xlsx")
for kind, where, detail in remaining:
    print(f"{kind:20} {where:14} {detail}")
unexpected = [f for f in remaining if f[0] != "property"]
assert not unexpected, f"still contains: {unexpected}"
print("clean — only the properties set deliberately remain")

Only the neutral properties set in Step 2 remain. Make this check part of the job that produces the shared file, so a new hidden sheet or comment added to the source next quarter stops the send instead of going out.

What this does not cover

openpyxl sees most of a workbook but not all of it. Pivot tables keep a cache of their source data inside the file — a pivot on a summary sheet can carry every row of the hidden data it summarises — so remove pivots from a shared copy or rebuild the summary as plain values. Images can contain text or metadata of their own. Macro-enabled files carry VBA code that openpyxl discards unless keep_vba=True, and threaded comments from newer Excel versions are stored separately from the notes openpyxl reads. For a file going to an external party, run Excel's File → Info → Check for Issues → Inspect Document on the result as a final check, or share a PDF when the recipient only needs to read it; Exporting Excel Reports to PDF covers that route.

Common pitfalls

Treating hidden as private. Hidden and protected data is still in the file; remove it.
Missing very hidden sheets. Check sheet_state for both "hidden" and "veryHidden".
Leaving formulas in. A cross-sheet formula reveals structure and breaks when sheets are removed; share values.
Loading without cached values. data_only=True needs a file last saved by a spreadsheet application; a file written by openpyxl has no calculated values to keep.
Forgetting metadata. Authors, titles and comments carry names; reset them.
Pivot caches. They hold source data; remove pivots from shared copies.

Performance notes

The audit reads every cell once, which is quick for report-sized workbooks. For very large sheets, skip the cell loop on sheets you will remove anyway, and audit only the sheets you intend to share.

Conclusion

Audit a workbook before sharing it: hidden and very hidden sheets, hidden rows and columns with content, ;;; formats, comments, cross-sheet formulas, defined names, external links and document properties. Build the shared copy from a data_only=True load, keeping only visible sheets you choose, deleting hidden rows and columns, clearing comments and names and resetting properties. Audit the copy again, and treat pivot caches, images and macros as a final manual check.

Frequently asked questions

How do I find hidden sheets in an Excel file with Python? Load it with openpyxl and check each worksheet's sheet_state. "hidden" sheets can be unhidden from Excel's menu; "veryHidden" sheets do not appear in that menu at all and can only be revealed through the VBA editor or code — which makes them easy to forget about.

What hidden data can an Excel file contain? Hidden and very hidden sheets, hidden rows and columns, cells formatted with ;;; so their values do not show, comments and notes with author names, defined names, formulas that reveal where numbers come from, links to other files, pivot caches holding source data, and document properties such as the author.

How do I share only the values from an Excel workbook? Load it with data_only=True so formulas are replaced by their last calculated values, remove the sheets, rows, columns, comments and names you do not want to share, reset the document properties and save a new copy. Then audit that copy again before sending it.

Is protecting or hiding a sheet enough to keep data private? No. Hidden and protected sheets are still in the file, and anyone with basic tools can read them. Data that the recipient must not see has to be removed from the copy you send, not hidden in it.

Up one level: Protecting and Sharing Excel Workbooks — protection and sharing in general.
Hide Sheets, Rows and Columns with openpyxl — the hiding this page undoes.
Set Workbook Properties with Python — the metadata fields.
Find Formula Errors in a Workbook with Python — another audit pass worth running.

More on protection & sharing