Remove Duplicates in Excel by Entire Row: A Safe Guide

How-to basics · Updated November 18, 2026

To remove duplicate records in Excel, select the full data range, choose Data > Remove Duplicates, and check every column in the dialog. Excel compares the selected columns, keeps the first matching row, and removes later matches. Make a copy of the data first: removing duplicates changes the range, and the comparison depends on which columns you select.

What You’ll Need

  • An Excel workbook with the records you want to clean. These instructions are for Microsoft Excel for Microsoft 365 on Windows.
  • A clear idea of what counts as a duplicate. To compare entire rows, use every column that belongs to each record. To identify repeated IDs even when other details differ, compare only the ID column instead.
  • A backup or a duplicate copy of the worksheet if the original data may be needed later. Excel can undo the removal immediately, but saving or making other changes afterward can make recovery less straightforward.
  • Consistent columns and headers, if your data has headers. Remove merged cells in the data range before proceeding, because merged layouts can interfere with sorting and data operations.

Step-by-Step

  1. Make a working copy. Save the workbook with a new name, or copy the worksheet by right-clicking its sheet tab and choosing Move or Copy, then selecting Create a copy. A separate copy is the safest option if the records are important. You can also copy the data to a new worksheet, but make sure the copy includes every column that belongs to the records.
  2. Check the data layout. Each row should represent one record, and each column should contain one type of information, such as order number, date, or customer name. Put a single header row above the records if possible. Remove completely blank rows within the data and check that there are no unrelated notes or totals mixed into the range. Those extra rows can make it harder to select and interpret the results.
  3. Select the complete data range. Select every column and row that makes up the records, including the headers if you have them. Do not select just one column when your goal is to remove entire duplicate records: Excel needs the full row range so it can remove the matching record without leaving the remaining columns out of alignment. If the data is an Excel table, click a cell inside it; the table can be used as the data range.
  4. Open the duplicate-removal tool. On the Windows ribbon, choose Data > Remove Duplicates. If a warning asks whether to expand the selection, choose to expand it when the adjacent columns are part of the same records. If those columns are unrelated data, cancel and select the intended range yourself. Review this prompt carefully; using only part of a record can leave your worksheet inconsistent.
  5. Tell Excel whether the first row has headers. In the dialog, select My data has headers if the top row contains column names such as Date or Customer. Leave it unchecked if the first row is a real record. When headers are recognized, the dialog displays their names rather than generic column labels, which makes the next choice easier to verify.
  6. Choose every column for an entire-row comparison. In the column list, click Select All so that every field is included. Excel will remove a later row only when its values match the corresponding values in all selected columns. For example, if a row has the same customer and date but a different order number, selecting all columns keeps both rows because the complete records are not identical.
  7. Run the removal and read the result. Click OK. Excel reports how many duplicate values or rows were removed and how many unique values remain. With every column selected, interpret this as duplicate records across the selected range. Click OK again to close the result message, then inspect the worksheet rather than assuming the outcome is correct.
  8. Verify the rows that remain. Check a few known duplicates and confirm that one copy remains. Also inspect records that look similar but should be different, such as two purchases made by the same customer on different dates. Excel keeps the first matching occurrence in the current row order. If you need a particular copy to remain, arrange the rows in the desired order before running the command, or undo and repeat after sorting.
  9. Save only after checking the result. If the remaining data looks right, save the workbook. If the wrong rows were removed, use Ctrl+Z on Windows immediately, then review the selection and checked columns before trying again. On Mac, use Command+Z. If you have already closed the workbook or made later edits, restore the backup rather than relying on undo.

In Excel for Mac, the command is also available from the Data tab as Remove Duplicates; the dialog choices are substantially the same. Excel for the web also provides a Remove Duplicates command on the Data tab, although the ribbon layout can vary. If you do not see it, check the Data tab or open the workbook in the desktop app. In all versions, select the full record range and check the intended columns before confirming.

Common Mistakes

  • Selecting only one column by accident. When Excel asks how to compare records, selecting only one field means it treats rows with the same value in that field as duplicates, even if their other details differ. That can remove legitimate records. For exact whole-row duplicates, check every relevant column in the dialog.
  • Selecting all columns when you mean to deduplicate by ID. The reverse mistake is also common. If two rows share an ID but differ in another field, Excel keeps both when every column is selected. If the ID alone defines a duplicate, select the complete data range but check only the ID column in the dialog. Excel then removes later records with a repeated ID, including their other cell values.
  • Leaving out columns from the selected range. A comparison can only use columns included in the range and selected in the dialog. If a field that distinguishes records sits outside the selected range, Excel cannot use it to decide whether the rows are identical. Select the full record, not just the columns that are easiest to highlight.
  • Including unrelated adjacent data. Excel may offer to expand a selection to nearby cells. Expanding is right when those cells belong to the same records, but not when they contain separate notes or another table. Cancel if the selection expands unexpectedly, then select the correct data range explicitly.
  • Misidentifying the header row. If you mark a real record as a header, Excel will not treat it like the other records. If you fail to mark actual headers, the first row can be included in comparisons as data. Confirm the checkbox and the column names shown in the dialog before running the command.
  • Expecting formatting to define a duplicate. Remove Duplicates compares cell contents, not whether cells look alike. Matching colors or number formats do not make different values duplicates, and different formatting does not protect otherwise matching records from removal.

Tips

  • Sort first if the retained copy matters. Excel keeps the first occurrence it encounters and removes later matches. Sort by a quality or timestamp field before removing duplicates if you want to keep, for example, the newest or most complete record. Check that the sort includes the entire data range so values stay with their rows.
  • Use a table for growing datasets. If your records are an Excel table, clicking inside it helps Excel identify the table as a single data range. Tables also make headers easier to recognize. Check that the table does not include unrelated columns before opening Remove Duplicates.
  • Decide how blank cells should count. With all columns selected, rows that match across those columns can be considered duplicates even if some fields are blank. A blank in one field is not the same as a populated value, but two otherwise matching rows with the same blank fields may be removed as duplicates. Decide whether missing information makes a record uncertain before deleting it.
  • Check for invisible differences. Extra spaces, inconsistent punctuation, or slightly different spellings can make records that look identical on screen compare differently. For instance, a name with a trailing space may not match the same name without it. Clean or standardize the source data first when you suspect hidden inconsistencies, and keep a backup so you can compare the original values.
  • Use the result message as a quick check, not the only check. Excel’s summary tells you how many rows it removed, but it cannot confirm whether your definition of a duplicate was appropriate. Reviewing a few examples is especially important when several columns contain dates, codes, or optional details.

Troubleshooting

Excel removed too many records. Undo at once with Ctrl+Z on Windows or Command+Z on Mac. Reopen Remove Duplicates, make sure the full record range is selected, and check every column if you want exact row matches. If you intended to remove repeated IDs, check only the ID field while keeping the full record range selected. Do not rerun the command until the selection and comparison rule match your goal.

Excel removed no records, but rows look identical. Confirm that the apparently matching rows are included in the selected range and that all intended columns are checked. Look for trailing spaces, different punctuation, different dates or numbers, or a value that is hidden by the current column width. Formatting can make different underlying values appear alike. If the records should match after standardization, clean the data in a copy and try again.

The header names do not appear in the dialog. The header checkbox may be off, or the selected range may start below the actual header row. Cancel the dialog, select the range again from the header through the last record, and reopen the command. If there is no header row, leave the checkbox off and carefully identify the columns using the labels or positions shown.

The wrong rows seem to have been kept. Excel keeps the first occurrence in the order it processes the range. Undo, sort the complete range so the preferred record appears first, and repeat the removal. Do not sort one column by itself; that separates values from their associated record fields and can corrupt the data.

The command is unavailable or behaves unexpectedly. Make sure the worksheet is not protected in a way that prevents edits, and verify that the selection contains ordinary tabular data rather than merged cells or several unrelated blocks. If working in Excel for the web and the ribbon command is unavailable, open the workbook in desktop Excel. If an expanding-selection warning appears, choose the option that includes the complete records, not unrelated neighboring content.

Frequently Asked Questions

Does Remove Duplicates delete the whole row?

It removes duplicate records from the selected range, not just the repeated cell. To have Excel compare a whole record, select the complete data range and check all the columns that belong to it. If you select only one comparison column, Excel can still remove the later matching record across the selected range.

How do I remove duplicates based on one column but keep the entire row?

Select the complete record range, open Data > Remove Duplicates, and check only the column that defines a duplicate, such as an order ID. Excel keeps the first row for each distinct value in that column and removes later matching rows from the selected range. Sort first if you need to control which matching record is kept.

Does Excel keep the first or last duplicate?

It keeps the first occurrence in the current row order and removes later matches. If you want a particular version of a record to remain, sort the entire range so that version comes first, then remove duplicates.

Can I undo Remove Duplicates?

Yes. Use Ctrl+Z on Windows or Command+Z on Mac soon after running the command. Undo may no longer be practical after later changes or after closing the workbook, so keep a backup when the data matters.

Does Excel treat uppercase and lowercase text as different?

Remove Duplicates does not distinguish text solely by capitalization, so values such as Sam and SAM can be treated as matching in the selected columns. Other differences, such as extra spaces or different punctuation, can still prevent a match.

Can I remove duplicates without deleting the original data?

Yes. Copy the worksheet or data to another location first, then run Remove Duplicates on the copy. You can also use Data > Advanced to filter a range and copy unique records elsewhere, but that is a separate workflow and should be checked against the columns that define uniqueness.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.