How to Keep Duplicate Values in Excel Without Losing Data

To keep repeated entries in Excel, do not run Data > Remove Duplicates on the data you need to preserve. Excel allows duplicates by default. To find them without deleting anything, use conditional formatting or a helper formula; if you want a separate list containing only repeated entries, filter the helper results and copy those rows to a new sheet. Make a copy of important data first, especially if you are unsure whether you want to keep every row or keep only values that occur more than once.

What You’ll Need

An Excel workbook with the data you want to check. The examples below use Microsoft Excel for Microsoft 365 on Windows.
A clear idea of what counts as a duplicate: the same value in one column, or a repeated combination of values across several columns.
Optionally, a blank helper column and a new worksheet if you want to isolate duplicate rows without changing the original list.

Excel for Mac has similar ribbon commands, though some dialogs and keyboard shortcuts differ. Excel for the web also supports common filtering and formatting features, but the placement of some commands can vary slightly.

Step-by-Step

Make a safe copy of the data. If the workbook is important, use File > Save As to create a copy before cleaning or reorganizing it. You can also duplicate the worksheet: right-click its tab, choose Move or Copy, select Create a copy, and confirm. In Excel for Mac, use the sheet-tab context menu and the equivalent Move or Copy option. This gives you an unchanged version if you later discover that a list was altered.
Decide whether you want to keep all rows or extract only duplicates. If your goal is to retain every occurrence, leave the source list alone and use highlighting or a helper column to inspect it. If you want a new list of records that occur more than once, identify those records and copy the matching rows elsewhere. These are different tasks: marking a duplicate does not delete it, while removing unique records from a copy does not affect the original.
Highlight duplicates without changing the values. Select the cells you want to check, such as A2:A100 if the list is in column A and row 1 contains a heading. Choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a format, and click OK. Excel highlights every cell whose value appears more than once in the selected range. This is only visual formatting: the cell contents and number of rows stay the same. To remove the highlighting later, select the range and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
Add a helper formula when you need a filterable result. Suppose your values are in A2:A8, with a heading in A1. Put a heading such as Duplicate? in B1. With the values in A2:A8 and the first value in A2, enter =AND(A2<>"",COUNTIF($A$2:$A$8,A2)>1) in B2, then fill it down to B8. The formula returns TRUE for each nonblank value that occurs more than once and FALSE for a value that appears only once. For example, if A2:A8 contains Apple, Pear, Apple, Lime, Pear, Pear, and a blank, the formula marks both Apples and all three Pears TRUE; Lime and the blank are FALSE. The dollar signs keep the full search range fixed as you fill down. In a regional Excel setup that uses semicolons as separators, replace the commas with semicolons.
Filter to show the repeated entries. Select the header row and data, then choose Data > Filter. Open the filter arrow in the helper column and select only TRUE. The matching rows remain in the worksheet; other rows are temporarily hidden, not deleted. If you want a separate duplicate-only list, copy the visible rows and paste them into a new worksheet, then check that the copied row count is what you expect. Keep the original data intact until you have verified the new list.
Mark only the later occurrences, if that is what you mean by duplicate. Sometimes you want to keep the first appearance unmarked and identify only subsequent repeats. With values in A2:A8, enter =AND(A2<>"",COUNTIF($A$2:A2,A2)>1) in B2 and fill down. In the example Apple, Pear, Apple, Lime, Pear, Pear, the formula returns TRUE for the second Apple and the second and third Pears: three TRUE results. It returns FALSE for the first Apple and first Pear, Lime, and the blank. Unlike the previous formula, the range grows as it moves down, counting how many times the current value has appeared so far.
Check whether a duplicate is a full-row match or just a repeated field. A repeated surname, product name, or invoice amount does not necessarily mean the entire record is duplicated. If a record is defined by two fields, such as customer name and order number, make a helper key in an unused column before checking duplicates. With names in A2:A8 and order numbers in B2:B8, enter =A2&"|"&B2 in C2 and fill down. Then use the duplicate-checking formula on C2:C8. The separator helps distinguish combined pairs, and you can review the original columns before taking action. For complex data, checking each relevant field separately may be safer than treating one matching column as a duplicate record.
Keep the duplicate rows and remove only what you have confirmed is unnecessary. If the task is to keep repeated records, leave the filtered rows in place or copy them to a new sheet. Do not click Data > Remove Duplicates on the source range: that command removes repeated rows and keeps one occurrence. If you use it on a separate working copy to remove unique records indirectly, first filter the helper column, select only the intended visible rows, and verify the result before replacing anything in the original workbook.

Common Mistakes

Choosing Remove Duplicates when you mean keep duplicates. The command under Data > Remove Duplicates is designed to delete repeated entries, not to mark or preserve all instances. It also removes rows from the selected range, so selecting multiple columns can affect whole records. Use conditional formatting or a helper column if you only need to inspect repetitions.

Checking the wrong range. If the formula searches only A2:A50 but your list continues to A200, duplicates outside the range will be missed. Include all current records, but do not include unrelated headings or other lists. If the list grows regularly, consider converting it to an Excel table with Home > Format as Table; table references can make formulas easier to maintain as rows are added.

Confusing repeated values with repeated records. Two orders can have the same date or amount and still be different orders. Decide which fields identify a record before filtering or deleting anything. A single-column check answers whether that value repeats, not whether every field in the row is identical.

Overlooking spaces or inconsistent text. Apple and Apple followed by a space may not behave as the same entry in every comparison you make. Text imported from another system may contain leading or trailing spaces, nonprinting characters, or inconsistent capitalization. Inspect suspicious values and clean a copy before deciding that a result is wrong.

Assuming a filter deletes hidden rows. Filtering hides rows temporarily. Clearing the filter brings them back. If you copy results to a new sheet, check that only the visible matching rows were copied; do not overwrite the source list until the copy is confirmed.

Tips

Keep a record of the original row count before filtering. If you are extracting duplicates, count the visible results and compare them with the TRUE results in the helper column. This simple check can reveal a missed range, an accidental heading in the selection, or a copied row that was hidden.

Use conditional formatting for a quick visual scan, and use a helper formula when you need a repeatable test, a filter, or a count. The conditional-formatting rule is convenient for review, but the helper column makes it easier to explain why each row was included and to sort or filter the result.

If duplicate checks happen often, name your helper column clearly, such as Repeat value? or Repeat order key?, and leave a note explaining which fields define a duplicate. Avoid deleting the helper column until you have finished checking the extracted list. When the data is an Excel table, enter the formula in its first data row; Excel generally fills the calculated column for the remaining table rows.

Before making final changes, clear filters and scan the complete source list. If the workbook is shared, tell other editors whether the helper values are temporary and whether the original rows must remain untouched. A clear distinction between the original data and a duplicate-only output reduces the risk of someone treating a filtered view as a cleaned source.

Troubleshooting

No cells are highlighted. Confirm that the selected range includes the values, not just the heading, and that the rule is set to Duplicate rather than Unique. Check whether the supposed matches are actually identical; extra spaces, different characters, or numbers stored as text can make entries appear alike while Excel treats them differently.

The formula shows an error or will not accept the commas. Check that you copied the formula exactly, including the parentheses and quotation marks. Some regional settings use semicolons between arguments, so change each comma to a semicolon if Excel expects that separator. Also make sure the formula is entered in a normal worksheet cell and that the range matches your data.

Rows you expected to match return FALSE. Verify that the helper formula was filled down and that the absolute range still covers every row. Compare the values directly, and inspect for spaces or inconsistent data types. If blank cells are meaningful entries in your list, remove the A2<>”” condition from the formula; otherwise it intentionally prevents empty cells from being counted as duplicates.

You accidentally removed duplicates. If the change just happened, use Undo immediately—Ctrl+Z on Windows or Command+Z on Mac. Undo may not be available after closing the workbook or after other actions. If it is unavailable, restore a saved copy, use version history if the workbook is stored in OneDrive or SharePoint, or retrieve a backup. Do not save over the only remaining copy until you have checked recovery options.

Frequently Asked Questions

Does Excel allow duplicate values by default?
Yes. Excel allows the same value to appear in multiple cells. Duplicates are removed only if you use a command, formula, macro, or workflow that changes them; ordinary entry does not automatically remove them.

How do I keep duplicates but remove unique values from a working list?
Use a helper formula that returns TRUE when a value occurs more than once, filter to TRUE, and copy the matching rows to a new worksheet. Keep the original list as a backup, because this process creates a duplicate-only result rather than changing the meaning of the source data.

Will conditional formatting delete or change my values?
No. The Duplicate Values rule changes how matching cells look, not their contents. It is safe for highlighting, though you should clear the rule if you no longer want the visual formatting.

Can Excel highlight duplicates across multiple columns?
Yes, but first decide what counts as a match. Selecting several columns for a built-in rule can identify repeated cell values within the selection, which is not always the same as identifying identical rows. To check a combination of fields, create a helper key from those fields and check that key for repeats.

What is the difference between duplicate values and duplicate rows?
A duplicate value repeats in a particular cell or field, such as a product code. A duplicate row repeats the full record, or the set of fields you have chosen as its identity. Matching one value alone does not prove that two whole records are duplicates.

Can I keep the first occurrence and mark only later repeats?
Yes. Use the running COUNTIF formula shown above. It returns FALSE for the first appearance and TRUE for each later occurrence, letting you filter or format later repeats without removing any rows.

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.