Microsoft Excel is a part of the Office Suite that helps users create rich, interactive spreadsheets. Large spreadsheets sometimes suffer from duplicate entries, which ruin statistics calculated from your entries. Fortunately, Excel includes an internal process to find duplicate entries. The process allows you to highlight duplicate cells within a file.
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.
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) > 1
The "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.).
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."
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.
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.
- Photo Credit Green Highlighter on a white paper background. image by BengLim from Fotolia.com
How to Remove Duplicates from Excel Macro
A duplicate entry in Microsoft Excel is an entry in a column or row that matches other entries in the same column...