Data Cleaning
Fix messy spreadsheet data with tools for trimming whitespace, removing blanks, deduplicating rows, detecting inconsistencies, and transposing — all without uploading your file.
Overview
Data cleaning in SheetTool covers the most common problems you encounter in real-world exports: leading and trailing whitespace that breaks matching, empty rows left over from form submissions, duplicate records from merged sources, and inconsistencies in casing, spacing, or date formatting. SheetTool auto-detects many of these issues and offers one-click fixes. For structural problems, the transpose tool swaps rows and columns when a report arrives in the wrong orientation.
Syntax & Reference
- Trim whitespace
- Removes leading and trailing spaces from every cell in the sheet. Essential before deduplication or matching, since hidden spaces make identical values look different.
- Remove empty rows
- Deletes rows where every cell is empty. Common in form exports, copy-paste operations, and files with separator rows.
- Remove duplicate rows (sheet-wide)
- Identifies and removes rows that are identical across all columns. Keeps the first occurrence and removes subsequent duplicates.
- Remove duplicates by column
- Deduplicates based on a specific column (e.g., email, SKU, order ID). When duplicates are found, the first row is kept.
- Auto-detect inconsistencies
- Scans columns for common data quality issues: extra whitespace, casing variations (e.g., 'Active' vs 'active'), and date format mismatches. Highlights problems for review.
- Transpose
- Swaps rows and columns. Use when a wide-format report needs to become tall (or vice versa) before further processing or import.
Examples
Clean a lead list from form exports
Start by trimming whitespace, then remove empty rows from abandoned form submissions, then deduplicate by email address to get a clean contact list.
Fix inconsistent dates
Use the inconsistency detector to find columns with mixed date formats (12/31/2024 vs 2024-12-31). Then use type casting to standardize to ISO format.
Prepare data from a transposed report
When a report arrives with months across columns but the next tool expects rows, transpose the data first, then rename the headers.
Multi-step cleanup pipeline
Trim whitespace → remove blanks → fix casing inconsistencies → deduplicate by key column → validate. This sequence handles most common data quality issues.
Tips & Best Practices
- Always trim whitespace before deduplicating. Hidden spaces are the most common reason duplicates are not detected.
- Review auto-detected inconsistencies before applying bulk changes, especially for identifiers, SKUs, or account codes where variations may be intentional.
- Use the recommended order: trim → remove blanks → deduplicate → validate. Each step makes the next one more effective.
- Combine data cleaning with the changelog to keep a record of what was fixed for audit or review purposes.
Frequently Asked Questions
What types of inconsistencies does SheetTool detect?+
SheetTool scans for extra whitespace (leading, trailing, double spaces), casing variations (e.g., 'Active' vs 'active' vs 'ACTIVE'), and date format mismatches within the same column. It highlights these for review so you can decide which to fix.
How does deduplication decide which row to keep?+
When duplicates are found, SheetTool keeps the first occurrence and removes subsequent duplicates. For column-based deduplication, the first row with each unique value is preserved.
Can I undo a cleaning operation?+
Yes. Every cleaning operation is fully undoable with Ctrl+Z. The changelog also tracks what was changed, so you can review the before and after.
Does data cleaning work on filtered data?+
Cleaning operations like trim and deduplication apply to all rows in the dataset, not just the visible filtered rows. Filter after cleaning to review specific subsets.