> Find duplicate entries in excel

Microsoft Excel is an awesome application with a lot of tools and features to manage Data of any kind. The more you use it, the more you get familiar with it.
Routine tasks are generally taken care of, by a user friendly Menus and Tools, but there are some functions where you really need to explore a lot.

Working with Large Spreadsheets with a lot of data may sometimes cause duplicate entries and this can ruin all your statistic calculations. Rectifying this manually will consume a lot of your time and efforts. So what do you do?

Fortunately as always, excel has a brilliant solution for this problem. It combines 2 properties to get the results.
a) Finding duplicate entries b) Highlighting those entries for quick recognition.

I tried it myself, and was so impressed that I could'nt resist Noting it down for later use.

Here are Step wise instructions for better understanding.

Step1) The process works column wise, so concentrate on 1 column at a time. Lets take column "A" as example. The same can be repeated across worksheet.

Step2) If you have a list of data in column "A" select the first cell. This cell will hold the condition to search duplicates in the file. Once the cell is selected, go to the Menu bar and follow Format > Conditional Formatting. This will open a new dialog window.



Step3) In this new window, select "Formula Is". A text box appears, prompting you to enter a formula. Enter this formula into the text box:
=COUNTIF(A:A, A1) >1 (remove spaces if any)
The function "COUNTIF" will search through the column "A" and find any duplicate entries in the column.

Step4) Now click the "Format" button in the "Conditional Formatting" dialog box. This expands the window and presents you with some formatting options. From the sub-menu "Patterns" select the highlighting color for the duplicate entries. Click "OK."

Step5) Copy the cell that holds the condition (A1). Press "Ctrl + spacebar" to select entire column. From the "Edit" menu, click "Paste Special". From the dialog box select "Formats" and then click "OK" to copy the conditional formatting to the entire column. This should highlight all duplicate entries in the column.

Step6) Once all entries are found, you can decide whether to keep, delete or just cut and paste them to some other worksheet incase you need them later. :)

Step7) Incase you need to perform the same function on some other column, the process remains the same, just replace all "A"s in the formula with the column alphabet.