Skip Hidden and Filtered Rows When Reading Excel

A colleague filters a sheet down to the North and West regions, saves it, and sends it over with "the numbers are in the attached". The total they saw on screen was 650. The total your script computes from the same file is 1,170, because pd.read_excel() ignores filters, hidden rows, collapsed groups and hidden columns — it reads every cell that holds a value. Whether that is a bug depends on what the file is meant to say. This guide from Extracting Data from Messy Excel Sheets shows how to see what the user saw, and how to decide which version you want.

Prerequisites

pip install openpyxl pandas

This builds orders.xlsx with an autofilter, a manually hidden row, a hidden column and a hidden sheet:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Orders"
ws.append(["Order", "Region", "Amount", "Internal note"])
for order, region, amount in [(1001, "North", 250), (1002, "South", 120), (1003, "North", 90),
                              (1004, "West", 400), (1005, "South", 310)]:
    ws.append([order, region, amount, "check"])

ws.auto_filter.ref = "A1:D6"
ws.auto_filter.add_filter_column(1, ["North", "West"])
ws.row_dimensions[3].hidden = True      # filtered out: South
ws.row_dimensions[6].hidden = True      # filtered out: South
ws.row_dimensions[4].hidden = True      # hidden by hand
ws.column_dimensions["D"].hidden = True
wb.create_sheet("Archive").sheet_state = "hidden"
wb.save("orders.xlsx")

Note the three row_dimensions lines. An autofilter in the file is only a description of the filter; Excel records which rows it hid by marking each one hidden, and openpyxl does not apply filters itself. A workbook saved from Excel after filtering already has those flags set, which is what you will read.

Step 1 — Find the hidden rows

Every row that is hidden — by a filter, by hand, or by a collapsed outline group — carries hidden=True in ws.row_dimensions. Rows with default formatting have no entry at all, so iterate over the entries that exist rather than over every row number.

from openpyxl import load_workbook

wb = load_workbook("orders.xlsx")
ws = wb["Orders"]

hidden_rows = {idx for idx, dim in ws.row_dimensions.items() if dim.hidden}
print(sorted(hidden_rows))          # [3, 4, 6]

print(ws.auto_filter.ref)           # A1:D6
for column in ws.auto_filter.filterColumn:
    print(column.colId, column.filters.filter)   # 1 ['North', 'West']

The filter definition is worth logging even when you only use the hidden flags. It tells a human reading your job's output why rows were excluded, which matters the first time somebody asks where the South orders went.

Step 2 — Build a DataFrame from visible rows only

The safest way to keep rows aligned with their flags is to build the DataFrame from openpyxl directly, skipping hidden rows as you go. Row numbers then never have to be translated between Excel's one-based numbering and a pandas index.

import pandas as pd

def read_visible(ws, header_row: int = 1, skip_hidden_columns: bool = True) -> pd.DataFrame:
    hidden_r = {i for i, d in ws.row_dimensions.items() if d.hidden}
    hidden_c = set()
    for dim in ws.column_dimensions.values():
        if dim.hidden:
            hidden_c.update(range(dim.min, dim.max + 1))    # grouped columns share one entry

    keep = [c for c in range(1, ws.max_column + 1)
            if not (skip_hidden_columns and c in hidden_c)]
    header = [ws.cell(header_row, c).value for c in keep]
    rows = [
        [ws.cell(r, c).value for c in keep]
        for r in range(header_row + 1, ws.max_row + 1)
        if r not in hidden_r
    ]
    return pd.DataFrame(rows, columns=header)

visible = read_visible(ws)
print(visible)
#    Order Region  Amount
# 0   1001  North     250
# 1   1004   West     400
print(visible["Amount"].sum())       # 650 — the number the colleague saw

The column loop has one subtlety worth the comment. When columns are hidden as a group, openpyxl stores a single dimension entry keyed by the first letter with min and max spanning the whole range, so checking only column_dimensions["C"] would wrongly report column C as visible. Expanding min..max handles both single and grouped columns.

Step 3 — Or filter a pandas read by row number

If the rest of the pipeline already uses read_excel — for its dtype handling or its engine choice — keep it and drop hidden rows afterwards. The mapping from DataFrame position to Excel row is position + header_row + 1, which holds as long as you did not pass skiprows.

df = pd.read_excel("orders.xlsx", sheet_name="Orders")
excel_row = pd.RangeIndex(len(df)) + 2          # header in row 1, data from row 2
visible_pd = df[~excel_row.isin(hidden_rows)].reset_index(drop=True)
print(visible_pd)

This is shorter but more fragile. Any option that changes which rows pandas reads shifts the mapping, so prefer Step 2 whenever the sheet has a title block or skipped rows above the data.

Step 4 — Skip hidden and very hidden sheets

Sheets can be hidden too, and pd.read_excel(sheet_name=None) returns them alongside the visible ones. openpyxl exposes the state on each worksheet: "visible", "hidden" (unhide-able from Excel's menu) or "veryHidden" (only reachable through VBA, and usually holding lookup tables or configuration).

visible_sheets = [s.title for s in wb.worksheets if s.sheet_state == "visible"]
print(visible_sheets)                 # ['Orders']

frames = pd.read_excel("orders.xlsx", sheet_name=visible_sheets)
print(list(frames))

A very hidden sheet is a strong signal that the workbook author did not intend it to be data. It is still worth listing in your job's log, because it is also exactly where a lookup table that your numbers silently depend on tends to live.

Step 5 — Decide which version you actually want

Visible-only is not automatically right. A filter is a view, and a person sending "the attached" may have left a filter on by accident. Rows hidden by hand are sometimes corrections and sometimes just clutter. The honest approach is to make the choice explicit and record it.

def load_orders(path: str, visible_only: bool) -> pd.DataFrame:
    book = load_workbook(path)
    sheet = book["Orders"]
    frame = read_visible(sheet) if visible_only else pd.read_excel(path, sheet_name="Orders")
    hidden = sum(1 for d in sheet.row_dimensions.values() if d.hidden)
    print(f"{path}: {len(frame)} rows read, {hidden} hidden rows "
          f"{'skipped' if visible_only else 'included'}")
    return frame

load_orders("orders.xlsx", visible_only=True)
load_orders("orders.xlsx", visible_only=False)

A good rule of thumb: treat visible-only as correct for files that are reports a person prepared, and all rows as correct for files that are extracts a system produced. When in doubt, log both totals.

Common pitfalls

The last row of that table is more common than it should be. Dragging a row border until it disappears does not hide the row; it sets its height to zero. Excel shows nothing, and the hidden flag stays false.

Performance and scale notes

Reading row dimensions requires a full load_workbook, and building the frame cell by cell is slower than a pandas read. For a few thousand rows neither matters. For a large sheet, collect hidden_rows with openpyxl once and read the values with a fast engine as in Step 3, or use Speed Up openpyxl with read_only Mode for the values and a separate normal load for the flags.

The two-read approach does not make the flag scan cheaper — a normal openpyxl load still builds every cell and style object — but it keeps that cost out of every later step, which can then use the fast engine and vectorised pandas operations instead of Python loops over cells. If you process the same workbook repeatedly — a daily extract that only changes its values — cache the set of hidden rows alongside the file's modification time and skip the flag scan whenever the file has not changed. And when the sheet is truly large, remember that filtered views are usually small: filtering the pandas frame by position is a vectorised isin, so the cost after both reads is negligible even at hundreds of thousands of rows.

Conclusion

pandas reads stored cells, not the view a person saw. openpyxl exposes that view through row_dimensions[n].hidden, column_dimensions (with grouped ranges expanded), auto_filter and sheet_state. Build the frame from visible rows when the file is a prepared report, keep every row when it is a system extract, and log which choice was made so that a mismatched total is explained rather than argued about.

Frequently asked questions

Does pandas have an option to skip hidden rows? No. read_excel has no visibility option for any engine. You need openpyxl to read the hidden flags and then drop those rows yourself.

Will openpyxl apply the autofilter criteria for me? No. openpyxl reads and writes the filter definition but does not evaluate it. The rows are hidden only if the application that saved the file applied the filter, which Excel always does.

How do I read hidden rows from a very hidden sheet? The same way as any sheet: index the loaded workbook by sheet name, which works regardless of sheet_state, and pandas can read it by name too. Visibility never blocks access from code.

Can I unhide everything and save a clean copy? Yes. Set hidden to False on every row and column dimension, set sheet_state to "visible" on every worksheet, clear ws.auto_filter.ref, and save to a new file.

Related

• Up one level: Extracting Data from Messy Excel Sheets — the rest of the techniques for human-shaped workbooks.
• Read Cell Colours and Font Styles as Data with openpyxl — more meaning stored outside the values.
• Hide Sheets, Rows and Columns with openpyxl — the write side of the same flags.
• Add an Autofilter to an Excel Sheet with openpyxl — how filter definitions are stored.
• Group and Outline Rows in Excel with openpyxl — collapsed groups and outline levels.