Search Every Sheet and Workbook in a Folder for a Value

"Which file has invoice 1042 in it?" is a question that arrives by email, and the honest answer in many organisations is "open them one by one". A folder of monthly workbooks, each with several sheets, some with the invoice number typed as text and some as a number, one note mentioning it in a sentence, an archive using a different format entirely — that is exactly what a script should search. This page, part of Working with Multiple Excel Sheets in Python, builds a search that reports every workbook, sheet and cell where a value appears, and handles the details that make naive searches miss matches.

Prerequisites

pip install openpyxl pandas

The examples search a reports folder with three workbooks in two levels, an Excel lock file (~$january.xlsx) and a file that is not a real workbook.

Step 1 — Search one workbook, every sheet

Open the workbook in read-only mode — it streams rows instead of building every cell in memory — and walk every sheet. iter_rows(values_only=True) returns plain values; enumerate supplies row and column numbers so each match can be reported as a cell address:

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

def search_workbook(path, match):
    wb = load_workbook(path, read_only=True, data_only=True)
    hits = []
    try:
        for ws in wb.worksheets:
            for r, row in enumerate(ws.iter_rows(values_only=True), start=1):
                for c, value in enumerate(row, start=1):
                    if value is not None and match(value):
                        hits.append({"sheet": ws.title, "cell": f"{get_column_letter(c)}{r}", "value": value})
    finally:
        wb.close()
    return hits

print(search_workbook("reports/2026/january.xlsx", lambda v: v == 1042))

That finds the invoice in Invoices!A3, stored as the number 1042. It misses the sentence in the Notes sheet — the value there is text containing 1042, not the number — and, run on February's file, it would miss the invoice too, because February's export typed the number as text. The match function is where the real work is.

Step 2 — Normalise before comparing

Spreadsheets store the "same" value in several forms: the number 1042, the text "1042", the float 1042.0 from a calculation, "1042 " with a trailing space. Compare a normalised text form of both sides, and choose among exact, contains and pattern matching:

import re

def norm(value):
    if isinstance(value, float) and value.is_integer():
        value = int(value)
    return str(value).strip().casefold()

def exact(target):
    t = norm(target)
    return lambda v: norm(v) == t

def contains(target):
    t = norm(target)
    return lambda v: t in norm(v)

def pattern(regex):
    rx = re.compile(regex, re.IGNORECASE)
    return lambda v: bool(rx.search(str(v)))

for name, m in [("exact", exact(1042)), ("contains", contains("1042")), ("regex", pattern(r"\b1042\b"))]:
    print(name, [h["cell"] for h in search_workbook("reports/2026/january.xlsx", m)])

exact treats the number and the text as the same thing, and casefold makes "Contoso Ltd" match "contoso ltd". contains also finds the Notes sentence. The regular expression with word boundaries finds 1042 as a whole token — useful for references like INV-1042-B in the archive while ignoring 10420. Pick the mode per question; for IDs, exact is usually right, and for names, contains.

Step 3 — Walk the folder, skipping what is not a workbook

Now apply the search to every file under the folder. Two kinds of file need handling rather than crashing the run: Excel's lock files, which start with ~$ and exist while someone has the workbook open, and files that fail to load because they are damaged or not really workbooks:

from pathlib import Path
import pandas as pd

def search_folder(folder, match, pattern_glob="**/*.xlsx"):
    results, skipped = [], []
    for path in sorted(Path(folder).glob(pattern_glob)):
        if path.name.startswith("~$"):
            skipped.append((str(path), "lock file"))
            continue
        try:
            for hit in search_workbook(path, match):
                results.append({"file": str(path), **hit})
        except Exception as exc:
            skipped.append((str(path), f"{type(exc).__name__}: {exc}"))
    return pd.DataFrame(results, columns=["file", "sheet", "cell", "value"]), skipped

found, skipped = search_folder("reports", contains("1042"))
print(found.to_string(index=False))
print(skipped)

Four matches across three workbooks, and two skipped files reported with their reasons. Reporting skips matters: a search that silently ignores an unreadable file can say "not found" when the answer is in the file it could not open. **/*.xlsx descends into subfolders; add *.xlsm with a second glob if macro workbooks are in scope.

The same loop answers the bigger version of the question — "which of these fifty invoices appear anywhere?" — in a single pass. Build a set of normalised targets and make the match function a membership test, so every cell is compared with all targets at once instead of searching the folder fifty times:

targets = {norm(t) for t in [1042, 1077, 9999]}
found_many, _ = search_folder("reports", lambda v: norm(v) in targets)
print(found_many.assign(key=found_many["value"].map(norm)).groupby("key")["file"].nunique())

Invoices that never appear — 9999 here — are simply absent from the result, so compare the found keys with the target set to list the ones that were not found anywhere.

Step 4 — Decide between results and formulas

data_only=True searches what a reader sees: cached formula results as saved by the last application that calculated the file. Without it, formula cells are searched as their formula text, which answers a different question — "which workbooks refer to the Rates sheet?" or "who is still linking to the old budget file?":

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws["A1"] = "=VLOOKUP(1042,Invoices!A:C,3,FALSE)"
wb.save("reports/lookup.xlsx")

def search_formulas(path, match):
    wb = load_workbook(path, read_only=True)          # formulas, not results
    try:
        return [(ws.title, cell.coordinate, cell.value) for ws in wb.worksheets
                for row in ws.iter_rows() for cell in row
                if isinstance(cell.value, str) and cell.value.startswith("=") and match(cell.value)]
    finally:
        wb.close()

print(search_formulas("reports/lookup.xlsx", contains("Invoices!")))

Workbooks written by scripts and never opened in a spreadsheet application have no cached results, so a data_only search finds nothing in their formula cells. That is rarely a problem for values typed into sheets, but worth knowing when a search unexpectedly comes back empty.

Step 5 — Save the results where people can use them

A search result that will be forwarded is more useful as a workbook with clickable links than as console output. Write the DataFrame, then turn each file path into a hyperlink:

from openpyxl import load_workbook as open_book

found.to_excel("search_1042.xlsx", index=False)
wb = open_book("search_1042.xlsx")
ws = wb.active
for row in range(2, ws.max_row + 1):
    cell = ws.cell(row=row, column=1)
    cell.hyperlink = Path(cell.value).resolve().as_uri()
    cell.style = "Hyperlink"
wb.save("search_1042.xlsx")
print(ws.max_row - 1, "results written")

file:// links open the workbook for anyone who can reach the same path — right for a shared drive, useless for someone outside the network. Write Clickable Links from a DataFrame Column to Excel covers the link options.

Common pitfalls

• Comparing raw values. 1042 == "1042" is False; normalise both sides.
• Leaving read-only workbooks open. On Windows they stay locked until closed; the finally blocks close them.
• Ignoring skipped files. Report them, or a "not found" may be wrong.
• Searching formula text by accident. Without data_only=True you match formulas, not results.
• Legacy .xls files. openpyxl cannot open them; read them with pd.read_excel(path, sheet_name=None) and search the DataFrames, as described in Read .xls Files in Python with xlrd and pandas.

Performance notes

Read-only mode keeps memory flat and reads roughly a few hundred thousand cells per second, so a folder of a few hundred ordinary reports takes seconds. For thousands of files, the work is independent per file and parallelises well across processes — Process Multiple Excel Files in Parallel with Python shows the pattern — and the calamine engine through pd.read_excel(path, sheet_name=None, engine="calamine") reads values considerably faster than openpyxl when cell addresses are not needed.

Conclusion

Search every sheet of every workbook with openpyxl in read-only mode, compare a normalised text form of each value so numbers and text match, and choose exact, contains or pattern matching per question. Skip Excel's ~$ lock files, report files that fail to open instead of ignoring them, use data_only=True for what readers see and formula text for references, and hand the answer back as a workbook with links to the files.

Frequently asked questions

How do I search all sheets of an Excel file in Python? Open it with openpyxl in read-only mode, loop over wb.worksheets and iter_rows(values_only=True), and compare each value with what you are looking for. Record the sheet name and the row and column of every match.

Why does searching for 1042 miss some cells? Because the value is stored as a number in some cells and as text in others. Compare normalised text instead: convert both sides to strings, strip spaces, and turn whole-number floats like 1042.0 into 1042.

Does the search look inside formulas? With data_only=True it searches the cached results that were saved with the file. Without it, it searches the formula text. Choose based on whether you are looking for a value a reader sees or for a reference inside formulas.

What are the ~$ files in my folder? Lock files Excel creates while a workbook is open. They are not workbooks and fail to load, so skip any file whose name starts with ~$.

Related

• Up one level: Working with Multiple Excel Sheets in Python — reading and writing many sheets.
• Read All Sheets from an Excel File into DataFrames — when you want the data, not just the location.
• Combine Multiple Excel Files into One in Python — the folder loop for consolidation.
• Find and Replace Text in Excel with openpyxl — changing what the search finds.