Topics
Every subject on the site, grouped into areas. A topic page brings together every guide, how-to, comparison and fix on its subject — wherever in the site it lives.
Want an ordered route instead? Follow a track, or search if you know the words.
Reading & writing workbooks
Getting data out of a workbook and back into one — every format, every layout, every sheet.
- Reading Excel filesread_excel, openpyxl and the options that decide what actually lands in your DataFrame.
- Writing Excel filesto_excel, ExcelWriter and appending to workbooks that already exist.
- Many sheets & many filesCombine a folder of workbooks, split one by value, copy and reorder sheets.
- File formats & conversion.xlsx, .xlsm, .xls, .xlsb, .ods, CSV, JSON and Parquet — reading each and converting between them.
- Messy spreadsheetsShifting headers, stacked tables, merged cells and subtotal rows in sheets people built by hand.
Cleaning & transforming data
Turning what the workbook holds into data you can trust and reshape.
- Data cleaningWhitespace, text-as-numbers, blank rows, duplicates and columns that need splitting.
- Missing dataFinding, reporting, filling and dropping the gaps in a sheet.
- Dates & timesSerial numbers, parsing, timezones, business days and grouping by month.
- Joins, lookups & comparisonsVLOOKUP and INDEX MATCH in pandas, merging files, fuzzy matching and diffing two workbooks.
- Pivots & summariesPivot tables, group-bys, running totals and rankings — in pandas and in the workbook.
- Validation & data qualitySchema checks before import, dropdown rules in the sheet, and flagging bad cells.
Formulas & Excel logic
Writing formulas from Python, reading their results, and replacing them with code.
- Formulas in workbooksWriting formulas with openpyxl, reading cached results, named ranges and recalculation.
- Excel functions in pandasSUMIF, COUNTIF, IF, INDEX MATCH, LEFT/MID and friends, translated function by function.
- Python in ExcelThe =PY() formula: referencing ranges with xl(), returning DataFrames and plotting in cells.
Formatting & presenting reports
Making a generated workbook look like somebody finished it.
- Cell styling & layoutFonts, fills, borders, widths, frozen panes, merged titles, hyperlinks and print setup.
- Number & date formatsCurrency, percentages, dates and custom format codes that survive the round trip.
- Conditional formattingColour scales, data bars, thresholds, duplicates and whole-row highlighting.
- Charts & imagesNative Excel charts, sparklines, matplotlib figures, logos and pictures in cells.
- Tables, filters & outlinesExcel tables, autofilters, sorting and grouped outline rows.
- Report layouts & templatesMulti-sheet dashboards, summary and contents sheets, and filling a designed template.
- Protection & sharingLocked cells, passwords, hidden sheets and comments for workbooks other people will open.
Automating & delivering reports
The job around the workbook: where the data comes from, when it runs, and who receives it.
- Databases, APIs & sourcesSQL queries, REST APIs, Google Sheets and cloud files as the input to a report.
- Scheduling & orchestrationcron, Task Scheduler, APScheduler, Airflow, GitHub Actions and Docker.
- Email deliverysmtplib, Outlook and the Graph API — attachments, HTML bodies and recipient lists.
- Publishing & sharing filesS3, SharePoint, Google Drive, network shares, Slack and Teams.
- PDF exportTurning a finished workbook into a PDF, on Windows or on a Linux server.
- Excel in web appsDownload endpoints and uploads in Flask, FastAPI and Django, built in memory.
- Logging, testing & packagingLogs, retries, pre-send checks, pytest, config files and shipping a script to someone else.
Driving Excel itself
When the file is not enough and you need the Excel application — on the desktop or online.
- xlwingsReading and writing a live workbook, calling Python from Excel, refreshing pivots.
- COM & pywin32win32com on Windows: ranges, refreshes, dialogs and closing Excel cleanly.
- Macros & VBARunning macros from Python, keeping .xlsm files intact, and replacing VBA with Python.
- Excel Online & Microsoft GraphWorkbooks in SharePoint and OneDrive, edited in place through the Graph API.
Libraries, speed & errors
Choosing the right library, making it fast, and decoding what it throws at you.
- Choosing a libraryopenpyxl, pandas, xlsxwriter, xlwings and Polars compared on the jobs they are good at.
- pandasEverything on the site that runs through a pandas DataFrame.
- openpyxlCell-level reading, writing and styling with openpyxl.
- xlsxwriterFast, richly formatted new workbooks with xlsxwriter.
- Polars & ArrowPolars, calamine and Arrow for faster reads and columnar pipelines.
- Large files & performanceChunked reads, read-only and write-only modes, parallel jobs and benchmarks.
- Errors & fixesThe exceptions and broken outputs that stop a job, with the cause explained.