Clean Data

Prepare data
Standardize messy customer or transaction exports with automated whitespace, casing, date, number, and mapping rules in Excel.

An analyst receives a customer or transaction export from another system every week. It has stray spaces, inconsistent capitalization, numbers and dates stored as text in multiple formats, blank rows, and several spellings of the same value. Fixing it by hand or with nested worksheet formulas is tedious and must be redone every week. This template cleans the export automatically and reports both the changes made and any records requiring manual review.

Result preview

When the workbook opens, the cleaned table appears with all columns intact. A summary table displays the count of cells changed by each rule—showing 23 whitespace fixes, 19 capitalization corrections, 33 standardized state codes, 29 parsed dates, 54 numeric conversions, 2 dropped blank rows, and 3 dropped duplicates. An unresolved issues preview beside the cleaning rules immediately displays flagged values (such as an unmapped state on row 12, an impossible date on row 19, and a non-numeric amount on row 39), with an explanatory note and a reference to the complete log on the Problems sheet.

What this template does

  • Cleans customer and transaction exports pasted into the Raw Data sheet, accommodating up to 12 columns and 500 rows.
  • Derives column headers dynamically from user data and preserves unmapped columns without alteration.
  • Trims leading, trailing, and doubled whitespace from text fields without altering letter casing.
  • Converts all-capital and all-lowercase names and statuses to title case, preserving uppercase acronyms (such as LLC, INC, and LTD) and words with an internal capital (such as McDonald or BlueSky).
  • Maps variant state names and abbreviations to standard postal codes using an editable reference table.
  • Parses diverse date strings into standardized calendar dates. Text without a day, month and year is flagged as an unparseable date, and day and month are read month-first (03/04/2026 is March 4).
  • Strips Unicode currency symbols (category Sc including $, €, £, ¥) and ISO currency codes (such as USD, EUR), and treats accounting parentheses (x) and trailing minus signs as negative values, converting them to numeric floats.
  • Removes fully blank rows and exact duplicate records, counting only rows actually dropped.
  • Displays an unresolved issues preview on the main sheet and logs complete exception details on a dedicated Problems sheet with explicit Raw Data row references.

Why Python

Data cleaning often requires chained string transformations, regular expressions, robust date parsing, and lookup tables for variant values. In standard spreadsheet formulas, combining TRIM, SUBSTITUTE, TEXT, and DATEVALUE across dozens of columns produces fragile expressions that break whenever formats vary. Python handles string normalization and multi-format date parsing reliably in a single reactive pipeline.

Paste an export into Raw Data, toggle rule switches on the Settings sheet, or add custom mapping rows. The cleaned output and audit summaries update automatically.

Operating workflow

Author: A data analyst or technical lead customizes cleaning rules, configures mappings, and maintains the workbook pipeline.

Workbook user: An analyst or operations coordinator pastes weekly data exports into Raw Data, toggles cleaning rules, and reviews flagged exceptions without writing code.

The workbook can also be viewed in App mode to provide a streamlined, card-like presentation for operational users. App mode is a presentation setting rather than permissions, source access, or workbook trust.

Download the Excel template

Inputs and assumptions

Input Location Description
Raw export table Raw Data!A4:L504 Input export data, accommodating up to 12 columns and 500 rows
Rule switches Settings!A5:B12 On/off toggles for each of the seven cleaning rules
Value mappings Settings!D5:E35 Reference table mapping variant text values to standardized targets

The raw export range accommodates up to 12 columns and 500 data rows, allowing larger datasets to be pasted over the table. Trailing blank rows beyond the active paste are automatically ignored.

Notebook implementation

The Python for Excel notebook binds the raw data range, rule settings, and value mappings:

inputs = bf.inputs(
    raw=bf.ref("Raw Data!A4:L504"),
    settings=bf.ref("Settings!A5:B12", headers=True),
    mappings=bf.ref("Settings!D5:E35", headers=True),
)

The cleaning pipeline inspects active switches, normalizes text with regular expressions, parses dates with python-dateutil, and publishes results back to the worksheet:

publication = bf.publish(
    outputs={
        "changes": changes,
        "issues": issues_out,
        "clean": clean_out,
        "problems": problems_out,
    }
)

How the calculation/model works

  1. Filtering & Deduplication: Trims trailing all-blank rows from the raw input range before evaluation. Within the populated data, completely blank rows are removed only when the switch is active, reporting an accurate count of dropped rows. When deduplication is active, duplicate rows are dropped based on cleaned cell values while preserving the first instance; blank rows are excluded from duplicate checks so that each toggle controls only its own behavior.
  2. Text Normalization: Collapses consecutive whitespace characters and strips boundary spaces. Recases all-capital and all-lowercase name, city, and status words to title case, preserving corporate acronyms (such as LLC, INC, LTD) and words with an internal capital (such as McDonald).
  3. Value Mapping: Compares state values against the Mappings table using case-insensitive matching to convert variants (e.g., “new york”, “N.Y.”, and “New York” all become “NY”) to canonical standard codes. With Trim whitespace off, values are compared exactly as entered. Unmapped values not present as mapping keys or standard values are flagged in the exception log.
  4. Data Type Parsing: Dates are parsed into ISO format (YYYY-MM-DD). Currency strings and formatted numbers (supporting Unicode currency symbols, ISO currency codes, accounting parentheses, and trailing minus signs) are stripped of symbols and converted to numeric floats.
  5. Exception Logging: When a date or amount cannot be converted, or when a value in a mapped column cannot be matched to the mappings table, the raw data row number, column name, original value, and error description are recorded under a “Raw Data row” column header. The first 5 exceptions appear directly on Clean Data, while the Problems sheet hosts the full 100-row log.

Architecture

  • Rule extension: Cell-level cleaning rules are driven by CELL_RULES in the # --- Edit these rules --- cell of notebook.py. Adding a clean_<name> function and an entry in CELL_RULES automatically registers its change counter and settings toggle; rules omitted from Settings default to enabled, and rules targeting columns absent from the pasted table are skipped.
  • Header alignment and toggle independence: Blank header cells in Raw Data skip that column while keeping subsequent columns aligned with their headers. Deduplication compares cleaned cell values and ignores all-blank rows so Drop empty rows and Drop duplicate rows operate independently.

Validation / expected results

Metric / Check Expected baseline
Whitespace adjustments 23
Capitalization fixes 19
Value mappings applied 33
Dates standardized 29
Numeric amounts parsed 54
Blank rows dropped 2
Duplicate rows dropped 3
Unresolved issues 3

Disabling the whitespace rule zeroes the whitespace change count and preserves original spacing in the output. Adding a new row to the Mappings table converts unmapped variants and resolves their flagged problem entry. Correcting an unparseable date in the raw data table immediately removes that record from both the issues preview on Clean Data and the unresolved issues list on Problems. Blanking a middle header cell in Raw Data leaves that column out of the cleaned output, and the columns after it still align with their headers and clean correctly.

Limitations

  • Deduplication detects identical cleaned records; fuzzy deduplication across similar customer names requires specialized entity resolution.
  • Multi-table joins and relational lookups should be performed before or after this cleaning step.
  • Very large exports (exceeding hundreds of thousands of rows) should be staged in batches.

When to use this approach

Use this template for recurring data imports from external CRM, billing, or ERP systems that contain formatting inconsistencies. It eliminates repetitive manual editing and provides a clear summary of applied transformations and flagged exceptions.

Changelog

2026-10-17

  • Compact task pane tables by disabling column types, summary charts, and downloads.