Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Excel IF Formulas as pandas Conditional Columns

IF becomes np.where and nested IFs become np.select, with pd.cut for numeric bands. Includes the IFERROR translation and why apply is the slow way to do it.

A column of nested IFs is the most common thing in a mature spreadsheet and the one that translates most cleanly. pandas separates the two cases Excel bundles together: a single condition becomes np.where, and a ladder of them becomes np.select — with the useful property that the conditions stay readable however many there are. This guide is part of Excel Formula Equivalents in pandas.

Which conditional tool the formula maps onto A single condition becomes np.where, a ladder of conditions becomes np.select, and thresholds on one numeric column are better expressed as pd.cut with labelled bins. How many conditions? np.where condition, true, false one np.select first match wins several pd.cut thresholds stay data numeric bands the shape of the condition picks the tool

Prerequisites

Bash
pip install pandas numpy openpyxl
Python
import numpy as np
import pandas as pd

deals = pd.DataFrame({
    "Deal": ["D-1", "D-2", "D-3", "D-4", "D-5", "D-6"],
    "Revenue": [24500.0, 9800.0, 15320.0, 3010.0, np.nan, 41200.0],
    "Region": ["North", "South", "North", "West", "South", "North"],
    "Days_Open": [12, 91, 45, 7, 33, 128],
})

One condition: np.where

Python
# =IF(B2>10000, "Large", "Standard")
deals["Size"] = np.where(deals["Revenue"] > 10000, "Large", "Standard")

# =IF(D2>90, "Stale", "Active")
deals["Status"] = np.where(deals["Days_Open"] > 90, "Stale", "Active")
print(deals[["Deal", "Revenue", "Size", "Days_Open", "Status"]])

np.where(condition, if_true, if_false) reads in the same order as the Excel function and evaluates the whole column at once. Both branches can be columns rather than constants, which covers the formula that picks between two values:

Python
# =IF(C2="North", B2*1.1, B2)
deals["Adjusted"] = np.where(deals["Region"] == "North", deals["Revenue"] * 1.1, deals["Revenue"])

One difference from Excel is worth knowing: both branches are computed for every row before the selection happens. That is harmless for arithmetic and matters when a branch would raise — dividing by a column containing zeros, for instance — in which case guard the operation rather than relying on the condition to skip it.

Nested IFs: np.select

A nested IF against a list of conditions A nested IF becomes unreadable by its third level and requires re-nesting to add a band, while np.select keeps the conditions and outcomes as two aligned lists that can be printed and tested. nested IF unreadable at level 3 re-nest to add a band cannot be tested np.select two aligned lists add one entry printable and testable same order first match wins in both

A nested IF ladder is unreadable by its third level and unmaintainable by its fifth. np.select takes the conditions as a list and the outcomes as another, in the same order, and the first match wins.

Python
# =IFS(B2>40000,"A", B2>20000,"B", B2>10000,"C", TRUE,"D")
conditions = [
    deals["Revenue"] > 40000,
    deals["Revenue"] > 20000,
    deals["Revenue"] > 10000,
]
grades = ["A", "B", "C"]
deals["Grade"] = np.select(conditions, grades, default="D")
print(deals[["Deal", "Revenue", "Grade"]])

Order matters exactly as it does in a nested IF: put the narrowest condition first, or a broader one above it will claim the rows. The advantage over the formula is that the ladder is a list you can print, count and test, and adding a band is one entry in two lists rather than a re-nesting exercise in a formula bar.

Conditions can span columns, which nested IFs make painful:

Python
deals["Flag"] = np.select(
    [
        deals["Revenue"].isna(),
        (deals["Days_Open"] > 90) & (deals["Revenue"] > 20000),
        deals["Days_Open"] > 90,
    ],
    ["Missing revenue", "Large and stale", "Stale"],
    default="OK",
)

Bands: pd.cut instead of a ladder

When every condition is a numeric threshold on the same column, pd.cut says so directly and keeps the boundaries as data.

Python
deals["Band"] = pd.cut(
    deals["Revenue"],
    bins=[0, 10000, 20000, 40000, float("inf")],
    labels=["D", "C", "B", "A"],
    right=False,
)
print(deals[["Deal", "Revenue", "Band"]])

right=False makes each interval half-open — [0, 10000) — which removes the question of which band a value exactly on a boundary falls into. That question is the source of most disagreements between two implementations of the same banding, and stating the answer in the call settles it.

pd.cut also returns a categorical with an order, so sorting and grouping by band behave sensibly rather than alphabetically. NaN stays NaN rather than falling into the lowest band, which is usually correct and always explicit.

The IFERROR case

Excel wraps a formula in IFERROR because a division or a lookup can produce an error value that propagates. pandas has no error values — it has NaN, and operations that would error either raise or produce NaN depending on how you ask.

Python
# =IFERROR(B2/D2, 0)
deals["Per_Day"] = (deals["Revenue"] / deals["Days_Open"]).fillna(0)

# =IFERROR(VALUE(B2), 0) — text that should be numeric
raw = pd.Series(["1200", "n/a", "980.5", ""])
numbers = pd.to_numeric(raw, errors="coerce").fillna(0)
print(numbers.tolist())

errors="coerce" is the closest thing to IFERROR in the library, and it is better than the formula because the intermediate NaN is visible. Counting them before filling turns "the report shows zero" into "eighteen rows had unparseable amounts", which is a materially more useful thing to know.

Conditional columns from a lookup

An IF ladder that maps exact values — not ranges — is a dictionary in disguise, and writing it as one makes the mapping editable without touching the logic.

Python
owners = {"North": "Ana", "South": "Ben", "West": "Dev"}
deals["Owner"] = deals["Region"].map(owners).fillna("Unassigned")

Keeping the mapping in a dictionary — or better, in a small reference sheet read at run time — means a new region is a data change rather than a code change. That distinction is what stops a reporting script from needing a developer every quarter, and it applies just as much to the thresholds in pd.cut as to the labels here.

Where NaN falls in a condition

Missing values deserve their own treatment because they behave differently from anything in Excel. Every comparison against NaN is False — NaN > 10000 is False, and so is NaN <= 10000 — which means a row with a missing value quietly lands in whichever branch is the fallback.

Python
import numpy as np

# Both comparisons are False for the NaN row, so it becomes "Standard"
deals["Size"] = np.where(deals["Revenue"] > 10000, "Large", "Standard")

# Better: say what a missing value means, first
deals["Size"] = np.select(
    [deals["Revenue"].isna(), deals["Revenue"] > 10000],
    ["Unknown", "Large"],
    default="Standard",
)

Excel behaves differently here in a way that hides the problem: a blank cell is treated as zero by a numeric comparison, so =IF(B2>10000,...) puts blanks in the false branch too, but a blank in a SUM is skipped entirely. The two conventions coexist in the same workbook, and reproducing them exactly is rarely what anybody wants once it is pointed out.

Making the missing case explicit as the first condition costs one line and turns a silent misclassification into a visible category. It also makes the count available — how many rows are Unknown is a number worth putting in the report, and the reasoning behind it is in Find and Report Missing Values in an Excel File.

Keeping thresholds out of the code

An IF ladder written into a script has the same maintenance problem as one written into a formula: the numbers are buried where only the author can find them. Lifting the thresholds into a small table — a dictionary, a config file, or a reference sheet in the workbook — makes them reviewable.

Python
import pandas as pd

BANDS = [
    {"floor": 40000, "label": "A"},
    {"floor": 20000, "label": "B"},
    {"floor": 10000, "label": "C"},
    {"floor": 0, "label": "D"},
]

bands = pd.DataFrame(BANDS).sort_values("floor")
deals["Grade"] = pd.cut(
    deals["Revenue"],
    bins=list(bands["floor"]) + [float("inf")],
    labels=list(bands["label"]),
    right=False,
)

Reading BANDS from a sheet in the source workbook instead of a literal makes the grading editable by the people who own the definition, which is usually the finance team rather than whoever maintains the script. That pattern — logic in code, parameters in data — is the same one recommended in Keep Excel Report Settings in a Config File, and it is what keeps a quarterly threshold change from being a deployment.

Common pitfalls

SymptomCauseFix
ValueError: truth value of a Series is ambiguousA Python if used on a columnUse np.where or np.select
A band claims too many rowsConditions ordered widest-firstPut the narrowest condition first in np.select
NaN rows get an unexpected labelComparisons with NaN are False, so default catches themTest isna() explicitly as the first condition
np.select raises about lengthsConditions and choices lists differ in lengthThey must match element for element
Numbers on a boundary fall in the wrong bandInterval closure not statedPass right=False (or True) to pd.cut deliberately
A branch raises even though its condition is FalseBoth branches are evaluatedCompute safely first, then select

Performance and scale

Applying a conditional to one million rows A vectorised np.where finishes in tens of milliseconds while an apply with a lambda pays a Python function call per row and takes seconds for the same result. apply with a lambda a call per row np.select vectorised np.where vectorised relative cost the widest gap in the whole translation

The gap between a vectorised conditional and a row loop is the largest in this whole topic. np.where over a million rows runs in the low tens of milliseconds; the same logic in a Python for loop or an apply with a lambda takes seconds, because every row pays the cost of a Python function call.

Python
import numpy as np

# Vectorised: one operation over the column
deals["Size"] = np.where(deals["Revenue"] > 10000, "Large", "Standard")

# Avoid: one Python call per row
deals["Size"] = deals["Revenue"].apply(lambda v: "Large" if v > 10000 else "Standard")

apply is not forbidden — it is the right tool when the logic genuinely cannot be expressed as column operations — but a conditional on a numeric comparison always can be. When a piece of logic resists vectorising, it is usually worth asking whether it is really row-wise or whether it is a group operation in disguise, which transform handles at column speed.

Conclusion

IF becomes np.where, nested IFs become np.select with the narrowest condition first, and a ladder of numeric thresholds is better expressed as pd.cut with explicit interval closure. IFERROR has no direct equivalent because there are no error values — use errors="coerce" and then decide what the NaN means, which is usually more informative than the blank the formula would have shown.

Frequently asked questions

Should I use np.where or a Python if statement? np.where, always, when the condition applies to a column. A Python if evaluates a single truth value and raises on a Series; writing a loop with if works but is hundreds of times slower than the vectorised form.

How many conditions can np.select take? As many as you like — it takes a list of conditions and a matching list of choices. The first condition that is True for a row wins, which is exactly how a nested IF ladder behaves.

What is the pandas equivalent of IFERROR? There is no single function because errors do not propagate as values. Guard the operation instead: use errors='coerce' on conversions to get NaN, then fillna to supply the default IFERROR would have shown.

Can I use a lookup table instead of a long IF ladder? Usually yes, and it is the better design. pd.cut maps numeric ranges to labels, and map handles a dictionary of exact values. Both keep the thresholds as data rather than burying them in code.