If you are managing an email newsletter signup list, aggregating survey responses from Google Forms, or combining multiple sales databases, you will inevitably end up with duplicate rows of data. Having the same client listed three times will ruin your metrics, inflate your counts, and result in you sending embarrassing duplicate emails to the same person.
Historically, finding and deleting duplicates required writing complex UNIQUE() formulas or relying on conditional formatting to highlight duplicates in red so you could delete them manually. Both methods are slow and tedious. Fortunately, Google Sheets now includes a dedicated, one-click “Data Cleanup” tool that instantly purges duplicates across thousands of rows without requiring a single formula.
Step 1: Select Your Data Range
Before you run the tool, you must tell Google Sheets exactly where to look.
- Click and drag to highlight all the columns and rows containing your data.
- Alternatively, if you want to scan the entire spreadsheet, simply click the empty gray rectangle in the very top-left corner of the sheet (above Row 1 and to the left of Column A) to select everything instantly.
Step 2: Access the Data Cleanup Tool
- With your data selected, look at the top menu bar and click on Data.
- Scroll down the menu, hover over Data cleanup, and select Remove duplicates.
Step 3: Configure the Deletion Parameters
A “Remove duplicates” dialogue box will appear in the center of your screen. This is where you set the rules for what constitutes a “duplicate.”
- Data has header row: If your very first row contains titles (like “First Name”, “Email Address”, “Phone Number”), you must check this box. This prevents Google Sheets from accidentally comparing your headers against your actual data.
- Columns to analyze: This is the most critical setting. By default, all columns are checked. This means a row is only considered a duplicate if every single piece of data across every column perfectly matches another row.
Advanced Tip: Deleting Based on a Unique Identifier
Let’s say a customer filled out your form twice. The first time they used their work phone number, and the second time they used their cell phone number. Because the phone numbers are different, Google Sheets will not consider them duplicates if you scan “All Columns.”
To fix this, you should scan based on a Unique Identifier, like an Email Address.
- In the “Remove duplicates” dialogue box, click Unselect all.
- Check only the box next to your unique identifier column (e.g., Column B – Email Address).
- Click the green Remove duplicates button.
Google Sheets will instantly scan the entire document, delete any row that shares an email address with a previous row (keeping only the very first instance it finds), and display a pop-up summarizing exactly how many rows were purged and how many unique rows remain.