Whether you're preparing data for a dashboard, running a mail merge, or analyzing financial records, dirty data will skew your results. Learning how to effectively clean up Google Sheets data is one of the most valuable skills you can develop.
In this guide, we provide the ultimate step-by-step checklist to take any messy spreadsheet and turn it into a pristine database.
Step 1: Remove Duplicate Rows
Duplicates inflate your numbers and cause embarrassing double-emails in marketing campaigns. Before doing any formatting, you must purge redundant data.
You can use the built-in tool (Data > Data cleanup > Remove duplicates) or use a professional tool like CleanSheet to intelligently keep the first entry while deleting the subsequent duplicates.
Step 2: Trim Whitespace
Extra spaces before or after a word are invisible to the naked eye but will break your VLOOKUP and COUNTIF formulas. A cell containing "John " is fundamentally different from "John".
Use the formula =TRIM(A1) in a helper column to strip out all leading and trailing spaces, then paste the values back over the original column.
Step 3: Standardize Text Formatting
Inconsistent capitalization (e.g., "new york", "New York", "NEW YORK") makes sorting and filtering a nightmare. Standardize your text using Google Sheets' built-in text functions:
=PROPER(A1)to capitalize the first letter of each word.=LOWER(A1)to force all lowercase.=UPPER(A1)to force all uppercase.
Step 4: Delete Empty Rows and Columns
Empty rows interrupt data ranges, causing pivot tables and charts to fail. Manually scrolling and deleting hundreds of blank rows is tedious. To solve this instantly, use the CleanSheet add-on to bulk-delete every empty row in your active sheet with a single click.
Step 5: Fix Date Formatting
Dates imported from CSVs often enter Google Sheets as plain text rather than actual date values. Select your date column, go to Format > Number > Date to ensure they are universally recognized by the spreadsheet engine.
Frequently Asked Questions
What order should I follow when cleaning up a messy Google Sheet?
Remove duplicate rows first, then trim whitespace, standardize text casing, delete empty rows and columns, and finally fix date formatting. Removing duplicates first prevents you from wasting time formatting rows you're about to delete anyway.
Why does Remove Duplicates sometimes delete the wrong row?
Google Sheets' built-in Data > Data cleanup > Remove duplicates tool always keeps the first occurrence and deletes later copies, with no way to choose which row survives or preview the result first. If you need control over which duplicate is kept, use CleanSheet's "Keep First Duplicate" rule with a preview step before anything is deleted.
How do I fix dates that were imported as text instead of real dates?
Select the affected column, go to Format > Number > Date. If the values still show as left-aligned text after that, they weren't recognized as valid dates — use =DATEVALUE(A1) in a helper column to convert them, then paste the results back as values.