Remove Duplicate Values In Excel Sheet

Though Remove Duplicate Values in excel is quite easy many of us not known how to do this. Therefore facing many difficulties while remove them by manually. Here we discuss

1. Remove Duplicate command on Data Tab
2. Highlight Duplicates using Conditional formatting and
3. Macro

Remove Duplicate Values Command

This command is placed under the data tab of the excel ribbon. We can use this command to remove duplicate values across single column or many columns. However, if we use this across multiple columns, it will remove only the rows which are having How to use Remove Duplicate Command

duplicates in all columns.  As an example, see this image. We can see row 2 and row 4 same except country. Further row 3 and 5 are exactly same If we select column A, B & C then this command remove row 5 only. (It is an ideal duplicate of row 2. If we select column A only, the command will remove cell A4 & A5. Nothing changes in column B & C as we select column A only.

1. Select the range you are going to clean
2. Then goto the data tab on the Ribbon.
3. After that Click Remove Duplicates button on the Data Tools Tab
4. If you selected a single column only and if there are data on the next column, excel will ask you to expand the selection.
5. Select your Choice and then press the Remove Duplicates Button.

Highlight Duplicates Using Conditional formatting

This is with the excel versions after 2007. You can highlight the duplicate values using conditional formatting and then remove them. Using this method you can just highlight them but not delete which is an advantage if you really need to visualize duplicates only. How to Highlight Duplicate Using Conditional Formatting

1. Select the range which is having duplicate values.
   
2. Then go to the home tab.
3. After that click conditional formatting on the styles group.
4. Then Click Highlight cells > Duplicate Values
5. Now you can set the formatting for the duplicate value cells. (default is red fill with dark red font)
6. Finally if you are going to
   • Visualize only, Leave it as it is.
   • Remove Duplicate Values, delete the highlighted values one by one.

Macro to Remove Duplicate Values

We can easily record a macro for first method described above and use later. If you don't know how to record a macro, then visit our post describing that.

"How to Record a Macro by KTK Tools"

First of all start macro recording. Assign a name and a shortcut Key to the macro. Then simulate the steps described in "How to use Remove Duplicate Command" section. After that add new data set to the sheet and select the range aa you selected while recording. Then press shortcut key to the macro. You can see macro activates and remove duplicates instantly.

If you are going to save the excel file with the macro, use save as and select excel macro enabled workbook as the save as type on save as pop up.