Topic area
Cleaning & transforming data
Turning what the workbook holds into data you can trust and reshape.
Topics in cleaning & transforming data
Each topic page gathers every guide, how-to, comparison and fix on its subject.
- 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.
Data cleaning
- OverviewAdvanced Data Transformation and Cleaning for Excel Automation
- GuideCleaning Excel Data With Pandas: A Production Workflow
- How-toClean Mixed-Currency Amounts into Amount and Currency Columns
- How-toCoalesce Several Excel Columns into One with pandas
Missing data
- GuideHandling Missing Data in Excel Reports with Pandas
- How-toDrop Rows with Missing Required Fields in pandas
- How-toFill Gaps in Per-Item Time Series with a Staleness Limit
- How-toFill Missing Values in Excel with Pandas fillna
Dates & times
- GuideWorking with Dates and Times in Excel Data
- GuideApplying Number and Date Formats in Excel
- How-toBuild a 4-4-5 Fiscal Calendar with pandas
- How-toBuild a Date Table for Excel Reports with pandas
Joins, lookups & comparisons
- GuideMerging and Joining Excel DataFrames with Pandas
- How-toAppend New Rows to a Master Excel File Without Duplicates
- How-toConcatenate Excel Sheets with Different Columns
- How-toCreate Every Combination of Two Lists with a Cross Join in pandas
Pivots & summaries
- GuideCreating Pivot Tables from Excel Data with Pandas
- GuideBuilding Finance and Operations Reports in Excel with Python
- How-toAdd a Native Excel Pivot Table with Python
- How-toAdd Subtotals and Grand Totals to a pandas Pivot Table
Validation & data quality
- GuideValidating Excel Data with Python
- How-toDetect Outliers in Excel Data with pandas
- How-toAdd Dropdown Data Validation to Excel with openpyxl
- How-toAnonymise Personal Data in an Excel File with Python
Other areas