Yes. To remove everything but duplicates in Excel, count how often each value or row occurs, keep records with a count greater than 1, and copy those records to a new range or sheet. Excel’s Remove Duplicates command does the opposite: it removes extra copies and keeps one instance.
Decide what counts as a duplicate
Before changing the data, decide which part of a record determines whether it is repeated. If a list contains product names in one column, you may want to keep every row whose product name appears more than once. If the data contains several columns, you might mean rows that match across every column, or rows that share a particular ID even though other fields differ. Those are different tests and can produce different results.
For example, suppose a list contains a customer ID, name, and city. If the ID is the key, two records with the same ID count as duplicates even if one city is misspelled. If you compare the entire row, those records are different. Likewise, if you compare only the name, two different customers with the same name may be treated as duplicates. Choose the key based on what the data represents, not just on which columns look similar.
- Repeated value: A cell’s value appears elsewhere in the selected column.
- Repeated key: A value such as an order number or email address appears more than once, and you want to keep the whole associated record.
- Repeated row: Every field in one row matches the corresponding fields in another row.
Also decide whether you want every occurrence of a repeated item or only one representative row for each repeated item. “Keep duplicates” usually means keeping all occurrences—for example, both rows containing Apple—not reducing them to a single Apple row.
Find repeated values with a helper column
A helper column is a dependable method because it makes the test visible before you remove anything. In this example, values are in A2:A8: Apple, Pear, Apple, Plum, Pear, Pear, and Kiwi. The repeated values are Apple and Pear; Plum and Kiwi occur once.
With values in A2:A8, enter this formula in B2 to count the occurrences of the value on the same row:
=COUNTIF($A$2:$A$8,A2)
Fill the formula down through B8. The results are 2 for each Apple, 3 for each Pear, and 1 for Plum and Kiwi. A count greater than 1 marks a row to keep. The dollar signs lock the full list range as you fill the formula down, while A2 changes to A3, A4, and so on.
To make the result easier to review, add a label in another helper column. With the original values in A2:A8 and their counts in B2:B8, enter this in C2 and fill down:
=IF(B2>1,"Keep","Remove")
You can then filter the label column to show Keep. In Excel for Microsoft 365 on Windows, select the data range and choose Data > Filter, or format the range as a table with Home > Format as Table. Use the arrow on the helper-column heading to select Keep. Copy the visible rows to a new sheet or area. This preserves every occurrence of repeated values while leaving the original list intact for checking.
Return matching records with FILTER
If you have Microsoft 365 or Excel 2021, the FILTER function can return the kept records in a separate location without manually copying filtered rows. It is often convenient to keep the helper count because it shows exactly why each record qualifies.
With values in A2:A8 and their counts in B2:B8, enter this formula in an empty cell outside the source data:
=FILTER(A2:A8,B2:B8>1,"No duplicates")
The result spills into the cells below the formula and returns Apple, Pear, Apple, Pear, and Pear, in their original order. The singleton values do not appear. Leave enough blank space for the results to spill; otherwise Excel displays a spill error. If no values qualify, the formula displays No duplicates.
To return entire records rather than just the key, make the first argument the full record range. For instance, with names in A2:A8 and cities in B2:B8, and counts for the name key in C2:C8, use the full A:B range as the output range. The rows returned will retain both the name and its city. FILTER is available in Microsoft 365 and Excel 2021, including Excel for the web; older perpetual versions may not support it. The helper-column and filter method works as an alternative.
Keep rows that match across every column
When a duplicate means that all fields in a row match, count combinations of columns rather than counting just one key. For a small table with data in A2:B9, where both columns must match, put this formula in C2 and fill it down through C9:
=COUNTIFS($A$2:$A$9,A2,$B$2:$B$9,B2)
COUNTIFS counts rows where the value in column A and the value in column B both match the current row. A result greater than 1 means that the complete A:B combination occurs more than once. After filling down, filter column C to show values greater than 1. Copy the corresponding rows to a new location if you want to retain all copies.
If the duplicate rule depends on three or more columns, add another range-and-criteria pair to COUNTIFS for each field that must match. Keep the compared ranges the same size. For example, if a third field in column C also has to match, include the C range and the current row’s C value as another pair. If instead only column A is the key, use COUNTIF on column A and copy the entire matching rows; do not include the other fields in the duplicate test.
Remove singleton rows without losing the source
Filtering to counts greater than 1 and copying the results is usually safer than deleting rows from the original data. It gives you a clean output while preserving the source in case the duplicate definition needs adjustment. Check the copied result before replacing or deleting anything, especially if the data is important or has formulas, notes, or formatting tied to its original location.
If you need the original list itself to contain only repeated items, first create and verify the extracted result. Then replace the source with the verified output if appropriate. Another approach is to filter the helper count to 1 and remove those singleton records, but take care to delete complete data rows rather than only cells in the key column; deleting cells alone can shift values out of alignment. Keeping an untouched backup sheet is a sensible precaution.
When the source is an Excel table, adding a helper-column formula to the table generally fills that calculated column for its data rows. Table filter arrows also make it straightforward to show only counts above 1. If new records are added later, confirm that the helper formula and any output range include them. A table can make the source easier to maintain, but it does not change the meaning of the duplicate test.
Why Remove Duplicates is not the right command
Data > Remove Duplicates is designed to leave one copy of each item or record. For a list such as Apple, Pear, Apple, it keeps one Apple and one Pear; it does not keep both Apple rows while discarding Pear if Pear appears only once. That makes it useful for creating a distinct list, but unsuitable for extracting every occurrence of repeated values.
The command lets you choose which columns to compare. Selecting only a key column treats records with the same key as duplicates; selecting all relevant columns tests the combination of those fields. It also removes records from the selected data, so work on a copy if you are unsure. To keep only repeated occurrences, use a count-and-filter method instead. Conditional Formatting can highlight duplicates, but highlighting is visual only: it does not extract or remove rows.
Common issues and useful checks
- Extra spaces: “Pear” and “Pear ” can appear identical but may not behave as the same clean text value. Check for accidental leading or trailing spaces and clean the source if needed.
- Blank cells: Repeated blanks can be counted as repeats. If blanks should not qualify, exclude them from the output condition or remove blank records before counting.
- Capitalization: COUNTIF and COUNTIFS are not case-sensitive. Apple and APPLE are normally counted together. If case differences matter, a different comparison method is required.
- Similar-looking text: A number stored as text may not behave like a numeric value in every comparison. Check the cell format and underlying value when an apparent duplicate is missed.
- Wildcards: COUNTIF treats asterisks and question marks in criteria as wildcard characters. If those symbols are literal parts of the data, verify the counts rather than assuming the formula is comparing plain text literally.
- Changing source data: Formula results update when source values change, so the extracted list may change too. If you need a fixed snapshot, copy the results and use Paste Special > Values.
For a quick audit, sort or filter the helper counts and compare a few repeated groups against the original data. This can reveal a wrong key choice, inconsistent spelling, or a range that stopped short of the last row. It is especially worthwhile before using the result for reporting or importing into another system.
Frequently Asked Questions
Does Excel have a button to keep only duplicates?
Not as a direct counterpart to Remove Duplicates. Use COUNTIF or COUNTIFS to identify rows with a count above 1, then filter and copy those records. In Microsoft 365 or Excel 2021, FILTER can return them to another range.
How do I keep duplicates but remove unique values?
Count each key with a helper formula, filter for counts greater than 1, and copy the visible rows. A count of 1 identifies a unique value; a count above 1 identifies every occurrence in a repeated group.
Can I keep duplicate rows based on one column only?
Yes. Count occurrences in that key column, then use the resulting count to filter the full record range. All rows with a repeated key will remain in the output even if other columns differ.
Will Remove Duplicates preserve every repeated copy?
No. It removes extra copies and retains one instance for each distinct item or selected column combination. Use a helper count when you need to keep all repeated occurrences.
Can I do this in Excel for the web or on a Mac?
Excel for the web supports the helper-column approach and, in supported versions, FILTER. The Windows ribbon paths described above may be arranged differently on Mac or in the web interface, but the core counting logic is the same. Use the available Data filter controls to show counts greater than 1.
Related
- Excel: Protect a Sheet but Keep Slicers Usable for Users
- Excel Protect Sheet but Allow Data Refresh?
- Excel Protect Sheet but Allow Filtering: How to Set It Up
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Jira Sync: Methods, Setup, and LimitsGoogle Sheets does not include a built-in, always-live Jira sync, but you can connect the two with a…
- Goal Seek in Excel: Step-by-Step InstructionsTo use Goal Seek in Excel for Microsoft 365 on Windows, select Data > What-If Analysis > Goal…
- How to Insert a Line in Microsoft Word: 5 Easy WaysTo insert a quick horizontal line in Word, type three hyphens on an otherwise blank paragraph and press…
- Excel: How to Count Text vs. Numbers in a Range CorrectlyWith values in A2:A20, count numeric cells with =COUNT(A2:A20) and count cells whose values are text with =SUMPRODUCT(--ISTEXT(A2:A20)).…
- Excel Drop-Down Keyboard Shortcut: Open a List QuicklyIn Excel for Microsoft 365 on Windows, select a cell that has a drop-down list and press Alt+Down…
- How to Insert a Dynamic Checkbox in Microsoft WordTo insert a dynamic, clickable checkbox in Word, show the Developer tab, then choose Developer > Check Box…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.