Excel 2016 Tip: Highlight, hide or remove duplicate data

Updated on 23rd December 2016

Quite often you'll come across excel sheets which contain duplicated and you may want to get rid of. You can have different interpretation for Here we show you how to remove, highlight or filter duplicate data in an Excel 2016 spreadsheet.

You can have different interpretations for duplicate data. For example you may have two identical rows of data like below:

excel 2016 duplicate row

or same cell values in a single column

excel 2016 duplicate cell

or same cell values in a range of cells

excel 2016 duplicate cells

Removing duplicate rows

To remove (permanently delete) duplicate rows :

  • Select the cell range you want to check. You may also select any header rows also.
  • Click the Data menu tab, then from Data Tools group click Remove Duplicates
    excel 2016 remove duplicates
  • Select all the columns and check the box "My data has headers" if your selected range of data contains header rows.
    excel 2016 delete duplicate rows
  • Click OK and a message box will tell you how many duplicate rows have been removed.
    excel 2016 delete duplicate rows
  • The resulting data, after one row is removed will be like this
    excel 2016 delete duplicate rows

Remove duplicate cell values in a column

To remove duplicate cell values in a column or columns, the procedure is same as above except that you select only those specific columns you want to check. For example you select only the column City

excel 2016 duplicate cells

The resulting sheet will be like this

excel 2016 duplicate cells

Highlight duplicate values

Now, let's see how to highlight cells that contain duplicate value instead of deleting them.

  • Select the cell range you want to check.
  • Click Home then from Styles group select Conditional Formatting → Highlight Cell Rules → Duplicate Values
    excel 2016 highlight cells
  • Select Duplicate and a color scheme for highlighting, then click OK.
    excel 2016 highlight cells
  • Here is the result.
    excel 2016 highlight cells

Filter duplicate rows

Another option for handling duplicate data is to hide or filter duplicate rows. This is how you do it.

  • Select the cell range you want to check
  • Click Data from menu. From Sort & Filter group select Advanced
    excel 2016 filter rows
  • Select the action Filter the list, in place and check the box next to Unique Records only. Click OK.
    excel 2016 filter cells
  • Here is the result.
    excel 2016 filter cells

Post a comment

Got a question about this article? Ask our forum

Name

Your Comment

Email (We dont publish it)

Comments

Nothing yet..be the first to share wisdom.