No Remove Duplicates Option in Excel? Find It

How-to basics · Updated November 21, 2026

Excel for Microsoft 365 on Windows has a Remove Duplicates command on the Data tab: select a cell in your list, then choose Data > Remove Duplicates. If you cannot see it, the ribbon may be collapsed or the command may be hidden in a compact group; Excel’s search box can usually locate it. The command removes duplicate rows from the selected range, so make a copy first if you might need the original data.

Where to find Remove Duplicates in Excel

In the standard Windows ribbon, select a cell in the data range and open Data > Data Tools > Remove Duplicates. In some window sizes or ribbon layouts, the Data Tools group may be compressed, so its commands can appear as smaller icons or inside a menu. Maximize the Excel window or expand the ribbon to check. You can also use the Search box at the top of Excel, often called “Tell Me”: type Remove Duplicates and choose the matching command.

The command is available for ordinary cell ranges and Excel tables. If you select a cell inside a table, Excel uses the table’s columns when it opens the removal dialog. You can also select a range first. When a selection contains more than one column, check carefully which columns are selected in the dialog before confirming.

There is no dedicated, standard keyboard shortcut that directly runs Remove Duplicates in every Excel setup. Keyboard access keys can vary with the interface and configuration, so searching for the command is often more dependable than relying on a memorized key sequence.

Why the command may seem to be missing

Most cases are interface or workbook issues rather than a missing Excel feature. Work through these checks before trying a different method:

  • The ribbon is collapsed. Expand the ribbon using the ribbon display controls near the upper-right corner, or maximize the Excel window. A narrow window can hide the full names of commands.
  • The Data tab is not visible. Check whether the ribbon is simplified or customized. If the tab itself was removed through ribbon customization, restore the default tabs in Excel’s ribbon settings.
  • You are using the search box. Search for the exact command name, Remove Duplicates, rather than “deduplicate” or “remove repeated cells.”
  • The worksheet is protected or the file is read-only. Excel may prevent edits while protection is active or when you lack permission to change the file. Check the Review tab and the file’s editing status. Only unprotect a sheet if you are authorized and have the password when one is required.
  • You are looking at another app or a limited interface. Confirm that the workbook is open in Excel, not a preview window or a different spreadsheet program. Web and mobile interfaces do not always place commands in the same location as desktop Excel.
  • The selected data is part of a special object. If the command is unavailable in a particular selection, click an ordinary cell in the list and try again. For a PivotTable, for example, remove duplicates from the source data rather than trying to alter the PivotTable’s summarized layout.

When the ribbon is customized, the command might not be displayed even though Excel supports it. Search is a useful diagnostic: if Excel offers Remove Duplicates in search, the feature exists in that installation and the issue is likely where the ribbon displays it or whether the current sheet permits changes.

How to remove duplicates safely

Before using the command, save the workbook or copy the source data to another sheet. Remove Duplicates changes the data in place; it is not just a filter or a temporary view. You can usually undo the operation immediately with Ctrl+Z on Windows, but do not rely on undo as your only backup, especially if you continue editing afterward.

  1. Click a cell in the list, or select the full range you want Excel to process. Include all columns that belong together in each record.
  2. Choose Data > Remove Duplicates.
  3. If the first row contains field names, select My data has headers. Verify the checkbox matches the actual first row.
  4. Choose the columns Excel should compare. Select every field that defines a duplicate record, or only the fields that define a repeated item for your task.
  5. Choose OK. Excel reports how many duplicate values it removed and how many unique values remain.

Suppose a sales list has columns for Order ID, Customer, and Amount. If an entire order row is duplicated, compare all three columns. If the aim is to retain only one row per Order ID, compare only Order ID. The second choice can discard another row with that same ID even if its customer or amount differs, so confirm that this is what you intend.

What Excel considers a duplicate

Remove Duplicates compares the columns you check in the dialog. When several columns are checked, a row is considered a duplicate of another row when the selected field values match across those columns. Unchecked columns do not protect a row from removal. This is why selecting only a name column can remove a second person with the same name, even if the two records have different email addresses or account numbers.

The command keeps one occurrence and removes the other matching rows from the selected data. It does not combine their information, calculate totals, or decide which record is more accurate. If two records share a customer ID but one has a newer address, removal alone will not intelligently retain the newer address; sort or review the records first and define a rule for which one to keep.

Headers matter because they tell Excel whether the top row is labels or data. If a real data row is mistakenly treated as a header, it will not be compared like the other rows. If column headings are mistakenly treated as data, they may be included in the comparison. Also review spelling, extra spaces, blank cells, and inconsistent formats before drawing conclusions from the result. Values that look alike on screen can have underlying differences, and cleanup may be needed before a reliable comparison.

Check the data before and after removal

Removing duplicates is permanent enough to deserve a quick audit, especially in a customer, inventory, or transaction list. Make a working copy, then compare the number of records before and after. Check a few examples of the rows Excel removed and confirm that the chosen comparison columns match your definition of “duplicate.”

If you want to identify repeated entries without deleting anything, use a helper column. With values in A2:A8, put this formula in B2 and fill it down: =COUNTIF($A$2:A2,A2)>1. In this example, the values in A2:A8 are Apple, Pear, Apple, Orange, Pear, Pear, and Plum. The formula returns TRUE for the second Apple and for the second and third Pear; it returns FALSE for each first occurrence and for Orange and Plum. This flags later repetitions in the current order, leaving the source list intact. To check complete records, a helper formula needs to represent the fields that define a duplicate, rather than checking just one column.

A helper column is particularly useful when you need to inspect records before choosing which copy to keep. Filter the helper results to TRUE, review those rows, and decide whether any contain useful differences. Remember that a simple COUNTIF check on one column answers a narrower question than comparing multiple fields in Remove Duplicates.

Alternatives when you want a non-destructive result

If you have Microsoft 365 or Excel 2021 and want a separate list of unique values, use the UNIQUE function rather than deleting rows. With source values in A2:A8, enter =UNIQUE(A2:A8) in an empty cell. For the example list above, the result is Apple, Pear, Orange, and Plum, in the order each first appears. The formula spills results into neighboring cells and updates when its source data changes. It requires enough empty space for the results to spill.

UNIQUE returns distinct items or rows; it does not remove the original records. For a multi-column range, it can return distinct rows across the selected columns. If you need a permanent cleaned copy, copy the resulting spill range and use Paste Special > Values, or use Remove Duplicates on a separate copy. UNIQUE is not available in every older Excel version, so an older installation may show a name error for that formula.

Other useful choices include filtering the data to review it, sorting records so the preferred row appears first, or using Power Query for a repeatable import-and-clean process. Power Query is appropriate when the same source data will be refreshed regularly, but its interface and steps are different from the one-click worksheet command. For a one-time list, Remove Duplicates is usually simpler; for a reviewable or dynamic result, a helper column or UNIQUE may be safer.

Windows, Mac, and Excel for the web

These instructions describe Excel for Microsoft 365 on Windows. Excel for Mac also provides duplicate-removal functionality from the Data tab, though ribbon placement can differ by version and window size. Use the app’s command search if available, or inspect the Data tab. The Windows Ctrl+Z undo shortcut is Command+Z on Mac.

Excel for the web has a Data tab and supports removing duplicates in current versions, but the layout can differ from desktop Excel and available commands may depend on the workbook and account. If you cannot find it in the browser, open the workbook in the desktop app if you have access to it. Mobile Excel has a more compact interface, so editing and data-cleaning commands may be harder to locate; using the desktop or web app is often more practical for a large list.

Frequently Asked Questions

Does Remove Duplicates delete the whole row?

It removes duplicate rows from the selected range based on the columns checked in the dialog. It does not merely clear the repeated cell. Make a backup or duplicate the sheet if you need to preserve the original data.

How do I remove duplicates but keep the first occurrence?

Remove Duplicates keeps one occurrence and removes the other matching rows. If which occurrence remains matters, sort the records into the order you want before running the command, and check that sorting does not separate related columns.

Can I remove duplicates based on just one column?

Yes. In the dialog, select only that column. Excel then treats matching values in that field as duplicates, regardless of differences in unchecked columns. Use this only when one field alone defines a repeated record for your purpose.

Why did Excel remove a row that had other different information?

One or more comparison columns were likely selected, while the differing information was in an unchecked column. Reopen the dialog on a fresh copy and select every field that must match for two rows to count as duplicates.

Can I undo Remove Duplicates?

Usually, press Ctrl+Z immediately on Windows or Command+Z on Mac. Undo may no longer be available after other actions or after closing the workbook, so saving a copy before removal is the dependable safeguard.

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.