How to Use the Data Cleanup Tool in Google Sheets to Remove Duplicates and Trim Whitespace

When collecting data through Google Forms, web scrapers, or third-party CRM exports, spreadsheets frequently suffer from messy formatting. Trailing and leading spaces prevent lookup formulas like VLOOKUP and XLOOKUP from matching identical strings, while accidental duplicate entries distort numerical summaries and pivot tables. While users historically relied on complex regex formulas or script workarounds, Google Sheets features a native, dedicated Data cleanup toolkit that automates whitespace trimming, duplicate removal, and data column auditing.

Overview of the Native Data Cleanup Suite

Located directly within the main menu bar under Data > Data cleanup, Google Sheets provides three distinct utilities designed to sanitise raw datasets:

• Cleanup suggestions: An AI-assisted inspection sidebar that scans your dataset for inconsistent values, blank cells, formatting anomalies, and duplicated rows.
• Remove duplicates: A surgical tool allowing you to purge repeated records based on specific column keys while preserving header rows.
• Trim whitespace: An instant one-click command that removes leading, trailing, and repeated interior spaces across selected ranges without altering underlying values.

How to Use Cleanup Suggestions for Intelligent Auditing

Before deleting data permanently, reviewing automated suggestions ensures you do not inadvertently erase valid records:

1. Open your spreadsheet in Google Sheets.
2. Click any cell inside your dataset, or highlight the target range manually.
3. Click Data on the top menu bar.
4. Hover over Data cleanup and select Cleanup suggestions.
5. A dedicated panel appears on the right side of the browser window. Sheets inspects your columns and surfaces recommendations, such as:
   • Trim whitespace: Identifies cells containing superfluous spaces.
   • Duplicate rows: Flags exact matching rows for one-click removal.
   • Inconsistent values: Identifies entries that diverge from standard column formats (e.g. text entered in a numeric column).
6. Review each suggestion individually. Click Trim all or Remove duplicates to accept suggestions, or click Ignore to dismiss recommendations for intentional variations.

How to Remove Duplicates Based on Specific Columns

When working with large transaction ledgers, customer contact lists, or survey submissions, removing duplicate records requires precise control over which columns determine uniqueness:

1. Highlight the entire data table, including the header row. (Pressing Ctrl + A while inside a table container automatically selects the entire block).
2. Click Data > Data cleanup > Remove duplicates.
3. In the modal dialogue box:
   • Check the box labelled Data has header row. This locks the top row and labels column checkboxes with your human-readable headers instead of generic letters.
   • Select the columns that define a duplicate record. If an entire row must match across every column, leave Select all checked. If a record is considered a duplicate solely based on a shared email address or transaction ID, uncheck all columns and check only the unique identifier column (e.g. Email or Order Number).
4. Click the green Remove duplicates button.
5. Google Sheets displays a confirmation prompt stating how many duplicate rows were removed and how many unique rows remain. Click OK.

How to Trim Whitespace Across Selected Ranges

Invisible spaces appended to the beginning or end of text strings are among the most common causes of formula errors in Google Sheets. To strip unnecessary spaces instantly:

1. Highlight the columns, rows, or specific cell range you want to sanitise.
2. Click Data > Data cleanup > Trim whitespace.
3. Sheets evaluates every cell in the selection, stripping leading and trailing spaces and reducing multiple consecutive spaces between words to a single space.
4. A notification bubble appears at the bottom-left corner confirming how many cells had whitespace trimmed.

Best Practices and Edge Cases

To avoid data loss when executing cleanup operations, adhere to the following best practices:

• Preserving raw data: Data cleanup permanently modifies existing cell values. Duplicate your active tab (right-click the sheet tab at the bottom and choose Duplicate) before purging duplicates so you retain a historical backup.
• Case sensitivity: The native Remove Duplicates tool is case-insensitive (treating “John” and “john” as duplicates). If case sensitivity is critical to your workflow, employ a custom formula using UNIQUE combined with EXACT instead.
• Merged cells: Data cleanup tools cannot process ranges containing merged cells. Unmerge your layout cells prior to running cleanup operations.