🐼 pandas

📘 Start with the full guides

The long-form treatment of pandas, with the reasoning the shorter pages assume.

• OverviewAdvanced Data Transformation and Cleaning for Excel AutomationBuild production-grade Python pipelines that clean, validate, merge, and format Excel data with pandas and openpyxl — staged, testable, and reproducible.
• GuideCleaning Excel Data With Pandas: A Production WorkflowTurn messy Excel exports into reliable reporting inputs with pandas: load as text, normalize headers and types, drop noise, deduplicate, validate, and export.
• GuideCreating Pivot Tables from Excel Data with PandasBuild pivot tables from Excel data in Python: ingest and clean a workbook, aggregate with pandas pivot_table, add totals, filter, and export a formatted report.
• GuideExcel Formula Equivalents in pandasTranslate SUMIF, COUNTIF, VLOOKUP, INDEX MATCH, nested IFs and text functions into pandas — with the mask-select-aggregate pattern behind all of them.
• GuideHandling Missing Data in Excel Reports with PandasHandle missing data in Excel reports with pandas: map blanks to NaN, profile gaps, choose type-aware fills for numeric, categorical, and date columns, then validate and export.
• GuideMerging and Joining Excel DataFrames with PandasMerge and join Excel DataFrames with pandas: normalize keys, pick the right join type, join on multiple columns, resolve suffix collisions, validate cardinality, and fix dtype and duplicate-key bugs.
• GuideMoving Data Between Excel and DatabasesExcel as the interface, the database as the source of truth: query to workbook with SQLAlchemy, load a spreadsheet into a table safely, keep types intact in both directions, and refresh on a schedule.
• GuideBuilding Finance and Operations Reports in Excel with PythonThe month-end pack, the aging report, the commission statement and the stock reorder list share one shape: exports in, reference data joined, a period chosen, numbers calculated, totals tied back to the source and exceptions surfaced first. This guide builds that shape once — a budget-versus-actual report from a ledger export — and shows how each finance and operations report reuses it.
• GuideReading Excel Files with PandasLoad Excel workbooks into pandas DataFrames: pick the engine, target sheets and columns, control dtypes, and read multi-sheet files — with runnable examples.
• GuideWorking with Multiple Excel Sheets in PythonRead, transform, and write multi-sheet Excel workbooks with pandas and openpyxl — load all tabs into a dict, merge across sheets, and export to named sheets.
• GuideWriting DataFrames to Excel with PandasExport pandas DataFrames to Excel: single and multi-sheet workbooks, number and date formats, appending to existing files, and choosing the right engine.

🛠️ Step-by-step how-tos

One task each, with code you can copy, run and adapt.

• How-toClean Mixed-Currency Amounts into Amount and Currency ColumnsTurn an Excel column of amounts typed with their currency — "€1.234,50", "USD 1,200", "1200 GBP", "£950", "CHF 1'250.00", "(45.00) EUR" — into a numeric amount column and an ISO currency code column with pandas: map symbols and codes, resolve the ambiguous dollar sign, detect decimal commas, keep negative signs and parentheses, convert to one reporting currency, and list what could not be read.
• How-toCoalesce Several Excel Columns into One with pandasTake the first non-empty value from several columns — Mobile, else Work phone, else Home phone — the way SQL's COALESCE or a chain of nested IFs in Excel does: bfill across columns, combine_first for two columns, treating blanks and placeholder text as missing, recording which column supplied each value, and flagging rows where the sources disagree.
• How-toConvert Currency Strings Like "$1,234.56" and "(500)" to Numbers with pandasAmount columns exported as text — "$1,234.56", "(1,200.00)", "-€50", "1.234,56 EUR", "750.00-" or "100.00 CR" — cannot be summed. Parse every common currency format into a signed number and a separate currency code, report the values that do not parse, and write real numbers back to Excel.
• How-toConvert Excel Text Columns to Numbers with pandasNumbers stored as text will not sum. Convert them in pandas — currency symbols, thousands separators, European decimals, parentheses negatives, and reporting what failed.
• How-toConvert Percentage Strings Like "12.5%" to Numbers with pandasA rate column that mixes typed text like "12.5%" with real percentage cells that pandas reads as 0.125 cannot be averaged. Convert every form — "12.5%", "12,5 %", "-3%", "25 bp", fractions and bare numbers — to one consistent fraction, flag the ambiguous values, and write them back with a percent format.
• How-toConvert Trailing Minus and CR/DR Amounts with pandasTurn amounts exported by SAP, Oracle and bank systems into signed numbers: trailing minus signs like 1,234.56-, accounting brackets, CR and DR suffixes, a separate debit/credit indicator column, European 1.234,56 formats, and a check that nothing was left as text.
• How-toDetect Outliers in Excel Data with pandasFind the invoice for 48,000 in a column of 400s, the negative quantity and the delivery that took 90 days. Compare IQR fences, z-scores and median-based robust scores on the same data, detect outliers within each group, add business rules, and flag rows in the workbook for review instead of deleting them.
• 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-toFilter Excel Rows by Condition with pandasKeep only the rows you need from a sheet: boolean masks, combining conditions with & | ~ and the brackets they need, isin, between and date ranges, text matching that does not crash on blanks, query strings, filters defined in a config file, and deleting rows from a formatted workbook in place with openpyxl.
• How-toKeep the Latest Record per Key When Deduplicating with pandasA customer export lists the same customer several times as their details changed. Keep only the most recent row per customer with sort_values and drop_duplicates, break ties on equal timestamps, handle dates stored as text and missing dates, and avoid groupby().last(), which quietly mixes values from different rows.
• How-toNormalise Postcodes and ZIP Codes with pandasClean the postcode column of an international address list from Excel: restore the leading zeros Excel stripped from US ZIP codes and other numeric postcodes, format UK postcodes with the single space in the right place, handle ZIP+4, Dutch and Canadian formats, validate each value against its country's pattern, and list the ones that need a human.
• How-toPandas: Drop Duplicates From an Excel ColumnRemove duplicate rows by a single Excel column with pandas drop_duplicates and the subset parameter — including keep options, NaN behavior, and pre-cleaning.
• How-toRemove Blank Rows From Excel With PandasStrip blank rows from an Excel file with pandas: dropna(how='all'), subset and thresh, whitespace-only cells, index resets, and writing the cleaned file back.
• How-toRemove Duplicate Words and Extra Punctuation from TextTidy free-text columns from Excel with pandas: remove accidentally repeated words such as "the the" while keeping genuine repeats like "Bora Bora", collapse runs of punctuation, fix spaces before commas and full stops, normalise curly quotes and dashes, strip leading and trailing junk, and keep a log of exactly which cells changed and how.
• How-toRemove Non-Printable Characters from Excel Data with PythonFind and remove the characters you cannot see in Excel text — carriage returns, tabs, bell and other control codes, soft hyphens, zero-width marks and leftover _x000D_ escapes — using Unicode categories, without damaging line breaks or emoji you want to keep.
• How-toReplace Values in an Excel Column with pandasRecode values in an Excel column with pandas — replace versus map, mapping tables kept in a workbook, regex replacements, per-column rules, numeric bands with case_when, and a report of values that no rule covered.
• How-toSplit a Column into Multiple Columns with pandasThe pandas version of Excel's Text to Columns: split names, codes and addresses on a delimiter, cap the number of pieces, extract parts with a regex, and turn multi-value cells into rows.
• How-toSplit an Address Column into Street, City and PostcodeBreak single-cell addresses from an Excel export into street, city, postcode and country columns with pandas: handle line breaks typed with Alt+Enter and comma-separated addresses, find the postcode with a pattern for each country, take the city from around it, keep extra lines such as building names, and send addresses the rules cannot split to a review list instead of guessing.
• How-toSplit Delimited Cell Values into Rows with pandasTurn cells like "Red; Blue; Green" or "INV-1, INV-2" into one row per value with str.split and explode: clean up spaces and empty items, split two parallel columns together (and handle the "matching element counts" error when they disagree), keep a link to the original row, count and pivot the results, and join them back into one cell when needed.
• 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-toStandardise Category Values with a Mapping Table in pandasTurn "N. America", "north america ", "NA" and "USA" into one region name using a mapping table kept in Excel, where the people who own the categories can maintain it. Normalise keys, check the table for conflicts, report unmapped values with counts and suggestions, and append new ones for review.
• How-toStandardise Excel Column Names with pandasTurn messy Excel headers — stray spaces, line breaks, symbols, duplicates, blanks and dates as headers — into consistent snake_case names, map synonyms from different sources to one canonical schema, and check required columns before processing.
• How-toStandardise Phone Numbers in Excel Data with PythonTurn a column of phone numbers typed every possible way — spaces, brackets, (0), 00 prefixes, missing country codes, numbers mangled into floats by Excel — into one consistent format with pandas and the phonenumbers library: E.164 for systems, international or national format for people, validity and line-type checks, and a review list of numbers that cannot be trusted.
• How-toStrip Whitespace and Normalise Text Columns with pandasTrailing spaces break joins and group-bys silently. Clean Excel text columns in pandas — strip, collapse inner whitespace, non-breaking spaces, case folding and accents.
• How-toAdd a Native Excel Pivot Table with PythonShip a real, refreshable Excel pivot table rather than a static grid — write the source as a table, drive Excel through xlwings on Windows, and the cross-platform alternatives.
• How-toAdd Subtotals and Grand Totals to a pandas Pivot Tablepandas' margins=True adds only a grand total. Add Excel-style subtotal rows for each outer group of a multi-level pivot, compute them from the raw data so averages stay correct, and write them to Excel as bold rows or live SUBTOTAL formulas.
• 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-toBuild a Cohort Retention Table from Excel DataTurn an order export into the classic retention triangle: assign each customer to the month of their first order, count how many come back in each following month, convert counts to percentages, compare cohorts, weight an average retention curve correctly, and write a colour-scaled table to Excel.
• How-toBuild a Crosstab from Excel Data with pandasCount how often categories occur together in an Excel sheet with pandas.crosstab — frequency tables, row and column percentages, totals, sums instead of counts, banded numbers, categories with zero counts and missing values that silently drop rows — then write the result to Excel with percentage formats.
• How-toBuild a Product Co-occurrence Matrix from Order LinesFind out which products are bought together from an order-lines sheet: build an order × product incidence table, multiply it by its transpose to count co-occurrences, read the diagonal as order counts, rank pairs by support, confidence and lift, avoid the pitfalls of quantities and duplicate lines, and write a heatmap matrix and a top-pairs list to Excel.
• How-toBuild an RFM Customer Segmentation from Excel DataSegment customers by recency, frequency and monetary value from an Excel order history: compute the three measures per customer, score them 1 to 5 with quantiles that survive ties, map score combinations to named segments such as Champions and At risk, summarise each segment, and write a workbook with a recency-by-frequency grid.
• How-toCalculate Moving Averages with pandas rollingSmooth daily sales with a 7-day moving average, per store, the way an Excel AVERAGE over a sliding range does. Learn why rolling(7) and rolling("7D") differ when days are missing, what min_periods does to the first rows, trailing versus centred windows, and how to roll within groups without scrambling rows.
• 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-toCompare Two Periods Side by Side in a PivotBuild the classic "this period versus last period" table from Excel sales data with pandas: choose comparable periods (same month last year, month to date versus the same days last month), pivot them side by side, compute change and percentage change that cope with new and discontinued products, rank the biggest movers, show each line's contribution to the total change, and write it to Excel with coloured arrows.
• 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-toCreate a Pivot Table from Excel with PandasCreate a pivot table from an Excel file with pandas: read the workbook, aggregate with pivot_table using index, columns, values and aggfunc, then export the result.
• How-toExport a Pandas Pivot Table to Excel (Formatted)Build a pandas pivot_table, flatten its MultiIndex, export with to_excel, then add a styled header, number formats, and a grand-total row with openpyxl.
• 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-toPivot Text Values Instead of Numbers in pandasTurn a long list of statuses, shift codes or comments into a grid — projects by week, employees by day — when the values are text, not numbers: pivot for unique combinations, the "duplicate entries" error and how to resolve it, pivot_table with first, last or joined values, picking the latest status by timestamp, and colouring the grid by value in Excel.
• How-toRun a Pareto Analysis on Excel Data with pandasFind the handful of customers or products that make up 80% of revenue. Aggregate, sort, compute cumulative shares, classify items into A, B and C with a clear rule for the item that crosses each threshold, and write the table with a native Excel Pareto chart — columns plus a cumulative line on a secondary axis.
• How-toSort Pivot Rows in a Custom Order with pandasPivot tables sort labels alphabetically — Apr, Aug, Dec, Feb… and L, M, S, XL — which is never what a report wants. Impose a business order with ordered Categoricals or reindex, keep empty categories visible, push "Other" and totals to the end, sort by value within a fixed group order, and make sure the order survives the trip to Excel.
• How-toUnpivot a Wide Excel Sheet with pandas meltTurn a month-per-column spreadsheet into tidy rows — pd.melt, choosing id_vars, parsing the column names into real dates, and handling multi-level headers.
• How-toUse Different Aggregations per Column in a pandas PivotSum revenue, average price, count distinct customers and find the last order date in one summary table. Use pivot_table's aggfunc dictionary or groupby named aggregation, compute weighted averages correctly, add a total row that uses the same rules, and write readable column names to Excel.
• 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-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-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-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-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-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-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-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-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-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-toFind the Header Row in an Excel Sheet with a Shifting LayoutExports that start their table on row 3 one month and row 6 the next break skiprows. Scan for the row that holds your required labels, then read from there — with aliases and column offsets handled.
• How-toParse Records That Span Multiple Rows in Excel with pandasBank statements wrap long descriptions onto extra rows, and contact exports stack one field per row. Number each record from the row that starts it, join the continuation lines, pivot stacked fields into columns, and catch orphan lines and repeated fields instead of silently mixing records.
• How-toRead an Excel Sheet Where Records Run Across ColumnsSome sheets are laid out sideways: field names down column A and one record per column — supplier comparisons, product spec sheets, quotes side by side. Read them with header=None and index_col=0, transpose, fix the column types that transposing mixes up, handle several blocks on one sheet, and write the tidy table back.
• How-toRead Excel with Multi-Row Headers in pandasLoad a sheet whose column headers span two rows — a merged quarter above Revenue and Units — into a MultiIndex, then select, flatten or reshape it into a tidy table.
• How-toRemove Subtotal and Total Rows from an Excel ExportSubtotal and grand-total rows double every sum you compute. Find them by label, by missing detail fields or by SUBTOTAL formulas, remove them, and use the totals to check your own numbers.
• How-toDrop Rows with Missing Required Fields in pandasdropna() with no arguments removes almost everything. Name the required columns, normalise blank-looking placeholders first, and keep the rejected rows with a reason and a source row number.
• How-toFill Missing Values in Excel with Pandas fillnaFill missing values in an Excel file with pandas fillna: normalize blanks to NaN, then apply a scalar, a per-column dictionary, forward/back fill, or interpolation, and export.
• How-toFind and Report Missing Values in an Excel FileAudit an Excel file for gaps before you use it — per-column null counts, disguised blanks like n/a and dashes, missing-value patterns, and a formatted report of what is absent.
• How-toForward-Fill Blank Cells in Grouped Excel Reports with pandasGrouped Excel reports show each region or category once and leave the rows below it blank, often in merged cells. Fill those labels down with pandas without leaking a label into the next group, filling data columns by mistake, or carrying labels into total rows.
• How-toImpute Missing Values by Group with pandasFill gaps in Excel data with values that make sense for each row's group rather than one column-wide average: group means and medians with groupby transform, a fallback chain when a whole group is empty, minimum group sizes, flag columns that record what was filled, and a check that imputation did not distort the totals you report.
• How-toInterpolate Missing Numeric Values in Excel DataFill gaps in an ordered series without inventing nonsense — linear and time interpolation in pandas, limits and directions, group-wise fills, and flagging what was estimated.
• How-toReplace Placeholder Text with NaN in pandasExcel sheets mark missing data with "n/a", "-", "TBC", -999 or 31/12/9999. Turn those placeholders into real missing values with read_excel's na_values, per-column rules and sentinel masks — without losing genuine values such as the country code NA.
• How-toTell Blank Cells, Empty Strings and Zeros Apart in Excel DataA cell that looks empty in Excel can be truly empty, a formula returning "", a typed space, a zero hidden by its number format, or a placeholder like N/A — and pandas collapses most of them into NaN. See what read_excel and openpyxl actually return for each, classify every cell, and choose a policy before you count or fill blanks.
• How-toConcatenate Excel Sheets with Different ColumnsStack monthly exports whose columns drift — align headers, map renamed fields, keep a source column, and report what each file was missing before you combine them.
• How-toCreate Every Combination of Two Lists with a Cross Join in pandasBuild every store × product, employee × training course or region × month combination with merge(how="cross"), then left-join the actual data so missing combinations show as zero or "not done" instead of disappearing. Covers three or more lists, MultiIndex.from_product, filtering impossible pairs, guarding against size explosions, and writing a complete grid to Excel.
• How-toFind Rows in One Excel File Missing from AnotherReconcile two spreadsheets in Python — an indicator merge for both directions, composite keys, near-matches from whitespace, and a formatted exceptions workbook.
• How-toFuzzy Match Names Between Two Excel Files with PythonJoin two spreadsheets when the names do not quite match — Acme Ltd vs ACME Limited — with rapidfuzz: normalise names, choose a scorer, set a cut-off, score every pair at once, and send doubtful matches for review.
• How-toHandle Many-to-Many Lookups Without Duplicating RowsWhen a lookup key matches several rows — a product in two promotions, a customer with several account managers — a pandas merge multiplies the left rows and every total built on them is inflated. Detect the fan-out, then choose deliberately: collapse the lookup to one row per key, pick one match by a rule, combine matches into a list, or allocate amounts across them so totals still reconcile.
• How-toMatch Rows by Nearest Date with pandas merge_asofAttach the exchange rate, price or status that applied at each transaction's date — the latest one on or before it — with pandas merge_asof. Match within groups such as currency, limit how stale a match may be, choose backward, forward or nearest, and fix the sorting and dtype errors it raises.
• How-toMerge Excel Data on Multiple Key Columns with pandasMatch rows on region and product, or store and date, rather than on one ID. Merge on several columns at once, find why some rows do not match — a key typed as 7 in one file and "007" in another, a trailing space — guard against duplicate keys multiplying rows, and fall back to a less specific key when the exact combination is missing.
• How-toMerge Excel Files with Different Column Names in pandasTwo systems export the same customers under different headers — "Customer No" and "cust_id", "Email" and "email_address" — with keys formatted differently. Map both files to one schema, normalise the keys, merge on several columns and combine the overlapping fields with a clear rule for which source wins.
• How-toMerge Two Excel Files on a Common Column in PythonMerge two Excel files on a shared column in Python with pandas: load both workbooks, normalize the key, join with pd.merge, handle different names and duplicate keys, export.
• How-toUpdate One Excel File from Another with pandasApply a changes file to a master list — new prices, corrected emails, customers added — the way people do with copy-paste and VLOOKUP. Compare DataFrame.update, combine_first and an explicit upsert, keep a change log of old and new values, and write the updates into the existing workbook without destroying its formatting.
• How-toValidate a pandas Merge to Avoid Duplicate RowsA duplicate key in a lookup sheet makes a pandas merge silently add rows and inflate totals. Use merge's validate argument, indicator column and before-and-after checks to catch duplicate keys, unmatched rows and missing keys that match each other before a report goes out.
• 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-toExport SQL Query Results to Excel with PythonRun a parameterised query with SQLAlchemy and write the result to a formatted workbook: one sheet per result set, correct date and money formats, sized columns, and chunked reads for big results.
• How-toLoad an Excel File into a SQL Database with pandasTake a spreadsheet people filled in and get it into a table safely: normalise the columns, pin the types, stage the rows, validate them, then merge in one transaction that either succeeds or leaves nothing behind.
• How-toLoad Excel into SQLite with a Defined SchemaLoad Excel sheets into SQLite tables that you designed — proper types, primary keys, NOT NULL and CHECK constraints, foreign keys and STRICT typing — instead of whatever pandas.to_sql guesses: create the schema first, convert dates and decimals deliberately, insert in one transaction so a bad row rolls the whole load back, and report exactly which rows broke which rule.
• How-toQuery a Database for IDs Listed in an Excel SheetSomeone sends a spreadsheet of customer or order IDs and wants the matching records. Clean the IDs, pass them to SQL safely as parameters, split long lists into chunks the database driver accepts, switch to a temporary table for very long lists, and report the IDs that were not found — in the order the sheet listed them.
• How-toScrape a Web Page Table into Excel with pandasTurn an HTML table on a web page into a clean Excel sheet. Fetch the page with requests, pick the right table out of several with read_html, flatten two-row headers, strip footnote markers and dashes so numbers become numbers, keep the links, and save the result with its source and time.
• How-toUpsert Excel Rows into a Database Table with PythonLoad a maintained Excel sheet into a database table without duplicating rows — insert new keys, update changed ones and leave unchanged rows alone. Use a staging table and INSERT … ON CONFLICT, a to_sql method for smaller loads, and checks for duplicate keys and rows deleted from the sheet.
• How-toConvert pandas Excel Code to PolarsPort an existing pandas Excel report script to Polars step by step: read_excel and to_excel equivalents, filtering, new columns, groupby and merge, pivot and fillna, the index that Polars does not have, NaN versus null, apply calls that become expressions, and a test that proves the new script produces the same workbook figures as the old one.
• How-toSpeed Up pandas Excel Reads with the calamine EngineSwap openpyxl for calamine in pandas.read_excel: install it, measure the difference, understand the trade-offs, and know which reads it will not help.
• How-toCheck Excel Data Types with pandasFind out what pandas actually read from a spreadsheet — object columns hiding mixed types, numbers stored as text, Excel date serials — and coerce them with a record of every failure.
• How-toCheck References Between Excel Sheets with pandasFind orders that point at customers who do not exist, products missing from the price list and lookup sheets with duplicate keys. Check every reference from one sheet to another with pandas, normalise keys so near-misses are caught, report the Excel rows that break a reference, and flag master rows nothing uses.
• How-toCompare Two Excel Files for Differences with PythonDiff two versions of a workbook with pandas — added, removed and changed rows keyed on an identifier, a cell-level change list, and a colour-coded difference report in Excel.
• How-toFind Duplicate Rows in Excel with PythonDetect exact and near-duplicate rows in a spreadsheet with pandas — key-based duplicates, whitespace and case traps, duplicate groups with counts, and a report you can send back.
• How-toFind Gaps in a Numbered Sequence with pandasCheck that invoice, cheque or ticket numbers in an Excel export run without gaps: extract the number from IDs such as INV-2026-000123, check each series separately, list missing numbers as compact ranges, catch duplicates and numbers issued out of date order, and write an exceptions report.
• How-toReconcile Excel Totals Against Control Totals with PythonProve that an Excel import or report is complete by reconciling it with control totals — record counts, amount totals and hash totals supplied by the source system — overall and per group, at every stage of a pipeline, with money compared in cents and a reconciliation sheet that shows exactly where figures diverge.
• How-toRun a Benford's Law Check on Excel DataTest whether the leading digits of amounts in an Excel ledger follow Benford's law: extract first digits correctly, compare observed and expected frequencies, measure conformity with the mean absolute deviation and a chi-square test, drill into over-represented digits such as amounts just below an approval limit, and chart the result in Excel.
• How-toValidate Email and Phone Formats in Excel Data with PythonCheck the email addresses and phone numbers in a contact sheet before importing it — a quick regex screen, email-validator for real address syntax, phonenumbers for national and international formats, normalised values for deduplication, and a report of invalid cells with their spreadsheet row numbers.
• How-toValidate Excel Columns Before Import with pandasCheck an incoming workbook's sheet, headers, column order and required fields with pandas before a single row reaches your pipeline — with clear, actionable failure messages.
• How-toValidate Excel Data with pandera SchemasDeclare a schema for a spreadsheet and enforce it: column types, ranges, regex checks, uniqueness, cross-column rules, lazy validation and a readable error report.
• 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-toCalculate Business Days Between Excel Dates with pandasReproduce NETWORKDAYS, NETWORKDAYS.INTL and WORKDAY in Python: count working days between two date columns with numpy busday_count, include both ends like Excel, exclude holidays from a sheet, and add working days to a date.
• How-toCalculate the Nth Weekday of a MonthFind dates like "the second Tuesday", "the first Monday" or "the last Friday" of every month with pandas: the WOM frequency in date_range, the WeekOfMonth and LastWeekOfMonth offsets, a plain function for a single date, the equivalent Excel formula written into a sheet and checked against Python, and what to do when the date lands on a public holiday.
• How-toCalculate Working Hours Between Two TimestampsMeasure support tickets, approvals and orders against service levels in business hours: count only the time between opening and closing on working days, skip weekends and holidays, handle requests raised overnight, check a vectorised version against a simple one, flag SLA breaches and write the results to Excel.
• How-toConvert Excel Time Values and Durations in PythonExcel stores times and durations as fractions of a day, and pandas returns them as time objects, timedeltas or 1900 dates depending on the cell format. Turn them all into timedeltas, handle shifts that cross midnight, parse durations typed as text and write durations back so Excel shows hours, not "1".
• How-toConvert Dates Like 20240131, 31.01.24 and 2024031 with pandasERP and mainframe exports write dates as 20240131, 31.01.24, 31-Jan-24 or day-of-year codes, and read_excel returns them as integers or text. Convert each layout with an explicit format, handle placeholders like 00000000, invalid dates such as 20240231 and two-digit years, and report what did not convert.
• How-toConvert Unix Timestamps to Excel Dates with pandasExports from APIs, logs and databases give times as Unix timestamps — 1790920800, or 1790920800000 in milliseconds. Detect the unit from the magnitude, convert with pd.to_datetime(unit=..., utc=True), move to the reader's time zone and drop the zone so Excel accepts it, handle zeros and text, convert back, and use the equivalent Excel formula when the conversion must stay in the sheet.
• How-toExpand Date Ranges into One Row per Day with pandasTurn rows with a start and an end date into one row per day — or per working day — to count how many people are away on each date, spread a contract's value across the months it covers, and summarise by month. With explode, a vectorised repeat for large sheets, business-day calendars and a check that nothing was lost.
• How-toFill Missing Dates in a Time Series from Excel with pandasDaily exports only have rows for days when something happened, so charts skip the quiet days and averages come out too high. Add the missing dates with reindex and date_range, fill counts with zero and balances with the last known value, do it per store, and aggregate duplicate dates first.
• How-toFind Overlapping Date Ranges with pandasCatch double bookings, overlapping contracts and clashing leave requests in a sheet of start and end dates: the overlap test, checking each record against the previous one per group, listing every overlapping pair, merging overlaps into continuous periods, finding gaps, and highlighting the clashes in Excel.
• How-toGroup Excel Data by Fiscal Year and ISO Week with pandasAssign every Excel row to a fiscal year, fiscal period and ISO week with pandas — any fiscal start month, labels such as FY2026/27, period numbers 1–12, ISO years that differ from calendar years around New Year, Excel's WEEKNUM rule, and complete weekly tables with no missing weeks.
• How-toGroup Excel Rows by Month and Quarter with pandasAggregate Excel data into monthly and quarterly totals with pandas — Grouper vs to_period, filling empty periods, fiscal years, and writing the result back as a formatted report.
• How-toHandle Timezones in Excel Timestamps with PythonExcel cannot store a timezone. Convert aware datetimes to one zone, strip the offset for writing, label the sheet, and re-localise correctly when reading the file back.
• How-toParse Excel Dates into Python datetimes with pandasTurn messy Excel date columns into real datetime64 values with pandas — parse_dates on read, explicit formats, dayfirst ambiguity, coerce-and-report, and mixed-type columns.
• How-toResample Irregular Readings to Regular IntervalsTurn sensor, meter or price readings taken at irregular times into tidy hourly or daily values with pandas: resample means and their bias, time-weighted averages for values that hold until the next reading, consumption per interval from cumulative meter readings by interpolating at the boundaries, gaps left visible instead of invented, several devices at once, and an Excel report.
• How-toRead a Large Excel File in Chunks with pandaspandas has no chunksize for Excel — build one with an openpyxl row generator, aggregate chunk by chunk, and keep peak memory flat on files with hundreds of thousands of rows.
• How-toReduce Memory When Reading Excel with pandasTwo different numbers decide whether a big Excel file fits in memory — the peak while the engine parses the workbook and the size of the DataFrame you keep. Measure both, then cut them with usecols, categorical and smaller numeric types, the right engine and, when even that is too much, streaming rows with openpyxl.
• How-toWrite Large DataFrames to Excel with write_only ModeGenerate hundred-thousand-row workbooks without the memory — openpyxl write_only, xlsxwriter constant_memory, streaming a DataFrame row by row, and the formatting you must plan ahead.
• How-toRead an Excel File from SharePoint or OneDrive into pandasDownload a workbook from a SharePoint library or a user's OneDrive with Microsoft Graph and read it straight into pandas from memory — resolving sites and drives, skipping unchanged files, and handling whole folders.
• How-toBuild a Bank Reconciliation Report with Python and ExcelMatch a month of bank statement lines to the cash book: first on cheque numbers and invoice references, then on amount within a few days, one to one. List what is left — outstanding cheques, deposits in transit, bank charges and interest — flag old unmatched items, and prove the adjusted bank and book balances agree.
• How-toBuild a Capacity Planning ReportCompare the work in the order book with the hours each work centre can actually deliver, week by week, with pandas: turn orders into required hours through a routing table, calculate available capacity from shifts, efficiency, holidays and planned maintenance, show utilisation as a heatmap, find overloaded weeks, and test whether pulling work forward into spare weeks clears the backlog.
• How-toBuild a Fleet Mileage and Fuel ReportTurn a fuel card export into a fleet report with pandas: distance driven from odometer readings at each fill-up, fuel economy per vehicle in litres per 100 km and miles per gallon, cost per kilometre, and the checks fleet managers rely on — odometers that go backwards, fills larger than the tank, the wrong fuel for the vehicle, and vehicles whose economy suddenly gets worse — written to a monthly Excel report.
• How-toBuild a Production Yield and Scrap ReportTurn production order records into a yield and scrap report with pandas: yield and first-pass yield per line, product and shift, scrap units and scrap cost at standard cost, a Pareto of scrap reasons showing the few that matter, weekly trends against a target, and checks that the recorded quantities add up — written to an Excel report for the production meeting.
• How-toBuild a Sales Commission Report with Python and ExcelCalculate commissions from a month of closed deals: tiered rates applied the way the plan defines them — marginal like tax bands or on the whole amount once a tier is reached — clawbacks for cancelled deals, reps close to the next tier flagged, a summary for payroll and one statement sheet per rep showing the working.
• How-toBuild a Sales Forecast with Exponential SmoothingForecast monthly sales from an Excel history with Holt-Winters exponential smoothing in Python: fit trend and seasonality with statsmodels, test the model on the months it did not see, compare it with a naive seasonal forecast, produce a twelve-month forecast with a range, and write the actuals, forecast and chart into a workbook the business can read.
• How-toBuild a Warehouse Pick List from OrdersTurn the day's open orders into printable pick lists with pandas: allocate each order line to stock in specific bins, oldest stock first, record shortages instead of over-allocating, combine identical items across orders into one batch pick, sort the picks in a snake path through the aisles so the picker never doubles back, and write one sheet per picker with tick boxes and a put-to-order table.
• How-toBuild a Warranty Claims ReportAnalyse warranty claims with pandas the way quality and finance teams need: claims per thousand units sold by product and by month of sale, how long after sale products fail, the failure modes that cost most, a production batch that fails more often than the rest, and an estimate of the claims still to come on units under warranty to support the warranty provision — written to an Excel report.
• How-toBuild a Work in Progress Report for Unbilled TimeValue the time a professional services firm has worked but not yet billed with pandas: price timesheet hours at each person's rate, subtract what has been invoiced, cap fixed-fee projects at their remaining fee, age the unbilled value by when the work was done, flag old WIP and projects over budget, and write the month-end WIP report that finance and project managers use to decide what to bill.
• How-toBuild an Accounts Receivable Aging Report with PythonTurn an open-invoice export into the aging report credit control works from: balances bucketed into current, 1–30, 31–60, 61–90 and over 90 days past due as at the period end, one row per customer with totals, credit notes netted, customers over their credit limit flagged and the total tied back to the ledger.
• How-toBuild an Energy Consumption Report from Meter ReadingsTurn irregular meter readings typed into a spreadsheet into monthly energy use per site with pandas: convert cumulative readings into consumption, handle a meter that was replaced and one that rolled over, spread each reading interval across calendar months by days, flag suspicious jumps, add cost from tariffs and emissions from a conversion factor, and write a site-by-month report to Excel.
• How-toBuild an Inventory Reorder Report with Python and ExcelDecide what to order this week from a stock export and recent sales: average daily usage, reorder points from lead time and safety stock, stock already on order counted, order quantities rounded up to pack sizes and minimums, days of cover, stock-out risks flagged and a purchase list grouped by supplier.
• How-toBuild an Overtime Report from Shift DataCalculate overtime from clock-in and clock-out records with pandas: shifts that cross midnight, unpaid breaks, hours rounded to the quarter hour, overtime above contracted weekly hours, premium rates for Sundays and bank holidays, overtime cost by department, and a check of average weekly hours against a 48-hour limit — written to an Excel report for payroll and managers.
• How-toCalculate FIFO Inventory Valuation with PythonValue stock and cost of goods sold first in, first out from purchase and sales ledgers: consume the oldest cost layers per SKU with a deque, cost each sale across several layers, value the layers left at the period end, stop on sales that exceed stock, compare with weighted average cost, and write the results to Excel with a reconciliation.
• How-toCheck Supplier Invoices Against Contract PricesCheck every supplier invoice line before payment with pandas: look up the contract price in force on the invoice date, allow a small tolerance, compare the quantity invoiced with the quantity received, check the line arithmetic and the invoice total, value every overcharge, and produce a list of invoices to hold with the reason and the amount to query — written to Excel for the accounts payable team.
• How-toConsolidate Timesheets from Many Workbooks with PythonEveryone fills in the same timesheet template and sends it back; you need hours by project and person for the week. Read the name and week from each file's header cells, unpivot the day grid into rows, spot who has not submitted, catch unknown project codes, impossible days and short weeks, and write one consolidated workbook.
• How-toModel the Impact of a Price IncreaseEstimate what a price increase will do to revenue and gross profit with pandas before announcing it: apply candidate increases per product group, model lost volume with simple elasticity assumptions, respect customer contracts that fix prices until a date, calculate the break-even volume loss for each product, compare scenarios side by side and write an Excel workbook whose assumptions sales and finance can change.
• How-toWrite Multiple DataFrames to One Excel FileWrite several pandas DataFrames into one .xlsx: separate sheets via one ExcelWriter, stacking on a single sheet with startrow, and appending with mode='a'.
• How-toRead an Excel File Directly from Amazon S3 with pandasLoad a workbook from S3 into a DataFrame without a temporary file: BytesIO reads, paginated prefix scans, readable error translation, and a Parquet cache for repeat runs.
• How-toGenerate Realistic Test Data for Excel Reports with FakerBuild believable, reproducible test workbooks for Excel report code with Faker and pandas: seeded generators, locale-specific names and addresses, a custom provider for your own codes, related sheets with valid foreign keys, deliberate messiness such as blanks, stray spaces and text dates, unique values, and a pytest fixture that writes the file.
• How-toTest Excel Code Against Several Library Versions with noxFind out before your users do whether your Excel automation still works with the next pandas or openpyxl release, and still works with the oldest versions you claim to support: a nox session matrix over Python and pandas versions, uv for fast environments, warnings turned into errors, and a lowest-versions session that checks the minimums in pyproject.toml.
• How-toRead an Excel Table by Name into pandaspandas has no option to read an Excel table by its name, but openpyxl knows every table's exact range. Find a table anywhere in the workbook, skip its totals row, get formula results, and load it into a DataFrame.
• How-toSort Excel Rows with Python Before Writingopenpyxl cannot sort a worksheet — order the data in pandas first. Multi-key sorts, custom category orders, natural sorting of codes, stable ties and where blanks end up.
• How-toControl How xlwings Converts DataFrames and RangesGet the Python values you expect from xlwings: why a range comes back as a scalar, a list or a list of lists, and how ndim, numbers, dates, empty, transpose and expand change that; how the DataFrame converter treats headers and the index on read and write; dictionaries from two-column ranges; and writing a custom converter for your own conventions, with its logic tested without Excel.
• How-toConvert an Excel Sheet to a LaTeX Table with PythonTurn a range from a workbook into a LaTeX table that compiles first time: read it with pandas, write it with to_latex or the Styler, escape the characters that break LaTeX (& % _ $ #), format numbers and percentages without letting a bare % comment out the row, add booktabs rules, a caption and a label, split long tables across pages, and check the result with pdflatex.
• How-toConvert an Excel Sheet to a Markdown Table with PythonTurn a worksheet into a Markdown table for a README, a wiki page, a pull request or a chat message. Use DataFrame.to_markdown, then fix what it gets wrong — unescaped pipes, line breaks inside cells, "nan" for blanks and raw float formatting — and convert every sheet of a workbook into one document.
• How-toConvert an Excel Sheet to an HTML Table with PythonTurn a worksheet into clean HTML for a web page, an email or an intranet report — pandas to_html, number and date formatting, Styler for highlights, captions and accessible headers, and several sheets on one page.
• How-toConvert CSV Files to Excel with PythonTurn CSV exports into proper Excel workbooks — detect the encoding and delimiter, keep IDs as text, parse dates, combine many CSVs into one workbook with a sheet each, split files larger than Excel's row limit, and format the result.
• How-toConvert XML to Excel with pandas read_xmlTurn an XML export — orders, invoices, a feed from a legacy system — into a workbook with one sheet per record type. Use pd.read_xml with XPath and namespaces, keep nested line items linked to their parent record, preserve codes with leading zeros, and stream very large files with iterparse.
• How-toRead .xls Files in Python with xlrd and pandasOpen legacy .xls workbooks from Python: install xlrd 2.x, read sheets into DataFrames, decode the date serials xlrd returns, and handle the 65,536-row ceiling.
• How-toRead Binary .xlsb Excel Files in PythonRead Excel Binary Workbooks (.xlsb) with pandas — the pyxlsb and calamine engines, why dates arrive as numbers with pyxlsb, how error cells look, reading sheet by sheet with pyxlsb directly, and converting .xlsb to .xlsx.
• How-toHandle Merged Cells When Reading Excel with pandasMerged cells read as one value and a run of blanks. Fix them in pandas with forward fill, detect merges with openpyxl, and unmerge a workbook before importing it.
• How-toHow to Read Excel Files With Pandas: A Step-by-Step GuideA copy-paste-runnable walkthrough for reading Excel files with pandas — from a one-line read to sheet and column targeting, header realignment, dtype control, and a defensive reader.
• How-toKeep Leading Zeros When Reading Excel with pandasCustomer IDs, ZIP codes and phone numbers lose their leading zeros in read_excel. Fix it for text cells with dtype, and for numbers that only look padded because of a 00000 format, by reading the format with openpyxl.
• How-toRead a Cell Range Like B3:F20 into a pandas DataFramePull exactly one block of cells out of a worksheet — a table that starts at B3 and ends at F20, with other content around it. Do it with read_excel's usecols, skiprows and nrows, or with openpyxl and an A1 range string, and turn it into one reusable function.
• How-toRead Yes/No and TRUE/FALSE Columns as Booleans with pandasExcel sheets store flags as TRUE/FALSE cells, "Yes"/"No" text, Y/N codes, 1/0 or a tick mark. See what read_excel does with each, why true_values quietly skips columns with blanks or odd spellings, and how to convert every flag column into a reliable nullable boolean.
• How-toRead Excel Sheets Whose Names Change with pandasFind the right sheet when its name changes every month, differs in case or spacing, moves position or hides behind a hidden tab — by pattern, by date in the name, by visibility or by the columns it contains.
• How-toRead Only Some Rows of an Excel File with pandasRead the first rows, a window in the middle, every nth row or the last rows of an Excel sheet with pandas — nrows, skiprows, skipfooter, and which of them actually saves time.
• How-toRead Specific Columns From Excel With PandasUse read_excel usecols to load only the columns you need by name, index, Excel-letter range, or callable — combine it with sheet_name and dtype to save memory.
• How-toSkip Rows and Set the Header When Reading Excel with pandasRead Excel files with title blocks, banners and multi-row headers using pandas — skiprows, header, nrows, usecols, and finding the header row automatically when it moves.
• How-toUse Converters When Reading Excel with pandasClean values as they are read — account codes with leading zeros, currency text, Y/N flags and mixed date cells — with read_excel converters, and know how they interact with dtype and empty cells.
• How-toUse Nullable Data Types When Reading Excel with pandasStop blank cells turning whole integer and TRUE/FALSE columns into floats: read Excel with dtype_backend="numpy_nullable" or "pyarrow", set Int64 and boolean per column, see why convert_dtypes after the read is too late for booleans, and write the NA values back as empty cells.
• How-toClean Data with pandas in Python in ExcelUse pandas inside Excel's =PY() cells to clean a messy table — trim text, fix types, drop duplicates, standardise categories — and return the cleaned table to the grid, with the calculation-order and output-mode rules that make it work.
• How-toReturn DataFrames and Values from Python in Excel CellsA =PY cell returns its last expression, either as a Python object card or spilled into the grid as Excel values. Shape DataFrames for the grid, avoid #SPILL!, and chain cells without recomputing.
• How-toCreate a Sheet for Each Group in a DataFrameSplit one DataFrame into one worksheet per region, customer or month with groupby and ExcelWriter — with safe sheet names, a contents sheet, consistent formatting, and the silent overwrite trap when two groups map to the same name.
• How-toRead All Sheets from an Excel File into DataFramessheet_name=None returns a dictionary of every sheet — how to use it, why one ExcelFile beats repeated read_excel calls, how to filter and concatenate sheets, and what it costs on a large workbook.
• How-toAppend a DataFrame to an Existing Excel File with pandasAdd rows or a new sheet to a workbook that already exists — ExcelWriter mode 'a', if_sheet_exists options, appending below existing data, and the openpyxl-only limits.
• How-toAuto-Fit Column Widths When Writing with pandasStop Excel reports opening with ##### and truncated headers — measure content width in Python and set column widths with xlsxwriter or openpyxl, including a reusable helper.
• How-toControl How NaN, None and Infinity Appear in Excel Outputpandas writes NaN, None, pd.NA and NaT as empty cells and infinity as the text "inf". Learn what each choice does to Excel formulas, when na_rep and inf_rep help or hurt, and how to handle division-by-zero results and placeholders column by column.
• How-toExport a Styled pandas DataFrame to ExcelUse pandas Styler to colour, bold and highlight a DataFrame, then write it to Excel with the styles intact — and learn which Styler features survive the trip, including why format() does not and what to use instead.
• How-toHow pandas Data Types Are Written to Excel CellsWhat to_excel actually stores for each pandas dtype — integers, nullable types, floats, booleans, text, datetime64, date objects, timedeltas, categoricals, Decimal, infinity, Period, lists and timezone-aware timestamps — checked cell by cell with openpyxl, plus the conversions to apply before writing so the workbook holds what you meant.
• How-toWrite a DataFrame to a Specific Cell in Excel with pandasPlace a DataFrame at B5 instead of A1 — with startrow and startcol, into a new file or into an existing template without wiping its title, formatting and notes — and know when to drop down to openpyxl.
• How-toWrite a MultiIndex DataFrame to Excel with pandasWrite pivot tables and grouped results with MultiIndex rows or columns to Excel — merged or repeated labels, the extra index-name row, the index=False error, flattening for filters, and reading it back.
• How-toWrite Clickable Links from a DataFrame Column to ExcelTurn a column of URLs into clickable links when writing a DataFrame to Excel: rely on xlsxwriter's automatic URL detection, write HYPERLINK formulas with friendly text, or set cell.hyperlink with openpyxl. Covers link text, internal sheet links, URL length limits and formula injection.
• How-toWrite Excel Formulas from a pandas DataFramePut live formulas into a sheet written with to_excel: build formula strings per row, look up column letters by name so the formulas survive column changes, account for the header, index and startrow offsets, add a totals row, understand why the results read back as 0 or NaN until the file is recalculated, and turn the formula conversion off for text that merely starts with "=".
• How-toWrite a Pandas DataFrame to Excel Without the IndexPass index=False to to_excel() to drop pandas row numbers from your spreadsheet — plus how it behaves with a MultiIndex, append mode, and a quick output check.

⚖️ Compare the options

Which tool or approach to pick, and why.

• Comparisonopenpyxl vs pandas for Excel Automationpandas reads Excel through openpyxl, so they are not rivals. See where the line falls — rows versus cells — and how to hand off between them in one script.
• Comparisonpandas vs DuckDB for Excel DataWhen to query Excel data with DuckDB's SQL instead of pandas: reading .xlsx files with read_xlsx, speed on a large workbook, SQL against DataFrames already in memory, joins and window functions, the types DuckDB infers from Excel, a glob that silently reads one file, and writing results back to Excel.
• Comparisonpandas vs Polars for Excel WorkflowsBoth read Excel through calamine and write through xlsxwriter, so the real difference is the transform. Compare the APIs, the speed, and how to move between them.
• Comparisonopenpyxl vs xlsxwriter vs pandas.ExcelWriterUnderstand how pandas.ExcelWriter wraps the openpyxl and xlsxwriter engines, when each one wins, and write the same DataFrame three ways with a clear decision table.
• ComparisonRound Numbers for Excel Output: float_format vs Number Formatspandas offers three ways to get two decimal places into Excel — to_excel's float_format, DataFrame.round and a cell number format — and they do different things. See which one changes the stored value, which one changes only the display, and why they disagree on halves like 0.015.

🔧 When it breaks

The errors this topic produces, with the cause explained rather than just the fix.

• FixFix Garbled Characters Like "Café" and "’" in Excel DataText that shows "Müller", "€100" or "’" was UTF-8 read as Windows-1252 somewhere upstream. Detect the affected cells, repair them with a one-line re-encode or with ftfy, avoid the repairs that damage correct text, recognise what cannot be recovered, and stop it happening again.
• FixFix the Capitalisation of Names with Exceptions in pandasTurn SHOUTED and lower-case names in an Excel list into proper names without the mistakes str.title makes: O'Brien instead of O'brien, McDonald, hyphenated surnames, particles such as van and de, generational suffixes like III, company suffixes and acronyms such as LLC and UK, leaving already mixed-case names alone, and a review list of everything that changed.
• FixFix Excel Serial Numbers Showing Instead of DatesYour Excel file shows 45292 where a date should be. Fix it in Python with openpyxl number formats, pandas ExcelWriter date formats, and a repair pass for existing workbooks.
• FixFix "Excel does not support datetimes with timezones" in pandasExcel cells cannot store a time zone, so pandas refuses to write timezone-aware timestamps. Decide which clock the reader needs, convert to it, drop the zone, and label the column — for columns, indexes and object columns alike.
• FixFix "Excel File Format Cannot Be Determined" in pandasWhy pandas cannot pick an engine for your spreadsheet, how to name the engine explicitly, and how to handle streams, URLs and files with no extension at all.
• FixFix "ExcelWriter object has no attribute save" in pandaspandas 2.0 removed ExcelWriter.save(), so old scripts fail with AttributeError. Replace it with a with block or close(), and fix the other removed writer APIs that usually break at the same time — book assignment, sheets assignment and handles.
• FixFix Floating-Point Noise Like 0.1 + 0.2 ≠ 0.3 in Excel OutputSums such as 0.1 + 0.2 print as 0.30000000000000004, prices turn into 1998 cents instead of 1999, and threshold checks fail by a hair. See where binary floating point leaks into Excel reports, what the xlsx writers already hide, and how to round, compare and convert money safely.
• FixFix "Missing optional dependency 'openpyxl'" in pandaspandas reads and writes Excel through separate engine packages, and the error means the engine is not importable from the Python that runs your code. Find which interpreter is running, install into that one, and handle the version-too-old variant.
• FixFix pandas FutureWarnings in Excel PipelinesClear the FutureWarnings an Excel reporting script prints under pandas 2.x before they turn into errors in pandas 3: month-end and hourly frequency aliases, fillna(method=), applymap, positional Series indexing, chained assignment and inplace on a column — plus the silent pandas 3 change to text columns that breaks dtype == object checks — with each warning reproduced under both versions and fixed.
• FixFix "This sheet is too large" When Writing Excel with pandaspandas raises "This sheet is too large! Your sheet size is … Max sheet size is 1048576, 16384" when a DataFrame exceeds Excel's grid. Split rows across sheets, transpose or split wide frames, summarise, or choose another format — and avoid the off-by-one that silently drops a row.
• FixFix "Unnamed: 0" Columns When Reading Excel with pandasColumns called "Unnamed: 0", "Unnamed: 3" or "Unnamed: 0_level_1" appear when a header cell is empty. Find which of the four causes produced them and apply the matching fix — index_col, usecols, header rows or a cleanup step.

🧭 Follow it through a track

Prefer an ordered route? These tracks cover pandas along the way, in the order that builds on itself.

• 🧹Data transformation141 of this topic's pages
• 🌱Getting started49 of this topic's pages
• 🚀Reporting workflows23 of this topic's pages
• 🎨Formatting & charting2 of this topic's pages

🔗 Related topics

Subjects that keep turning up on the same pages as this one, most overlap first.