# excel 2000 question

**URL:** <https://boards.straightdope.com/t/excel-2000-question/92867>\
**Category:** Factual Questions\
**Created:** [November 12, 2001, 4:35am UTC](https://boards.straightdope.com/t/excel-2000-question/92867 "2001-11-12T04:35:08Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![random1](https://avatars.discourse-cdn.com/v4/letter/r/47e85d/32.png) [@random1](https://boards.straightdope.com/u/random1)\
**Post date:** [November 12, 2001, 4:35am UTC](https://boards.straightdope.com/t/excel-2000-question/92867/1 "2001-11-12T04:35:08Z")

</div>

I am putting together a spreadsheet. One of the columns is phone numbers. I want excel to tell me if I am duplicating an entry in that column. You know, that I already entered that phone number. Does anyone know how to do this? I tried asking Mr Paper Clip but he must be mad at me he has stopped answering my questions.

---

<div class="post-metadata">

**Author:** ![xash](https://avatars.discourse-cdn.com/v4/letter/x/c6cbf5/32.png) [@xash](https://boards.straightdope.com/u/xash)\
**Post date:** [November 12, 2001, 4:46am UTC](https://boards.straightdope.com/t/excel-2000-question/92867/2 "2001-11-12T04:46:07Z")

</div>

have you tried asking Mr. Waggy Tail Dog ? 😃

sometimes i wonder whether they’ll give me different answers 🙂

---

<div class="post-metadata">

**Author:** ![xash](https://avatars.discourse-cdn.com/v4/letter/x/c6cbf5/32.png) [@xash](https://boards.straightdope.com/u/xash)\
**Post date:** [November 12, 2001, 4:55am UTC](https://boards.straightdope.com/t/excel-2000-question/92867/3 "2001-11-12T04:55:21Z")

</div>

excel seems to allow you to figure out duplicates, provided they contain a text string or text with numbers…

> [@](#):
>
> \*\*  
> Quickly fill in repeated entries in a column\*\*
> 
> If the first few characters you type in a cell match an existing entry in that column, Microsoft Excel fills in the remaining characters for you. Microsoft Excel completes only those entries that contain text or a combination of text and numbers; entries that contain only numbers, dates, or times are not completed.

here’s somewhat of a work around for phone numbers…

select the phone number column, go to data/sort and sort the column ascending/descending whatever, you’ll be able to see duplicates easily.

---

<div class="post-metadata">

**Author:** ![random1](https://avatars.discourse-cdn.com/v4/letter/r/47e85d/32.png) [@random1](https://boards.straightdope.com/u/random1)\
**Post date:** [November 12, 2001, 5:01am UTC](https://boards.straightdope.com/t/excel-2000-question/92867/4 "2001-11-12T05:01:29Z")

</div>

thanks xash, but I am trying to get excel to tell me it’s a duplicate as I enter it. So I don’t enter it. I don’t really want to enter it then go back and figure out it’s a duplicate. And it is all numbers so autocomplete doesn’t work.

---

<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:** [November 12, 2001, 5:02am UTC](https://boards.straightdope.com/t/excel-2000-question/92867/5 "2001-11-12T05:02:52Z")

</div>

Highlight the cells in the column you want to validate.

Top menu: Data Validation

Settings TAB for Allow select Custom.

In the formula box put something like the following:

=COUNTIF($A$1:$A$1000,A1)=1

This one will prevent duplicates for the first 1000 rows of column A.

Use the Error Alert TAB to choose the action when a duplicate is attempted. Warning brings up a box with the error message and allows a choice to continue or stop.

---

<div class="post-metadata">

**Author:** ![Earthling](https://avatars.discourse-cdn.com/v4/letter/e/90db22/32.png) [@Earthling](https://boards.straightdope.com/u/Earthling)\
**Post date:** [November 12, 2001, 5:03am UTC](https://boards.straightdope.com/t/excel-2000-question/92867/6 "2001-11-12T05:03:32Z")

</div>

AFAIK, Excel doesn’t have a really good duplicates detector. A rather awkward workaround I’ve used is to sort the phone numbers, then go to Data \> Subtotals, and use the “Count” function to see how many times each phone number appears in your list.

---

<div class="post-metadata">

**Author:** ![random1](https://avatars.discourse-cdn.com/v4/letter/r/47e85d/32.png) [@random1](https://boards.straightdope.com/u/random1)\
**Post date:** [November 12, 2001, 5:14am UTC](https://boards.straightdope.com/t/excel-2000-question/92867/7 "2001-11-12T05:14:42Z")

</div>

don’t ask  
Thanks that is exactly what I was looking for. That worked perfectly. Again thanks alot.

---

<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:** [November 12, 2001, 5:19am UTC](https://boards.straightdope.com/t/excel-2000-question/92867/8 "2001-11-12T05:19:58Z")

</div>

My pleasure.

If you use Excel much, bookmark [these guys](http://www.microsoftexceltraining.com) have lots of free tips which I’ve stolen before.