# How to delete duplicate rows in Excel?

**URL:** <https://boards.straightdope.com/t/how-to-delete-duplicate-rows-in-excel/322703>\
**Category:** Factual Questions\
**Created:** [September 21, 2005, 2:46pm UTC](https://boards.straightdope.com/t/how-to-delete-duplicate-rows-in-excel/322703 "2005-09-21T14:46:25Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![FatBaldGuy](https://avatars.discourse-cdn.com/v4/letter/f/ecd19e/32.png) [@FatBaldGuy](https://boards.straightdope.com/u/FatBaldGuy)\
**Post date:** [September 21, 2005, 2:46pm UTC](https://boards.straightdope.com/t/how-to-delete-duplicate-rows-in-excel/322703/1 "2005-09-21T14:46:25Z")

</div>

I have an Excel spreadsheet which I am going to be importing into a database, but I want to delete all the rows with duplicate entries. In other words, if I have the data sorted on column D, I would like to delete all rows where the contents of D are equal to the contents of D in the row above.

I know I could do this manually, but it would be time consuming and error-prone. Does anyone know of a way to do this with a function or macro?

Thanks for your help.

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [September 21, 2005, 2:53pm UTC](https://boards.straightdope.com/t/how-to-delete-duplicate-rows-in-excel/322703/2 "2005-09-21T14:53:09Z")

</div>

This might not help at all, but deduplication is much easier in Access than Excel (and indeed, I suspect, in any other database also) - I only mention this because you mentioned this was for import.

---

<div class="post-metadata">

**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\
**Post date:** [September 21, 2005, 2:54pm UTC](https://boards.straightdope.com/t/how-to-delete-duplicate-rows-in-excel/322703/3 "2005-09-21T14:54:45Z")

</div>

stick this formula in the next column: If(d1=d2,0,1)

Then copy that column and paste special as values

Sort by the new column descending and delete everything that has a 1

---

<div class="post-metadata">

**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\
**Post date:** [September 21, 2005, 2:56pm UTC](https://boards.straightdope.com/t/how-to-delete-duplicate-rows-in-excel/322703/4 "2005-09-21T14:56:04Z")

</div>

since it is a formula put a “=” before that

---

<div class="post-metadata">

**Author:** ![FatBaldGuy](https://avatars.discourse-cdn.com/v4/letter/f/ecd19e/32.png) [@FatBaldGuy](https://boards.straightdope.com/u/FatBaldGuy)\
**Post date:** [September 21, 2005, 2:57pm UTC](https://boards.straightdope.com/t/how-to-delete-duplicate-rows-in-excel/322703/5 "2005-09-21T14:57:16Z")

</div>

> [@don't ask](#):
>
> stick this formula in the next column: If(d1=d2,0,1)
> 
> Then copy that column and paste special as values
> 
> Sort by the new column descending and delete everything that has a 1

Thanks! That will do exactly what I want. I knew I could count on the Dopers for help.

FBG

---

<div class="post-metadata">

**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\
**Post date:** [September 21, 2005, 2:57pm UTC](https://boards.straightdope.com/t/how-to-delete-duplicate-rows-in-excel/322703/6 "2005-09-21T14:57:50Z")

</div>

shit no delete the 0’s

---

<div class="post-metadata">

**Author:** ![Innuendo\_Hunter](https://avatars.discourse-cdn.com/v4/letter/i/838e76/32.png) [@Innuendo\_Hunter](https://boards.straightdope.com/u/Innuendo_Hunter)\
**Post date:** [September 21, 2005, 2:58pm UTC](https://boards.straightdope.com/t/how-to-delete-duplicate-rows-in-excel/322703/7 "2005-09-21T14:58:18Z")

</div>

Advanced Filter \> Unique Records Only

[Delete Duplicate Rows](http://office.microsoft.com/en-us/assistance/HA010346261033.aspx)

---

<div class="post-metadata">

**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\
**Post date:** [September 21, 2005, 3:06pm UTC](https://boards.straightdope.com/t/how-to-delete-duplicate-rows-in-excel/322703/8 "2005-09-21T15:06:40Z")

</div>

Personally I think it is better to learn how to use formulas for this kind of stuff - you can recycle them and vary them endlessly.

A few basic formulas, a knowledge of pivot tables and some vlookup skills allow you to do really complex stuff without knowing how Excel would like you to do it.

---

<div class="post-metadata">

**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\
**Post date:** [September 22, 2005, 12:01am UTC](https://boards.straightdope.com/t/how-to-delete-duplicate-rows-in-excel/322703/9 "2005-09-22T00:01:23Z")

</div>

The formula approach is straightforward if you are only checking duplicates in one column ( **don’t ask** has shown us how to do that).

But, let’s say you were checking for duplicate rows (i.e. check the combination of all columns for uniqueness), that formula could get unwieldy, but **Inuendo Hunter’s** method will still work regardless if the row has one column or 255.