How to Find Duplicate Entries on an Excel Spreadsheet

1. Select the first cell in your column of data. This cell will serve as a template for the conditional statement that searches for duplicates in the file. After the cell is selected, click the 'Format' menu item and select 'Conditional Formatting.' This opens a new dialog window.
2. Select 'Formula Is' in the new dialog window. A text box displays, prompting you for an Excel formula. Enter the following code into the text box:=COUNTIF (A:A,A1) > 1The 'countif' function searches through the 'A' column and finds any duplicates (the formula assumes that A1 is your first cell. If your data begins at A2, replace 'A1' with 'A2' in the formula. Replace each 'A' with a 'B' to check column B, etc.).
3. Click the 'Format' button in the 'Conditional Formatting' dialog box. This opens a window prompting you for a color selection. Click a color you want to use as the highlight color for the duplicates. The 'countif' function finds the duplicates, and the format color is used to show you which cells are found. Click 'OK.'
4. With your cursor in the first data cell, click 'Copy' from the Edit menu. Press 'Ctrl spacebar' then click 'Paste Special' from the Edit menu. Select 'Formats' from the Paste Special dialog, then click 'OK' to copy the conditional formatting through the entire column.
5. Scroll down the column of data to find each duplicate. The duplicates are highlighted in the color you chose. You can either delete these duplicates, exclude them from future formulas or move them to another area of the spreadsheet.