# Removing \*all\* duplicate rows in Excel

**URL:** <https://boards.straightdope.com/t/removing-all-duplicate-rows-in-excel/593488>\
**Category:** Factual Questions\
**Created:** [August 21, 2011, 1:50am UTC](https://boards.straightdope.com/t/removing-all-duplicate-rows-in-excel/593488 "2011-08-21T01:50:14Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Don\_t\_Call\_Me\_Shirley](https://avatars.discourse-cdn.com/v4/letter/d/f4b2a3/32.png) [@Don\_t\_Call\_Me\_Shirley](https://boards.straightdope.com/u/Don_t_Call_Me_Shirley)\
**Post date:** [August 21, 2011, 1:50am UTC](https://boards.straightdope.com/t/removing-all-duplicate-rows-in-excel/593488/1 "2011-08-21T01:50:14Z")

</div>

I know how to use the remove duplicates feature in Excel 2010. That is not what I need to do. The remove duplicates feature removes the second and all subsequent duplicate entries. I want to remove \*all \*of the duplicate rows, including the first duplicated row, leaving \*only \*rows that did not have any duplicates at all.

Any ideas?

---

<div class="post-metadata">

**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\
**Post date:** [August 21, 2011, 2:07am UTC](https://boards.straightdope.com/t/removing-all-duplicate-rows-in-excel/593488/2 "2011-08-21T02:07:46Z")

</div>

Let’s say you have your data in column A. In a new column (perhaps B), use the formula:

```auto

=IF(COUNTIF(A:A,A1) > 1,"",A1)

```

Fill down throughout column B. Then column B will only show unique entries.

---

<div class="post-metadata">

**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\
**Post date:** [August 21, 2011, 2:13am UTC](https://boards.straightdope.com/t/removing-all-duplicate-rows-in-excel/593488/3 "2011-08-21T02:13:23Z")

</div>

(With the implication being you can then use the unique values to make further modifications, perhaps copying only those rows to a new spreadsheet minus the duplicates, etc.)

---

<div class="post-metadata">

**Author:** ![JWT\_Kottekoe](https://avatars.discourse-cdn.com/v4/letter/j/3ec8ea/32.png) [@JWT\_Kottekoe](https://boards.straightdope.com/u/JWT_Kottekoe)\
**Post date:** [August 21, 2011, 11:10pm UTC](https://boards.straightdope.com/t/removing-all-duplicate-rows-in-excel/593488/4 "2011-08-21T23:10:54Z")

</div>

You can use the conditional formatting feature to highlight all duplicate rows, then filter by cell color from the right click menu, then delete rows from the right click menu:

1. Select all items
2. Conditional Formatting -\> Highlight cell rules -\> Duplicate Values -\> OK
3. Right click in selection -\> Filter -\> Filter by selected cell’s color
4. Right click in selection -\> Delete Row -\> OK

---

<div class="post-metadata">

**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\
**Post date:** [August 21, 2011, 11:28pm UTC](https://boards.straightdope.com/t/removing-all-duplicate-rows-in-excel/593488/5 "2011-08-21T23:28:40Z")

</div>

> [@JWT\_Kottekoe](#):
>
> You can use the conditional formatting feature to highlight all duplicate rows, then filter by cell color from the right click menu, then delete rows from the right click menu:
> 
> 1. Select all items
> 2. Conditional Formatting -\> Highlight cell rules -\> Duplicate Values -\> OK
> 3. Right click in selection -\> Filter -\> Filter by selected cell’s color
> 4. Right click in selection -\> Delete Row -\> OK

This works much better 🙂

---

<div class="post-metadata">

**Author:** ![mytouchsr](https://avatars.discourse-cdn.com/v4/letter/m/e19b73/32.png) [@mytouchsr](https://boards.straightdope.com/u/mytouchsr)\
**Post date:** [October 12, 2011, 7:47pm UTC](https://boards.straightdope.com/t/removing-all-duplicate-rows-in-excel/593488/6 "2011-10-12T19:47:10Z")

</div>

How can i do this in vba without manually deleting all duplicate rows. Every example i have found so far only deletes the duplicates not the original as well. Thanks

---

<div class="post-metadata">

**Author:** ![keno](https://avatars.discourse-cdn.com/v4/letter/k/f9ae1b/32.png) [@keno](https://boards.straightdope.com/u/keno)\
**Post date:** [October 13, 2011, 4:08pm UTC](https://boards.straightdope.com/t/removing-all-duplicate-rows-in-excel/593488/7 "2011-10-13T16:08:52Z")

</div>

> [@mytouchsr](#):
>
> How can i do this in vba without manually deleting all duplicate rows. Every example i have found so far only deletes the duplicates not the original as well. Thanks

Simply have VBA do one of the two methods provided above

---

<div class="post-metadata">

**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\
**Post date:** [October 13, 2011, 4:28pm UTC](https://boards.straightdope.com/t/removing-all-duplicate-rows-in-excel/593488/8 "2011-10-13T16:28:16Z")

</div>

Easiest would be to turn on macro recording, then follow the steps in **JWT Kottekoe** ’s post. Voila, you have VBA.