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:

• 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 know what action each visitor performed on their last visit. To do this:

1. Sort the table by visit number in descending order.
2. Remove all duplicate user names except the first one (which represents the user’s last visit). You’ll get exactly the rows you need for filtering and analysis.

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.

This post is also available in RU.