How to Show Duplicate Records in Excel

Formatting
1. Click your first data (not heading) cell and select 'Conditional Formatting' from the Format menu. These cells are identifiable by location. A cell in the first column and first row is 'A1.' Frequently. Excel users may reserve the top row ('1') to create headings for their data. If your 'A1' cell includes the heading 'Year' and your 'A2' cell includes the data '2010,' you will want to click 'A2.'
2. Select 'Formula Is' from the drop-down menu on the 'Conditional Formatting' box and type '=COUNTIF(A:A,A2)>1' into the field that appears.
3. Click the 'Format' button in the Conditional Formatting box.
4. Click the 'Patterns' tab and choose a color, such as pink, to highlight duplicate entries by clicking on any of the available the color swatches.
5. Click 'Ok' on the 'Format' and then the 'Conditionally Formatting boxes' to close both. Your cell will become highlighted because it contains the data you are highlighting.
Application
6. Click with the left mouse button to open the Edit menu and select 'Copy' with your cursor still in the same cell.
7. Highlight the entire column by pressing 'CTRL' 'Spacebar.'
8. Open the Edit menu. Select 'Paste Special.'
9. Select the radio button (circle) next to 'Formats' on the 'Paste Special' box and click 'Ok.' Duplicate cells will become highlighted.