Formulas & Excel logic
Excel functions in pandas
SUMIF, COUNTIF, IF, INDEX MATCH, LEFT/MID and friends, translated function by function.
Start with the full guides
The long-form treatment of excel functions in pandas, with the reasoning the shorter pages assume.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toApproximate-Match VLOOKUP for Tiered Rates in pandasLook up commission rates, discount bands or tax brackets the way VLOOKUP(value, table, 2, TRUE) does — the largest threshold not above the value. Use merge_asof, searchsorted or pd.cut, handle values below the first tier, keep the tier table sorted, use different tiers per region, and calculate progressive (marginal) amounts correctly.
- How-toAVERAGEIFS, MAXIFS and MINIFS Equivalents in pandasTranslate Excel's AVERAGEIFS, MAXIFS and MINIFS to pandas — boolean masks for one-off results, groupby for tables, transform for per-row columns — and reproduce Excel's handling of blanks, text, wildcards, date ranges and the empty case where MAXIFS returns 0.
- How-toCHOOSE, SWITCH and IFS Equivalents in pandasTranslate Excel's multi-way choice formulas into vectorised pandas: CHOOSE by position with a lookup list, SWITCH by value with Series.map and a default, IFS with ordered conditions using numpy.select, ranges with pandas.cut, what happens when nothing matches, and a check that each version gives the same answers as the spreadsheet formulas.
- How-toCORREL and LINEST Equivalents in PythonReproduce Excel's CORREL, PEARSON, RSQ and LINEST in pandas and NumPy: one correlation or a whole matrix, simple and multiple regression, the full five-row LINEST statistics block with standard errors, R², F and sums of squares, LINEST's reversed coefficient order, and a check against the spreadsheet's own result.
- How-toCOUNTIF and COUNTIFS Equivalent in pandasCount matching rows by summing a boolean mask, count blanks and distinct values in one call, and replace a copied-down COUNTIF with groupby().transform('size').
- How-toEOMONTH and EDATE Equivalents in pandasReproduce Excel's EDATE and EOMONTH in pandas for whole columns — month arithmetic that clamps 31 January to 28 February, month ends that do not depend on whether a date is already a month end, a different number of months per row, first-of-month dates and the time-of-day that Excel drops.
- How-toEXACT and Case-Sensitive Comparisons in pandasExcel compares text without regard to case — "AB-100"="ab-100" is TRUE, and COUNTIF, MATCH, XLOOKUP and UNIQUE all ignore case — while pandas compares exactly. Reproduce both behaviours: EXACT as plain equality, Excel-style matching with casefold, COUNTIF with wildcards, first-match lookups and distinct counts, and see why a migrated report counts six products where the spreadsheet counted three.
- How-toExcel IF Formulas as pandas Conditional ColumnsIF 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.
- How-toExcel TEXT Function Equivalent in pandasTurn numbers and dates into formatted text — "1,234.50", "12.3%", "00042", "Mar 2026", "(1,234.50)" — for labels, file names and email bodies, the way Excel's TEXT does. Map TEXT format codes to Python format specs and strftime, and fix the rounding difference that makes "1,234.5" come out as "1,234" instead of "1,235".
- How-toExcel Text Functions LEFT, RIGHT, MID and CONCAT in pandasSlicing replaces LEFT, RIGHT and MID; + and str.cat replace CONCATENATE and TEXTJOIN. Plus split, extract and the regex work that has no formula equivalent.
- How-toFIND, SEARCH and SUBSTITUTE Equivalents in pandasTranslate Excel's FIND, SEARCH, SUBSTITUTE and REPLACE to pandas string methods: positions that start at 0 instead of 1, -1 instead of #VALUE!, case-insensitive search, Excel wildcards, replacing only the second occurrence, and the MID(FIND()) pattern for text between delimiters.
- How-toFORECAST, TREND, SLOPE and RSQ Equivalents in PythonReproduce Excel's linear trend functions — SLOPE, INTERCEPT, RSQ, CORREL, STEYX, TREND and FORECAST.LINEAR — with NumPy, matching the spreadsheet to many decimal places. Fit a trend per product, use dates as the x-axis, project the next periods, and know where a straight line stops being a forecast.
- How-toFV, PV, RATE and NPER Equivalents in PythonReproduce Excel's time-value-of-money functions in Python: FV for a savings plan, PV for what a payment stream is worth, NPER for how long a loan takes, and RATE for the interest rate implied by a deal — with Excel's sign convention, the type argument for payments at the start of a period, zero-rate cases, vectorised versions for whole DataFrames, and a LibreOffice check that the numbers match.
- How-toGoal Seek in Python Instead of ExcelReplace Excel's Goal Seek with scipy.optimize: find the price that hits a profit target, the interest rate behind a loan payment, a break-even value for every row of a DataFrame, and even goal-seek an Excel model itself by recalculating it in LibreOffice — with brackets, tolerances and failure checks.
- How-toExcel GROUPBY and PIVOTBY Function Equivalents in pandasExcel 365's GROUPBY and PIVOTBY functions summarise a table with one formula. Translate them to pandas: groupby with a grand total row, sorting by the value column, filtering rows before grouping, several aggregations at once, subtotals for two levels, pivot_table with row and column totals, and PERCENTOF shares — plus why a workbook that relies on them will not calculate outside Excel 365.
- How-toIFERROR, IFNA, ISNA and ISNUMBER Equivalents in pandasIn pandas, a division by zero is inf, a failed lookup is NaN and bad text fails to_numeric — there are no error cells to catch. Translate IFERROR, IFNA, ISNA, ISBLANK, ISNUMBER and ISTEXT into the pandas operations that match each case, and keep IFERROR's habit of hiding real problems out of your pipeline.
- How-toINDEX MATCH Equivalent in pandasINDEX/MATCH becomes map for one column and merge for several. Handle composite keys without a helper column, report unmatched keys, and use merge_asof for banded lookups.
- How-toINDIRECT and OFFSET Equivalents in pandasTranslate the jobs Excel workbooks do with INDIRECT and OFFSET into plain pandas: pick a sheet or column whose name is stored in a cell, sum a range that grows with the data, take the last N periods, look a fixed number of rows up or across, and build a summary across monthly sheets — with each formula checked against a spreadsheet's own result.
- How-toIPMT, PPMT, EFFECT and NOMINAL Equivalents in PythonSplit loan payments into interest and principal and convert between nominal and effective rates in Python, matching Excel: PMT, IPMT, PPMT and CUMIPMT/CUMPRINC from first principles, Excel's sign convention, payments at the start or end of the period, EFFECT and NOMINAL for compounding frequencies, a vectorised schedule in pandas, and a check against the spreadsheet functions.
- How-toMatch Excel Rounding in pandaspandas and Python round halves to the nearest even number, while Excel's ROUND rounds them away from zero — so 0.125 becomes 0.12 in pandas and 0.13 in Excel. Reproduce ROUND, ROUNDUP, ROUNDDOWN and MROUND exactly, and fix totals that differ by a penny.
- How-toMEDIAN, MODE and QUARTILE Equivalents in pandasMatch Excel's MEDIAN, MODE.SNGL, MODE.MULT, QUARTILE.INC, QUARTILE.EXC, PERCENTILE.INC and PERCENTILE.EXC in pandas: which quantile method equals which Excel function, why pandas' mode differs from MODE.SNGL when there is a tie, how Excel ignores text in ranges, per-group medians and quartiles, and a spreadsheet cross-check.
- How-toPMT, NPV, IRR and XIRR Excel Functions in PythonReproduce Excel's financial functions in Python with numpy-financial: loan payments with PMT, FV, NPER and RATE, project values with NPV and IRR, and dated cash flows with XNPV and XIRR. Every result here matches the spreadsheet — including the NPV difference that catches almost everyone.
- How-toRANK and PERCENTILE Formulas in pandasMatch RANK.EQ with method='min' and ascending=False, rank within groups in one call, and replace LARGE, PERCENTILE and QUARTILE with nlargest, quantile and qcut.
- How-toREGEXTEST, REGEXEXTRACT and REGEXREPLACE Equivalents in pandasExcel's new regular-expression functions map neatly onto pandas' string methods: REGEXTEST to str.contains, REGEXEXTRACT's three return modes to str.extract, str.findall and capture groups, and REGEXREPLACE to str.replace — with the differences that bite: $1 versus \1 in replacements, the case-sensitivity argument, replacing only the nth match, #N/A versus NaN, and patterns that behave differently in Python.
- How-toReplace Excel Solver with PythonMove Solver models out of a dialog box and into a script: read the decision data from a workbook, solve linear programmes with scipy.optimize.linprog, whole-number problems with milp, report which constraints bind and what an extra unit of each resource is worth, and write the plan back to Excel.
- How-toROUND, ROUNDUP, MROUND and CEILING Equivalents in PythonReproduce Excel's rounding functions exactly in Python and pandas: why round(2.5) gives 2 and pandas rounds halves to even while Excel's ROUND rounds them away from zero, how floating point turns 2.675 into 2.67, and correct equivalents for ROUND, ROUNDUP, ROUNDDOWN, MROUND, CEILING and FLOOR — including negative digits and negative numbers — checked against a recalculated workbook.
- How-toRunning Totals and Year-Over-Year Growth in pandascumsum for running totals, shift and pct_change for period comparisons, rolling for moving averages — and why a year-over-year join beats shift when months go missing.
- How-toSCAN, REDUCE, MAP and BYROW Equivalents in pandasExcel's LAMBDA helper functions — SCAN for running results, REDUCE for a single accumulated value, MAP for element-by-element calculations, BYROW and BYCOL for per-row and per-column results — translated to pandas and the Python standard library: when a vectorised cumsum, cumprod or clip does the job, when only an itertools.accumulate loop reproduces SCAN (a balance that cannot go below zero), and how to keep the logic testable.
- How-toSEQUENCE and RANDARRAY Equivalents in PythonGenerate the number grids, date series, random numbers and shuffled lists that Excel's SEQUENCE, RANDARRAY, RANDBETWEEN and SORTBY formulas produce, with NumPy and pandas: row-major sequences with start and step, monthly and business-day date series, seeded random arrays that are reproducible unlike Excel's, random samples without repeats, and writing the results to a sheet as values.
- How-toSLN, DB and DDB Depreciation Functions in PythonReproduce Excel's depreciation functions exactly in Python: SLN straight line, SYD sum-of-years digits, DB fixed-declining balance with its three-decimal rate and partial first year, DDB double-declining balance that never switches to straight line, and VDB with its switch — each implemented, laid out as a schedule with pandas, and checked period by period against the spreadsheet functions.
- How-toSTDEV.S, STDEV.P, VAR.S and VAR.P in pandas and NumPypandas .std() and NumPy std() give different answers for the same numbers — one matches Excel's STDEV.S, the other STDEV.P. Match each Excel function exactly with ddof, choose between sample and population deliberately, handle single-value groups, and compute per-group spread, coefficients of variation and z-scores.
- How-toSUBTOTAL and AGGREGATE Equivalents in pandasReproduce Excel's SUBTOTAL and AGGREGATE in pandas: totals that skip the subtotal rows already in a sheet, the 1–11 versus 101–111 function numbers that include or ignore hidden rows, and AGGREGATE's option to ignore error values — reading which rows are subtotals and which are hidden with openpyxl, and checking every result against LibreOffice.
- How-toSUMIF and SUMIFS Equivalent in pandasTranslate conditional totals into pandas: boolean masks, several conditions joined with &, wildcards as string methods, and transform for a SUMIF copied down a column.
- How-toSUMPRODUCT and Weighted Averages in pandasTranslate Excel's SUMPRODUCT to pandas — total value from quantity times price, conditional sums with boolean arrays, and weighted averages such as average price weighted by units. Covers weighted averages per group, missing weights, and why the plain mean of averages gives the wrong answer.
- How-toTEXTBEFORE, TEXTAFTER and TEXTSPLIT Equivalents in pandasReproduce Excel's TEXTBEFORE, TEXTAFTER and TEXTSPLIT in pandas: text before or after the first, nth or last delimiter, case-insensitive matching, the crucial difference when the delimiter is missing (Excel returns #N/A, str.partition returns the whole text), splitting into columns and into rows, and a check of every case against the spreadsheet functions.
- How-toTEXTJOIN Equivalent in pandas (Join Values per Group)Combine many rows into one cell — every product a customer bought, "North, South, East" — the way Excel's TEXTJOIN does. Join per group with groupby and str.join, skip blanks like ignore_empty, convert numbers, de-duplicate and sort within each group, respect Excel's cell length limit, and split the result back into rows.
- How-toUNIQUE, SORT and FILTER Equivalents in pandasReproduce Excel's dynamic array functions in pandas: UNIQUE with drop_duplicates, "values that appear exactly once", SORT and SORTBY with sort_values, FILTER with boolean masks and an if_empty fallback, and the chained SORT(UNIQUE(FILTER())) pattern — plus the case-sensitive sort that puts "Banana" before "apple".
- How-toVSTACK, HSTACK, TAKE and CHOOSECOLS Equivalents in pandasTranslate Excel's array-shaping functions — VSTACK, HSTACK, TAKE, DROP, CHOOSECOLS, CHOOSEROWS, TOCOL and WRAPROWS — into pandas and NumPy, including the one difference that changes results: Excel stacks by position, pandas by column name and index. Each mapping is checked against the spreadsheet's own result.
- How-toXIRR and XNPV Equivalents in PythonReproduce Excel's XNPV and XIRR for cash flows on irregular dates: the 365-day formula behind XNPV, a root-finder for XIRR that matches Excel to the last digit, why NPV and IRR give a different (wrong) answer for uneven dates, XIRR per investment with groupby, cases with no or several solutions, and a LibreOffice cross-check.
- How-toXLOOKUP Equivalent in pandasReproduce every XLOOKUP argument in pandas — exact lookups with a default for missing keys, Excel's case-insensitive matching, first or last match, next smaller or next larger item, wildcards, and returning several columns at once — with a reusable xlookup function.
- How-toXLOOKUP with Multiple Criteria in pandasReproduce Excel lookups that match on two or more columns — XLOOKUP(1, (A=x)*(B=y), C) and its concatenated-key cousins — in pandas: an exact match on several keys with merge, a not-found default, guarding against duplicate keys, tiered "next smaller" matches by quantity with merge_asof, last-match lookups, and a check against the same XLOOKUP formulas recalculated in a workbook.
- How-toYEARFRAC and DAYS360 Equivalents in PythonReproduce Excel's YEARFRAC with all five day-count bases and DAYS360 with the US and European methods in Python: 30/360 rules for month ends and February, actual/360, actual/365, actual/actual with its leap-year logic, a vectorised version for interest accrual tables in pandas, and a check against spreadsheet results for the awkward dates.
- How-toThe VLOOKUP Equivalent in pandas for Excel Filesmerge is the real answer, map is the fast one: translating a VLOOKUP into pandas, keeping the unmatched rows visible, handling duplicate lookup keys, and the approximate-match case VLOOKUP's fourth argument hides.
- How-toExtract Numbers and Codes from Text with pandasPull order numbers, SKUs, quantities and amounts out of free-text cells like "Re: PO-10442, 3 x SKU AB-1200 @ $12.50". Use str.extract with named groups for one match per cell, extractall for many, fullmatch to validate codes, and a report of the rows where nothing matched.
- How-toSplit Full Names into First and Last Names with pandasA single "Name" column holds "Ada Lovelace", "Lovelace, Ada", "Dr. Grace Hopper", "Ludwig van Beethoven" and "Smith Jr., John". Split it into first and last names with rules for commas, titles, suffixes and surname particles, keep the original, and flag the names no rule should guess about.
- How-toBin Values into Ranges Like Excel FREQUENCY with pd.cutGroup order values, ages or delivery times into bands such as "0-10", "10-50" and "over 100". Use pd.cut with explicit edges and labels, decide which side of each band is closed, stop values outside the edges from silently becoming NaN, match Excel's FREQUENCY counts exactly, and use qcut for equal-sized groups.
- How-toCalculate Percent of Total per Group in pandasShow each product's share of its region's sales, each region's share of the company, and a crosstab where every row adds to 100%. Use groupby transform for shares within groups, crosstab normalize for tables, keep rows with missing groups, and round shares so they still add up to exactly 100%.
- How-toCount Distinct Values per Group in pandasCount unique customers per region, distinct products per order or active users per month — the "Distinct Count" that Excel pivot tables only offer through the Data Model. Use nunique, decide how blanks, case and spacing count, get several distinct counts at once, and avoid adding distinct counts across groups.
- How-toGet the Top N Rows per Group in pandasList the three best-selling products in each region, the largest invoice per customer, or the top five reps per quarter. Use sort_values with groupby().head(), decide what happens to ties with rank, rank aggregated totals rather than raw rows, and fold everything else into an "Other" line.
- How-toNumber Rows Within Each Group in pandasAdd a running number that restarts for every customer, region or order — the pandas equivalent of Excel's COUNTIF($A$2:A2,A2) and SQL's ROW_NUMBER() OVER (PARTITION BY ...): cumcount in a chosen order, ranks that handle ties, numbering consecutive runs, numbering the groups themselves, and writing the result to Excel.
- How-toCalculate Age and Tenure from Dates with pandas (DATEDIF Equivalent)Compute completed years, months and days between two dates for a whole column — ages from birth dates, tenure from hire dates, contract length — matching Excel's DATEDIF. Covers why days divided by 365.25 is wrong on birthdays, 29 February, an as-of date, fractional years and tenure bands.
- How-toBuild a Fixed Asset Depreciation Schedule with PythonTurn a fixed asset register into a monthly depreciation schedule: straight-line and double-declining balance with the switch to straight line, a start-of-depreciation convention, disposals part way through the year, net book values, a cost and depreciation roll-forward by category, and a check against Excel's SLN and VDB functions.
- How-toBuild a Lease Liability ScheduleCalculate a lease liability and right-of-use asset the way IFRS 16 does, with pandas: discount the payments at the incremental borrowing rate, unwind interest month by month with payments in advance, depreciate the asset straight line, split the liability into current and non-current portions, produce the month's journals and a maturity analysis, and check the opening liability against Excel's PV function.
- How-toBuild a Loan Amortization Schedule in Excel with PythonGenerate a month-by-month repayment schedule: the payment from the annuity formula, interest and principal split per period, cent rounding and a final payment that clears the balance exactly, overpayments that shorten the term, totals and payoff date, and a workbook with live PMT formulas checked against the Python result.
- How-toRun a Regression with Python in ExcelFit a multiple linear regression in a =PY cell with statsmodels: read a table with xl(), fit with a formula, return a tidy coefficient table to the grid, predict with intervals, check the residuals with a chart, and confirm the numbers against LINEST.
- How-toRun What-If Scenarios on an Excel Model with PythonKeep the Excel model as the single source of truth and let Python drive it: define scenarios as input values, write one copy of the model per scenario with openpyxl, recalculate them all in one headless LibreOffice call, read the outputs back, build a sensitivity table and write a scenario comparison workbook.
- How-toTurn an Excel Model into a Python FunctionKeep a profit model in Excel where finance owns it, and call it from Python like a function: compile the workbook's formulas with the formulas library into a callable that takes the input cells and returns the output cells, check its answers against LibreOffice, run two thousand Monte Carlo scenarios in a few seconds, and catch functions the library cannot evaluate before trusting the results.
Follow it through a track
Prefer an ordered route? These tracks cover excel functions in pandas along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
Excel functions in pandas is part of Formulas & Excel logic.