How to Find Duplicate Data in Excel Spreadsheet Columns

Written by lysis
  • Share
  • Tweet
  • Share
  • Email

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 specialised Excel formula for the columns.

Skill level:


  1. 1

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

  2. 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 dialogue box where you enter the information to check for duplicates.

  3. 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.

  4. 4

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

  5. 5

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

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.