Excel Remove Duplicates Without Shifting Rows in Place

Yes—but not by using Excel’s Remove Duplicates command, which removes repeated records and compacts the selected data. To keep every record in its original worksheet row, flag duplicates and filter or blank them instead; the rows then stay put.

What “without shifting rows” means in Excel

People use “remove duplicates” to mean two different things: delete repeated records from a list, or make repeated records disappear while preserving the layout around them. Those goals are not quite the same. If Excel deletes a record from the middle of a list, later records move up within the selected data. If every item must remain on its original row number, you need to hide the duplicate record or clear its contents rather than delete its row.

For example, imagine a roster in rows 2–10, with a person’s name in column A and department in column B. If row 6 repeats row 3, deleting row 6 compacts the roster. Filtering row 6 out of view leaves it in row 6. Clearing its cells leaves an empty row 6. Both latter options preserve the positions of the other records.

Why Remove Duplicates moves records

Excel’s built-in command is designed to produce a shorter list. It identifies repeated combinations in the selected columns, keeps the first occurrence, and removes later matching records from the selected data. This is useful when you want a clean, compact table, but it is not a hide-in-place feature.

The command also depends on which columns you select as the comparison fields. If you select a complete table but check only Email in the dialog, Excel treats repeated email addresses as duplicates even if the names or departments differ. It removes the later matching records, not merely the repeated email values. That can be the right choice for one-email-per-person data, but a poor choice if shared addresses are legitimate.

Safest option: flag and filter duplicate rows

Filtering is the simplest way to make duplicates disappear from view without changing row positions or deleting information. Add a helper column, mark each repeated record, and filter that column. The first occurrence stays visible, while later occurrences are hidden until you remove the filter.

Add a heading such as “Duplicate?” in an empty column beside the data.
With names in A2:A10 and departments in B2:B10, enter this in C2 to flag a repeated name-and-department pair:

=COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1

Fill the formula down the helper column. The first occurrence of each pair returns FALSE; subsequent occurrences return TRUE. The growing ranges make each row compare its values with the rows above it, so the earliest record is preserved.

Select a cell in the data and choose Data > Filter.
Open the helper-column filter and show only TRUE to view the duplicate records, or show only FALSE to view the first occurrence of each record.
Clear the filter when you want to restore the full view. The records remain in their original rows.

This method is reversible and keeps all source values. It is a good fit for review, auditing, or a worksheet where row numbers are referenced elsewhere. Keep the helper column: it documents the rule Excel used and can be recalculated when the data changes.

Choose the right fields for matching

A duplicate is only a duplicate relative to the fields you compare. A repeated customer name alone might refer to two different people; a repeated name plus email is usually a more specific match. Decide what makes a record unique before removing or hiding anything.

For two fields, the COUNTIFS example above flags records only when both fields match. For a single key, such as an ID in column A, with IDs in A2:A10, use this formula in B2 and fill down:

=COUNTIF($A$2:A2,A2)>1

COUNTIF and COUNTIFS comparisons are not case-sensitive. Thus, values such as “alex@example.com” and “Alex@example.com” are treated as a match by these formulas. Extra spaces, however, can make text values differ. If inconsistent spacing is a concern, create a cleaned comparison field with TRIM, or inspect the source values before deciding that two records are different.

Blank keys deserve attention, too. If the key column contains blanks, a repeated blank may be flagged as a duplicate. Decide whether blank-key rows should be left alone or treated as repeats; do not assume that a blank is a meaningful identifier.

Blank duplicate records but keep their row slots

If duplicate data should no longer appear in the worksheet, but the row numbers must remain unchanged, create a parallel output area or a copy of the sheet. First use a helper formula to identify duplicate records. Then use formulas in the output area to show the original values for first occurrences and empty strings for later ones.

With the duplicate flag in C2, the source value in A2, and an output column beginning in D2, enter this formula in D2 and fill it down:

=IF($C2,"",A2)

It returns a blank for rows flagged TRUE and the original value for rows flagged FALSE. For each additional source column, make a corresponding output formula—for example, to return the value from B2, use =IF($C2,"",B2). Keep the output rows aligned with the source rows; do not sort or compact the results if preserving row positions is the goal.

This creates formula results that look blank, not truly empty cells. If you need to replace the original data, first review the output, then copy it and use Home > Paste > Paste Values into an appropriate destination. Work on a backup or a duplicate sheet, especially if the source contains formulas, because pasting values over the source replaces those formulas. If other formulas rely on the original records, check their results before changing the source.

When to use Remove Duplicates instead

Use the built-in command when you genuinely want a shorter dataset and do not need the duplicate rows to retain their positions. Make a backup first, select the entire table or data range, and choose Data > Remove Duplicates. In the dialog, confirm whether the first row contains headers and check the columns that define a duplicate. Select all relevant comparison columns when a record should match only if several fields agree.

For instance, to remove repeated rows where both Product ID and Warehouse match, select the full inventory table but check only Product ID and Warehouse in the dialog. Excel keeps the first occurrence of each pair and removes later matches; other columns do not prevent a match. If you instead check every column, rows are duplicates only when all checked values match.

On Excel for Mac, the command is also available from the Data tab as Remove Duplicates; the dialog may be arranged differently by version. The same distinction applies: the command removes records rather than hiding them in place. If the row positions matter, use a helper column and filter instead.

How formulas and other tools compare

Formula-based methods are useful when you want a live, reviewable result. The helper flag updates when source data changes, and filtering leaves the source intact. A separate output area can preserve row alignment while showing blanks for repeats. The trade-off is that you must maintain the helper or output formulas and decide what should happen when new rows are added.

Excel’s UNIQUE function is another option in Microsoft 365 and Excel 2021 or later. It returns a compact list of distinct values or rows, so it is convenient for summaries, but it does not preserve original row positions. Similarly, copying unique records with Advanced Filter or removing duplicates in Power Query is useful for making a clean, compact result. Those are alternatives for producing a separate list, not ways to keep duplicate rows in their original slots.

For a recurring cleanup that needs to be repeatable, Power Query can be helpful: it can remove duplicates from a query and refresh the resulting table from its source. But the output is still a transformed list, not a worksheet with duplicates hidden in their original rows. Choose the tool based on whether you need a fixed layout, a compact report, or a repeatable data-preparation step.

Common mistakes and how to avoid them

Selecting only the key column: If you intend to remove whole records, select the full data range or table before opening Remove Duplicates. Otherwise, you risk acting on a narrower selection or getting a prompt about adjacent data.
Checking too few or too many fields: Too few fields can merge records that are not truly duplicates; too many can leave near-identical records that should match. Define the business meaning of “same record” first.
Sorting before marking duplicates: Sorting changes row order. If row positions matter, flag and filter the original data without sorting.
Assuming a filtered record is deleted: A filter only hides rows from the current view. Clear the filter to see them again.
Confusing blank formula results with empty cells: A formula returning an empty string still occupies a cell. This matters for some counts, exports, and later formulas; test the output in the way it will actually be used.

Frequently Asked Questions

Does Excel Remove Duplicates delete entire rows?
It removes duplicate records from the selected range or table based on the columns checked in its dialog. Select the complete dataset if you want all fields in a duplicate record removed together. It is not a command for hiding repeated rows while preserving their positions.

How do I hide duplicates without deleting them?
Add a helper column that flags later occurrences, then choose Data > Filter and filter the helper column. The hidden records remain in the worksheet and return when you clear the filter.

Can I remove duplicate values but keep blank rows?
Yes. Flag duplicate records, then create aligned output formulas that return empty strings for flagged rows and original values for the others. Review the output before replacing any source data; formula-generated blanks are not the same as physically empty cells.

Does Excel keep the first duplicate or the last one?
Remove Duplicates keeps the first occurrence in the current order and removes later matching records. If you want a different occurrence to be kept, decide how to identify it and arrange or flag the data deliberately before using a removal method.

Can I undo Remove Duplicates?
Usually, you can use Undo immediately after the command, but do not rely on that as your backup plan—later actions can limit what you can undo. Save a copy before removing records, particularly in a workbook with linked formulas or important row references.

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.