Remove duplicates in column without shifting rows

When you need to remove duplicates in a single column without deleting or shifting rows, Excel’s built-in Remove Duplicates tool won’t help:

Why Remove Duplicates breaks the table

• If you select just one column, Excel deletes cells and shifts the remaining cells up — breaking the structure of your table.
• If you select all columns, Excel removes only fully duplicated rows — and deletes those rows entirely.
• You can select all columns and specify which columns to check, but the second problem (row deletion) still remains.

Remove duplicates without shifting rows with a formula

If you prefer built-in tools, a helper column does the job. Put this formula in B2 and fill it down along the whole column:

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

The expanding range $A$2:A2 counts how many times the value has appeared from the top of the column down to the current row. The first occurrence returns the value itself, every repeat returns an empty string. Then copy the helper column, paste it over the original one as values and delete the helper column.

To be fair, the formula has a catch: the “empty” cells now contain a zero-length string, not true blanks. They look empty, but COUNTA counts them, and Go To Special → Blanks skips them.

Clear duplicate cells in place with !SEMTools

So, can you remove repeated values in a column without shifting or deleting rows, so the data doesn’t get scrambled?

Yes — that’s exactly what the !SEMTools add-in does. It removes duplicates from a column by simply clearing duplicate cells, leaving the rest of the row intact. The command is DELETE → Cells → Duplicates → Duplicates within range → Full match; the other buttons in that submenu also ignore extra spaces, word order or word forms.

Here’s a quick example.
We want to quickly find out the most expensive device sold in the shop, for each brand. To do this:

1. Sort the main table by price in descending order, so the highest prices are at the top.
2. Remove all duplicate device names except the first one (which, after the sort, represents that brand's most expensive product).

The reverse operation of the example above is filling blank cells with the value above (or below). To avoid needing it afterward, consider working on a copy of the original column: duplicate the source column, remove duplicates in the copy, and keep the original intact.

Removing duplicates without shifting rows: FAQ

Can I remove duplicates and count unique values at the same time?

Yes. You can do this with standard PivotTable features — or even faster using the streamlined Pivot creation in !SEMTools. Learn more: summarize a table and remove duplicates.

What if cell values differ slightly from each other?

That’s a case of hidden duplicates. You’ll need to normalize the data first to remove small differences. Depending on your case, you might remove everything except letters and number characters, standardize case, run lemmatization, or alphabetize words inside each cell. After normalization, remove duplicates.

Can I find duplicates in a column without deleting them?

Yes. There are several ways to highlight or list duplicates without removing them. Details: find duplicates.

See also:

• Count unique values in Excel