Highlight Duplicates in Excel

sometime we have to highlight Duplicates in excel and later work on them rather then deleting them using option " Remove Duplicates " .
here we will use " Conditional Formatting " for finding-out duplicates .

Let us assume the range we want to highlight duplicates is the range A1:A100.

1) Select range A1:A100 Starting from A1. This will ensure the active cell is A1

2) Go to Format>Conditional Formatting and choose "Formula is"

3) In the right hand box type the formula as show below

=COUNTIF($A$1:$A$100,A1)>1

4) Click the "Format" button and choose any desired format to apply to any duplicate entries. i advice to use some colour so that later u can " Filter by color " Option .

5) Click "Ok" then "Ok" again .

6) Now use " Filter by color " option to Consolidate all the Duplicates .