ThisCalculator

Everyday

How to Drop Duplicates from a Specific Location

Updated · Standard formula, shown below

This guide shows you how to remove duplicate rows or entries from a specific location in a spreadsheet or database, such as a particular column, range, or table. You’ll learn the exact steps for common tools like Microsoft Excel, Google Sheets, and SQL, so you can clean your data without affecting other parts of your dataset.

What You’ll Need

  • Access to the spreadsheet or database where your data lives (e.g., Excel, Google Sheets, or a SQL database).
  • If using a database, write permission to modify the table (or a way to create a new table).
  • Backup of your original data (recommended for any destructive operation).

Step-by-Step

In Microsoft Excel

  1. Select the range or column where you want to remove duplicates. For example, click the column header (like “A”) to select the entire column, or drag to select a specific range of cells.
  2. Go to the Data tab on the ribbon.
  3. Click “Remove Duplicates” in the Data Tools group. A dialog box will appear.
  4. Choose which columns to check. If you selected a single column, it will be checked by default. If you selected a range with multiple columns, you can uncheck any columns you don’t want to consider when identifying duplicates. For example, if you only want to deduplicate based on email addresses, uncheck all other columns.
  5. Check “My data has headers” if your selection includes the header row. This prevents the header from being treated as a duplicate.
  6. Click OK. Excel will remove duplicate rows based on the columns you selected and show a message with how many duplicates were removed and how many unique values remain.
  7. Review the result to ensure only the intended rows were removed. If it looks wrong, press Ctrl+Z to undo.

In Google Sheets

  1. Select the range or column you want to deduplicate. For example, click the column letter to select the whole column, or highlight a specific range.
  2. Click “Data” in the top menu.
  3. Choose “Data cleanup” and then “Remove duplicates”.
  4. In the dialog, select the range if it isn’t already filled in. You can also choose “Select all” or uncheck specific columns if you want to deduplicate based on certain columns only.
  5. Check “Data has header row” if applicable.
  6. Click “Remove duplicates”. Google Sheets will show a summary of how many rows were removed and how many remain.
  7. Undo if needed with Ctrl+Z (Cmd+Z on Mac).

In SQL (Database)

  1. Identify the table and columns that define a duplicate. For example, you might want to remove rows where the “email” column has the same value.
  2. Create a backup table (optional but recommended): 
    CREATE TABLE table_backup AS SELECT * FROM your_table;
  3. Write a DELETE statement that keeps only one row per duplicate group. A common method uses a subquery with ROW_NUMBER() (in databases that support it, like PostgreSQL, SQL Server, MySQL 8+): 
    DELETE FROM your_table
WHERE id NOT IN (
  SELECT MIN(id)
  FROM your_table
  GROUP BY email
);

    This keeps the row with the smallest id for each email and deletes the rest.

  4. Run the DELETE statement in your database client.
  5. Verify the result by running a SELECT to check for remaining duplicates: 
    SELECT email, COUNT(*)
FROM your_table
GROUP BY email
HAVING COUNT(*) > 1;

Common Mistakes

  • Selecting the wrong range – If you select an entire spreadsheet instead of just the column, you might remove rows that are unique in that column but have duplicates in other columns. Always specify exactly which columns define a duplicate.
  • Forgetting to include the header row – If your data has headers and you don’t check “My data has headers,” the header might be removed or treated as a duplicate.
  • Not backing up data – Removing duplicates is irreversible unless you undo immediately. Always save a copy before proceeding.
  • Ignoring case sensitivity – “John” and “JOHN” may be considered different in some tools. Check if your tool treats them as duplicates or not.
  • Using DELETE without a backup in SQL – If you delete the wrong rows, you can’t recover them unless you have a backup.

Tips

  • Use a helper column in Excel or Sheets to mark duplicates first (e.g., with COUNTIF) so you can review before deleting.
  • Sort data before removing duplicates to control which row is kept (e.g., sort by date so the most recent stays).
  • In SQL, test your DELETE with a SELECT first to see which rows will be removed. For example, run the subquery alone to preview.
  • Consider using a temporary table in SQL to hold the deduplicated data, then replace the original table.
  • For large datasets, removing duplicates can be slow; consider using a script or database tool that handles it efficiently.

Troubleshooting

  • Duplicates are not removed as expected – Check that you selected the correct columns. If you only want to deduplicate based on one column, make sure only that column is checked in the dialog.
  • The header row disappears – In Excel/Sheets, ensure the “My data has headers” checkbox is ticked. If not, undo and redo with the checkbox.
  • Data is removed from other columns – This happens when you select an entire row range. Instead, select only the columns that define duplicates.
  • SQL DELETE fails with an error – Check if your database supports the syntax. For older MySQL, you might need a different approach using a temporary table or JOIN.
  • Case sensitivity issues – If “John” and “JOHN” are considered different, use functions like LOWER() in SQL or a case-insensitive comparison in Excel/Sheets.
  • Undo doesn’t work – If you’ve made other changes after removing duplicates, you may not be able to undo. That’s why a backup is crucial.

Frequently Asked Questions

Can I remove duplicates from only one column in Excel without affecting other columns?

Yes. Select only that column (or a range that includes only that column), then use “Remove Duplicates”. Excel will keep the first occurrence and delete the entire row for duplicates, so other columns in those rows will be removed as well. If you want to keep other columns, you need to copy the unique values to a new location.

How do I remove duplicates in Google Sheets but keep the last occurrence?

Google Sheets’ built-in “Remove duplicates” always keeps the first occurrence. To keep the last, you can add a helper column with row numbers, sort by that column in descending order, then remove duplicates, and finally re-sort. Alternatively, use a script or formula to identify and delete the earlier ones.

What does “Remove Duplicates” do with blank cells?

In Excel and Google Sheets, blank cells are treated as a value. If you have multiple blank cells in the selected column, they will be considered duplicates and only one blank will remain. If you don’t want that, filter out blanks before removing duplicates.

Can I remove duplicates from a specific range in SQL?

SQL operates on tables, not ranges. To deduplicate a subset, you can create a temporary table with a WHERE clause, deduplicate that, and then replace the original table or update the original with the cleaned subset.

Is there a way to remove duplicates without deleting rows?

Yes. You can use conditional formatting to highlight duplicates and then manually review them, or use a formula to extract unique values to a new column. This is safer if you need to keep the original data intact.

Does removing duplicates affect formulas or formatting in Excel?

Yes. When you remove duplicate rows, any formulas in those rows are deleted. Formatting from removed rows is also gone. If you have formulas that reference the removed rows, they may return errors. Always check your formulas after removal.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.