The fastest way to remove duplicates in Excel

Select the column that contains duplicates, then use the Remove Duplicates feature built into Excel. On Windows, go to the Data tab and click Remove Duplicates. On Mac, the same button is in the Data tab. Excel will delete the duplicate rows and keep one copy of each entry. This works on the entire column or just the cells you select.

The feature compares the values in your selected column and removes exact matches. If you have "Smith" and "smith" in the same column, Excel treats them as different entries because of the capital letter — it will keep both. If that matters for your work, you may need to clean up the capitalization first.

This method is permanent once you click the button, so save a copy of your spreadsheet before you start if you want to keep the original.

Key Takeaways

  • The Remove Duplicates button in the Data tab deletes duplicate rows and keeps one copy of each entry automatically.
  • Excel's duplicate removal is case-sensitive, so "Smith" and "smith" are treated as different values.
  • You can remove duplicates from a single column or multiple columns at once by selecting them before you start.
  • The deletion is permanent, so save your file before using this feature if you need to keep the original data.
  • If you want to see which rows are duplicates without deleting them, use conditional formatting or a filter instead.

How to select the right column before removing duplicates

Click the column header letter (A, B, C, and so on) to select the entire column. If you only want to remove duplicates from part of the column, click the first cell with data, then hold Shift and click the last cell you want to include. This selects just that range.

If your data has headers — like a row at the top that says "Name" or "ID" — make sure to include the header row in your selection. Excel will ask you whether the first row contains headers, and if you tell it yes, Excel will not treat the header as a duplicate even if another row has the same text.

What happens when you remove duplicates from multiple columns at once

You can select more than one column before clicking Remove Duplicates. When you do, Excel looks for rows where all the selected columns match. For example, if you select columns A and B together, Excel will only delete a row if both the value in column A and the value in column B are identical to another row.

This is useful when you have data like a customer list where the same first name appears many times, but you only want to remove rows where both the first name and last name are exactly the same. Select both columns, and Excel will keep one copy of each unique combination.

Using filters to find duplicates without deleting them

If you want to see which entries are duplicates before you delete anything, use the AutoFilter feature instead. Select your column, go to the Data tab, and click AutoFilter. A dropdown arrow appears at the top of each column. Click the arrow in your column and uncheck Blanks if you have empty cells, then look through the list to spot repeated values.

This method lets you review the data first and decide which duplicates to keep. You can also sort the column alphabetically, which groups identical entries together and makes duplicates easier to spot by eye.

Fixing case sensitivity issues before removing duplicates

If your column has the same word spelled different ways — like "Smith", "smith", and "SMITH" — Excel's Remove Duplicates feature will keep all three because it sees them as different. To fix this before removing duplicates, use the UPPER or LOWER function to convert everything to the same case.

Create a helper column next to your original data. In the first cell of the helper column, type =UPPER(A1) if your original data is in column A. Press Enter, then copy this formula down to every row with data. Now you have a column with all entries in capital letters. Copy this helper column, then use Remove Duplicates on it. Delete the original column and rename the helper column to replace it.

Recovering data after you remove duplicates

If you remove duplicates and then realize you made a mistake, press Ctrl+Z (Windows) or Command+Z (Mac) when ready to undo the action. This works as long as you have not closed the file or performed other actions since the deletion.

If you have already closed the file, you cannot undo the deletion. This is why saving a backup copy before you remove duplicates is the safest approach. Keep the backup file in a separate folder so you do not accidentally overwrite it.

When to use a pivot table instead of removing duplicates

If you need to count how many times each entry appears rather than just delete the duplicates, a pivot table is faster than the Remove Duplicates feature. A pivot table summarizes your data without changing the original column. Select your data, go to the Insert tab, and click Pivot Table. Drag your column name into the Rows area and also into the Values area. Excel will show you each unique entry and how many times it appears.

Pivot tables are also useful when you want to keep the original data intact while exploring it. The pivot table is a separate sheet, so your original column stays unchanged.

Frequently Asked Questions

Does Remove Duplicates delete the entire row or just the duplicate cell?

It deletes the entire row. If you have data in columns A, B, and C, and rows 2 and 5 are duplicates in column A, the Remove Duplicates feature will delete all the data in row 5 — not just the value in column A. Make sure you understand what data is in each row before you use this feature.

Can I remove duplicates from a column without affecting other columns?

No. The Remove Duplicates feature always deletes entire rows, which means it removes data from all columns in that row. If you only want to remove duplicates from one column and keep the rest of the row intact, you will need to use a different method like sorting and manually deleting, or using a helper column with a formula.

What if I have blank cells in my column — will they be treated as duplicates?

Yes. If you have multiple empty cells in your column, Excel treats them as identical values. The Remove Duplicates feature will keep one blank cell and delete the others. If you want to keep all the blank cells, you can manually add a unique identifier to each one before removing duplicates, then delete the identifiers afterward.

Can I undo Remove Duplicates after I close the file?

No. Once you close the file, you cannot undo the deletion. The undo history is cleared when you close. This is why saving a backup copy of your spreadsheet before you remove duplicates is important — it gives you a way to recover the original data if something goes wrong.

Does Remove Duplicates work the same way on Google Sheets?

Google Sheets has a similar feature called Remove duplicates in the Data menu, but the steps are slightly different. In Google Sheets, select your data range, go to Data, click Data cleanup, then Remove duplicates. Google Sheets will show you how many duplicate rows it found before you confirm the deletion.