Remove duplicates in column without shifting rows

• Frequently asked questions
  • Can I remove duplicates and count unique values at the same time?
  • What if cell values differ slightly from each other?
  • Can I find duplicates in a column without deleting them?

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:

• 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.

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.

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.

Frequently asked questions

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.