Microsoft Excel

Excel is an excellent tool for storing and managing data. The data can later be used to perform tasks such as data analysis or accounting. But sometimes data duplication can occur. And duplicate values in excel can hinder the aforementioned tasks.

Data duplication can occur in a spreadsheet when records from multiple sources are combined. It can also occur when a particular data is accidentally entered twice. In this article, we will look into a few easy ways to remove duplicate values in excel. When a duplicate value is removed, it does not affect any other values outside its cell or table. However, since the deletion of these values are permanent, it is advisable to check twice before you remove them.

We’ll first be looking at how you can filter for unique value in Excel. Then we’ll move on to how you can remove the duplicate values. Finally we’ll be looking into some formatting techniques.

So, without further ado, let us start with the first method to remove duplicate values in excel:

Method 1: To filter for Unique Values in Excel

Filtering for unique values is a task quite similar to removing duplicate values. Both of these tasks help present a list of unique values in the spreadsheet. There is, however, one big difference. Filtering data will only temporarily hide the duplicate values. Removing duplicate values will permanently delete them.

Thus, it is always advisable to first filter and check for unique values before you remove duplicates. To filter for unique values, follow these steps:

  1. Open excel and click on the Data tab.
  2. In the Sort & Filter group of the Data tab, click on Advanced.
    Duplicate Values Excel
  3. You will need to enter the range of cells or the table you want to check in the popup window.
  4. In case you want to filter the values in place, click on Filter the list, in place. To copy the results of the filter to another location (recommended), click on Copy to another location. Add a cell reference in the Copy to box below.Duplicate Values Excel
  5. Check mark “Unique records only” and then click Ok.

The unique values for the range will be copied to a new location.

Method 2: To remove duplicate Values in Excel

A value can be said to be a duplicate value if all the values in a row is identical to all the values in another row. It is worth noting that when excel checks for duplicate values, it only checks what appears as raw data in the cells. It cannot perceive the meaning or values of these data, so some duplicate values may still remain in the spreadsheet. For example, two values 01/15/2019 and Jan 15 2019 in two different cells will not be identified as duplicate values since the format of the date is different in the two cases.

To remove duplicate values in excel, follow these steps:

  1. In excel, select the range of cells you want to check. Or make sure the active cell is in a table.
  2. Now click on the Data tab.
  3. In the Data Tools group of the Data tab, click on Remove Duplicates.
    Duplicate Values Excel
  4. Now in the new pop up window, select one or more columns. To select all columns, click on Select All. To unselect all columns, click on Unselect all. Or if you want to select particular columns, simply check mark the columns you want to check.
    Duplicate Values Excel

Note 1: This operation will remove data from all columns. The value of the columns that you select will be used as the key to look for duplicates in all other columns. In case a duplicate is found in these columns, the entire row will be removed, even from other columns in the range.

  1. Click on Ok. A message will pop up. It will state the number of duplicate values that were removed or the number of unique values that remain. Click Ok.

Note 2: To undo any change, simply press Ctrl + Z.

Method 3: Formatting techniques to filter for unique values in excel

We will be looking into two different types of formatting techniques, quick formatting and advanced formatting.

3.1 Quick formatting:

  1. In excel, select at least one cell within a range or table.
  2. Now click on the Home tab.
  3. In the Style group of the Home tab, click the small arrow for Conditional Formatting to expand it.
    Duplicate Values Excel
  4. Select Highlight Cells Rules and then Duplicate Values.
    Duplicate Values Excel
  5. Enter a specific format and select Duplicate or Unique. Then click on Ok.
    Duplicate Values Excel

3.2 Advanced formatting:

  1. In excel, select at least one cell within a range or table.
  2. Now click on the Home tab.
  3. In the Style group of the Home tab, click the small arrow for Conditional Formatting to expand it.
    Duplicate Values Excel
  4. Select Manage Rules. A Conditional Formatting Rules Manager window will pop up.
    Duplicate Values Excel
  5. What would you like to do next?
  • If you want to add a conditional format, select New Rule. The New Formatting Rule window will pop up.
  • If you want to change a conditional format, first check to be sure if you’ve chosen the appropriate worksheet or table in the Show formatting rules for list. (If you want to choose a different range of cells, click on the collapse button to minimize the active window. You can then select additional range of cells and expand the minimized Applies to window again.) Now, simply select the rule and click on Edit rule. The edit formatting rule window will pop up.
  1. Click on Format only unique of duplicate values under the Select a Rule Type tab.
  2. In the Edit the Rule description window, choose either unique or duplicate in the Format all list. Click on Format. The Format Cells window will pop up.
    Duplicate Values Excel
  3. Select all the things (font, number, fill format, border) you want to apply when the specified condition is met by a particular cell value. Now click Ok.
    Duplicate Values Excel

Note: You can select multiple formats. All formats that you have selected are displayed in the Preview panel.

Hopefully, these methods will serve your purpose. If you need further assistance, you can refer to the online MS Excel Answers community. Or if you need specific assistance, you can ask for help in the Excel Tech Community.

LEAVE A REPLY

Please enter your comment!
Please enter your name here