How To Find Unique Values In Excel

You may encounter several redundant values, especially when compiling data from multiple users. Excel offers numerous features you can use to find unique values from your data set.

You must use different methods depending on if you want to straight-up remove duplicate values or filter unique entries from the repeated values. 

Use Advance Filter

You can configure the Filter tool to show you only unique values. Excel lets you choose between pasting the unique entries in the same location or transferring them to a different location in the grid. This way, you can make a copy of your values with only unique values. 

1. Open your workbook and select your data range.
2. Head to the Data tab.
3. In the Sort & Filter section, click on Advanced.
   
4. Choose one of these options depending on your preferences:

• Filter the list, in-place: To remove all duplicate values from the list.
• Copy to another location: To paste the unique values in the location you specify in the Copy to section.
  

5. Select the box next to Unique records only > OK.

Using UNIQUE Function

Excel has a special function called the UNIQUE function you can use to only paste unique entries from the cell range. This function is, however, limited to Office 365 and the web version of MS Excel.

You can use the UNIQUE function in the following format to retrieve unique entries from your range:

=UNIQUE(range)

For example, we’ve made six entries in the range A1:A6. We entered the =UNIQUE(A1:A6) formula in cell C3. When we hit enter, it returned the four unique values.

Using Power Query

You can use Power Query to only keep unique data on your Excel sheet. Power Query however does not clear repeated values in different cases. For example, if your range contains Apple and apple, Power Query will not eliminate any of these values as the tool view them as unique entries.

1. Select your range in the Excel grid.
2. Head to Insert > Table.
   
3. Select Data from the menubar.
4. In the Get & Transform section, click From Table.
   
5. Right-click on the header and select Remove Duplicates.
   
6. From the home tab, select the flyout menu for Close & Load > Close & Load To.
   
7. Configure how you want to view your data and where you want to paste your new data.
8. Click Load.