Filter Excel Rows by Condition with pandas

Filtering is the first thing most scripts do after reading a sheet: only this region, only open orders, only amounts over a threshold, only last month. Excel's AutoFilter does it by clicking; pandas does it with a boolean mask — a column of True and False, one per row — and a handful of methods that build masks for common conditions. The syntax is short but has sharp edges: and instead of & raises an error, a missing pair of brackets silently changes the meaning, and a blank cell can crash a text search. This page, part of Cleaning Excel Data with pandas, covers the conditions you will actually write and ends with filtering a formatted workbook without losing its formatting.

Prerequisites

pip install pandas openpyxl

orders.xlsx has 40 orders from August and September 2026 with a date, region, customer, status, amount and a free-text note. Two amounts are missing and some notes are blank — the gaps that make filters misbehave. The sheet has a styled header row, which matters in the last step.

import pandas as pd

df = pd.read_excel("orders.xlsx", sheet_name="Orders")
print(df.shape)
print(df.head(3).to_string(index=False))

Step 1 — One condition: a boolean mask

Comparing a column with a value gives a Series of True/False. Put that mask inside df[...] to keep the rows where it is True:

is_open = df["Status"] == "Open"
print(is_open.head(5).tolist())
open_orders = df[is_open]
big = df[df["Amount"] > 1000]
print(len(open_orders), "open,", len(big), "over 1,000")

Rows with a missing amount are not in big: any comparison with NaN is False, so the two orders without an amount drop out of every numeric filter. That is usually right, but it means "over 1,000" plus "1,000 or less" does not add up to all rows — check df["Amount"].isna().sum() when the counts matter.

Step 2 — Several conditions: &, | and ~

Combine masks with & (and), | (or) and ~ (not). Each condition needs its own brackets:

north_open_big = df[(df["Region"] == "North") & (df["Status"] == "Open") & (df["Amount"] > 500)]
north_or_south = df[(df["Region"] == "North") | (df["Region"] == "South")]
not_cancelled = df[~(df["Status"] == "Cancelled")]
print(len(north_open_big), len(north_or_south), len(not_cancelled))

try:
    df[df["Region"] == "North" and df["Status"] == "Open"]
except ValueError as err:
    print("ValueError:", str(err)[:60])

Python's and and or need a single True or False, and a whole column is neither, so pandas raises "The truth value of a Series is ambiguous". The brackets matter because & binds more tightly than ==: without them, df["Region"] == "North" & df["Status"] == "Open" is evaluated as df["Region"] == ("North" & df["Status"]) == "Open", which either raises a type error or, with numeric columns, quietly computes something else. Brackets around every comparison make the intent unambiguous.

Step 3 — Lists, ranges and dates

For "any of these values", isin is shorter and faster than a chain of |. For ranges, between includes both ends by default:

key_accounts = df[df["Customer"].isin(["Arden Ltd", "Cobalt GmbH"])]
mid_size = df[df["Amount"].between(500, 1500)]
september = df[(df["Date"] >= "2026-09-01") & (df["Date"] < "2026-10-01")]
print(len(key_accounts), len(mid_size), len(september))
print(september["Date"].min().date(), "to", september["Date"].max().date())

Comparing a datetime column with a date string works because pandas converts the string. Use a half-open range — on or after the 1st, before the 1st of the next month — rather than <= "2026-09-30": if the column carries times, a timestamp like 30 September 14:00 is later than "2026-09-30" (midnight) and would be dropped. between has an inclusive= argument ("both", "left", "right", "neither") for the same reason.

Step 4 — Text conditions that survive blanks

str.contains finds a substring or a pattern. What it does with blank cells depends on the column's type. pandas 3 reads text into its new string dtype, where a blank simply does not contain anything and the mask gets False. In pandas 2, or in any column with the older object dtype — common when a column mixes text and numbers — blanks produce a missing value in the mask, and selecting with it fails:

print(df["Note"].dtype, df["Note"].str.contains("urgent").isna().sum(), "missing in the mask")

as_object = df["Note"].astype(object)
try:
    df[as_object.str.contains("urgent")]
except ValueError as err:
    print("ValueError:", err)

urgent = df[df["Note"].str.contains("urgent", case=False, na=False)]
print(len(urgent), "urgent:", urgent["Note"].unique().tolist())
arden = df[df["Customer"].str.lower().str.startswith("arden")]
print(arden["Customer"].unique().tolist())

Passing na=False makes the behaviour the same everywhere, so write it by habit — a script that works on your machine with pandas 3 should not break on a colleague's pandas 2 installation. na=False treats blanks as "does not contain", and case=False makes URGENT - call match too. str.startswith and str.endswith cover the other common text conditions; lower-casing first catches the inconsistent arden ltd (EU) spelling. Watch out for regular-expression characters: str.contains("(EU)") treats the brackets as a group, so pass regex=False for literal text.

Step 5 — query: filters as readable strings

DataFrame.query takes the whole condition as one string, which reads closer to how people describe filters — and uses and, or and not:

threshold = 750
result = df.query("Region in ['North', 'East'] and Status != 'Cancelled' and Amount > @threshold")
print(len(result))
print(result[["OrderID", "Region", "Status", "Amount"]].head().to_string(index=False))

@threshold refers to a Python variable. Column names with spaces need backticks inside the string: query("`Order Value` > 100"). query is convenient for filters that are typed by a person or stored as text; for filters built in code, masks are easier to compose and debug.

Step 6 — Filters from a configuration file

Report jobs often run the same filter with different values each month or for each recipient. Keep the conditions as data and turn them into one mask:

FILTERS = {
    "Status": {"in": ["Open", "Shipped"]},
    "Amount": {"min": 250},
    "Date": {"min": "2026-08-15", "max_before": "2026-09-15"},
}

def apply_filters(frame, spec):
    mask = pd.Series(True, index=frame.index)
    for col, rule in spec.items():
        if "in" in rule:
            mask &= frame[col].isin(rule["in"])
        if "min" in rule:
            mask &= frame[col] >= rule["min"]
        if "max_before" in rule:
            mask &= frame[col] < rule["max_before"]
    return frame[mask]

selected = apply_filters(df, FILTERS)
print(len(selected), "rows match the configured filter")

Starting from an all-True mask and narrowing it with &= makes each rule optional. The dictionary can come from a YAML or JSON file that non-developers edit, as described in Keep Excel Report Settings in a Config File.

Step 7 — Filter a formatted workbook in place

Writing selected.to_excel(...) produces a new sheet without the original formatting. When the workbook's look must survive — a styled header, number formats, column widths — delete the non-matching rows with openpyxl instead, working from the bottom up so row numbers do not shift under the loop:

from openpyxl import load_workbook

keep_ids = set(df.loc[df["Status"] == "Open", "OrderID"])
wb = load_workbook("orders.xlsx")
ws = wb["Orders"]
for r in range(ws.max_row, 1, -1):
    if ws.cell(r, 1).value not in keep_ids:
        ws.delete_rows(r)
wb.save("orders_open.xlsx")

check = load_workbook("orders_open.xlsx")["Orders"]
print(check.max_row - 1, "rows kept; header bold:", check["A1"].font.b, "; date format:", check["B2"].number_format)

The rule is decided in pandas, where conditions are easy, and applied in openpyxl, where formatting is kept. Deleting row by row is slow for tens of thousands of rows; for large sheets, write the filtered data into a copy of the template instead, as in Populate an Excel Template Without Losing Formatting. If the reader should be able to change the filter themselves, apply an AutoFilter instead of deleting anything — see Apply AutoFilter Criteria and Hide Rows.

Common pitfalls

• and/or instead of &/|. Raises "truth value of a Series is ambiguous".
• Missing brackets around comparisons. Operator precedence changes the meaning; bracket every condition.
• Blank cells in text filters. On object columns, and in pandas 2, blanks make the mask unusable. Pass na=False to str.contains, startswith and endswith so the code works on every version.
• Dates compared with an inclusive end. Use < next day rather than <= last day when the column may have times.
• Modifying a filtered slice. subset = df[mask] followed by subset["x"] = ... triggers a copy warning; call .copy() when you intend to change the subset.

Conclusion

Build a boolean mask for each condition — comparisons, isin, between, str.contains(..., na=False) — combine them with &, | and ~ with every condition in brackets, and select with df[mask]. Use query for filters written as text, a dictionary of rules for filters that change per run, and openpyxl row deletion when the filtered result must keep the workbook's formatting.

Frequently asked questions

How do I filter rows in pandas by multiple conditions? Combine boolean masks with & for and, | for or and ~ for not, with each condition in brackets: df(df"Region" == "North") & (df"Amount" > 500). Python's and/or keywords do not work on whole columns.

Why do I get "The truth value of a Series is ambiguous"? You used and, or or not between two conditions on columns. Replace them with &, | and ~, and put brackets around each comparison.

How do I filter rows where a column contains text? Use dfdf"col".str.contains("text", case=False, na=False). na=False makes blank cells count as not matching on every pandas version and column type, and regex=False treats the search text literally.

How do I filter an Excel file without losing its formatting? Decide which rows to keep with pandas, then open the workbook with openpyxl and delete the other rows from the bottom up with ws.delete_rows, and save. The header styles, number formats and column widths stay as they were.

Related

• Up one level: Cleaning Excel Data with pandas — the everyday clean-up toolkit.
• Remove Blank Rows from Excel with pandas — filtering out empty rows specifically.
• Split One Excel Sheet into Multiple Files by Value — one filter per value, one file each.
• UNIQUE, SORT and FILTER Equivalents in pandas — Excel's FILTER function translated.