How to Find Duplicate Data in Excel Spreadsheet Columns

By Lysis

The Microsoft Excel software is used to create spreadsheets for reports and other lists. When creating large lists of data, there is a chance that you may create duplicate data in any particular column. Luckily, the Excel spreadsheet software has the ability to locate duplicate records by highlight the fields with duplicate content. You can set this in the format configuration window using a specialized Excel formula for the columns.

Step 1

Double-click the Excel spreadsheet with the data you want to find. This opens Excel and loads the spreadsheet automatically.

Step 2

Highlight all the cells you want to check or click the column heading to select all the cells in the column. Press the "Format" menu item and then click the "Conditional Formatting" option. This opens a new dialog box where you enter the information to check for duplicates.

Step 3

Select "Formula Is" from the first drop-down box. Enter "=COUNTIF(A:A,A2)>1" into the text box for the formula. Replace "A" values with the letter of the column you are checking. This formula checks other field values and returns "True" if a duplicate value exists.

Step 4

Click the "Format" button. This opens a dialog box where you choose a color to highlight duplicate entries. Choose a color and click the "OK" button.

Step 5

View the list of fields in the spreadsheet columns. Any values founds as duplicates are highlighted.