Cleaning up duplicate rows is one of those small tasks that can make your spreadsheets far more reliable and easier to analyze. Whether you’re using Google Sheets or Microsoft Excel, there are several practical ways to find and remove duplicates—either by deleting them in place or by generating a clean, deduplicated list. Below you’ll find clear, step-by-step methods suited for everyday data cleanup and reporting.
Remove Duplicate Rows in Google Sheets: Methods
The quickest way in Google Sheets is the built-in Remove duplicates tool. First, select your data range, including the header row if you have one. Then go to Data > Data cleanup > Remove duplicates. In the dialog, choose which columns define a “duplicate” (select all columns to remove fully identical rows), tick “Data has header row” if applicable, and click Remove duplicates. Sheets will show how many rows were removed and how many unique rows remain. Because this changes your data in place, consider working on a copy or duplicating your sheet beforehand.
If you prefer a dynamic approach that updates automatically as new data arrives, use the UNIQUE function. In a new sheet or empty range, enter a formula like =UNIQUE(A2:D) to return unique rows from columns A through D. If you need the results sorted, wrap it with SORT, for example =SORT(UNIQUE(A2:D), 1, TRUE) to sort by the first column ascending. Once you’re satisfied with the output, you can copy the results and paste them as values to “fix” the deduplicated list, or simply keep the formula-driven output for always-up-to-date reports.
To audit before deleting, you can highlight duplicates with conditional formatting or use a helper column. For one-key duplicates (e.g., by email), select that column, then Format > Conditional formatting, and set a custom formula like =COUNTIF($A$2:$A,$A2)>1, then choose a highlight color. For whole-row duplicates, create a helper column that combines columns (e.g., =TEXTJOIN(" | ", TRUE, A2:D2)), apply conditional formatting with COUNTIF on that helper, and filter by color to remove unwanted rows. For very large datasets or repeatable workflows, consider a pivot table that lists unique combinations or use Apps Script to automate TRIM/LOWER normalization before deduplication.
Remove Duplicate Rows in Microsoft Excel: Steps
For most cases, Excel’s Remove Duplicates command is the fastest option. Select your range (include headers), go to the Data tab, and click Remove Duplicates. In the dialog, select which columns should match to count a row as a duplicate—choose all columns for full-row matches—and ensure “My data has headers” is checked if appropriate. Excel will remove duplicates in place and show a summary of how many were found and removed. It’s good practice to save or copy your data first, especially if you need to review changes later.
If you want a formula-based, dynamic list, Excel for Microsoft 365 supports the UNIQUE function. Enter =UNIQUE(A2:D) to return unique rows from A through D, or combine with SORT like =SORT(UNIQUE(A2:D), 1, 1) for a sorted result. On older Excel versions, you can use Advanced Filter: select your range, go to Data > Advanced (under the Sort & Filter group), choose “Copy to another location,” tick “Unique records only,” and specify a destination. Alternatively, mark first occurrences with a helper formula (for example, COUNTIFS across key columns) and filter to keep only the first instances.
For robust, repeatable cleaning—especially when importing files frequently—use Power Query. Select your range and create a table (Ctrl+T), then Data > From Table/Range to load it into Power Query. Use Home > Remove Rows > Remove Duplicates and choose the columns that define uniqueness. Before deduplication, you can normalize text by selecting columns and applying Transform > Format > Trim and Clean (and even lowercase/uppercase) to avoid duplicates caused by extra spaces or inconsistent casing. Click Close & Load to push the cleaned, deduplicated table back to Excel; refresh it anytime your source data changes.
Whether you prefer one-click tools or dynamic formulas, both Google Sheets and Excel offer reliable ways to purge duplicates and keep your dataset tidy. Start with the built-in Remove Duplicates for quick fixes, then move to UNIQUE, Advanced Filter, or Power Query when you need repeatable, auditable workflows. A quick normalization pass—trimming spaces and standardizing case—will make every deduplication step more accurate.