clean-data-xls

Clean Data

Clean messy data in the active sheet or a specified range.

Environment

• Office JS: Use Excel.run(async (context) => {...}). Read via range.values, write helper-column formulas via range.formulas = [["=TRIM(A2)"]].
• Standalone .xlsx: Use Python/openpyxl.

Workflow

Step 1: Scope

• If a range is given (e.g. A1:F200), use it
• Otherwise use the full used range of the active sheet
• Profile each column: dominant type (text / number / date) and outliers

Step 2: Detect issues

Issue	What to look for
Whitespace	leading/trailing spaces, double spaces
Casing	inconsistent casing in categorical columns (usa / USA / Usa)
Number-as-text	numeric values stored as text; stray $, ,, % in number cells
Dates	mixed formats in the same column
Duplicates	exact and near-duplicate rows
Blanks	empty cells in otherwise-populated columns
Mixed types	a column that's 98% numbers but has 3 text entries
Encoding	mojibake, non-printing characters
Errors	#REF!, #N/A, #VALUE!, #DIV/0!

Step 3: Propose fixes

Show a summary table before changing anything:

Column	Issue	Count	Proposed Fix

Step 4: Apply

• Prefer formulas over hardcoded cleaned values — write =TRIM(A2), =VALUE(SUBSTITUTE(B2,"$","")), =UPPER(C2), =DATEVALUE(D2) in a helper column rather than overwriting the original.
• Only overwrite in place when user requests, or when no formula equivalent exists (encoding repair).
• For destructive operations (dedup, fill blanks, overwrite), confirm first.
• After each category (whitespace -> casing -> number conversion -> dates -> dedup), show a sample and get confirmation before moving on.
• Report a before/after summary.