How To Find Unique Values In Excel

You might encounter a number of redundant values, particularly when compiling information from a number of customers. Excel presents quite a few options you should utilize to seek out distinctive values out of your information set.

You should use completely different strategies relying on if you wish to straight-up take away duplicate values or filter distinctive entries from the repeated values. 

Use Advance Filter

You can configure the Filter instrument to indicate you solely distinctive values. Excel enables you to select between pasting the distinctive entries in the identical location or transferring them to a distinct location within the grid. This method, you may make a replica of your values with solely distinctive values. 

1. Open your workbook and choose your information vary.
2. Head to the Data tab.
3. In the Sort & Filter part, click on on Advanced.
4. Choose one in every of these choices relying in your preferences:

• Filter the listing, in-place: To take away all duplicate values from the listing.
• Copy to a different location: To paste the distinctive values within the location you specify within the Copy to part.

5. Select the field next to Unique data solely > OK.

Using UNIQUE Function

Excel has a particular perform known as the UNIQUE perform you should utilize to solely paste distinctive entries from the cell vary. This perform is, nonetheless, restricted to Office 365 and the online model of MS Excel.

You can use the UNIQUE perform within the following format to retrieve distinctive entries out of your vary:

=UNIQUE(vary)

For instance, we’ve made six entries within the vary A1:A6. We entered the =UNIQUE(A1:A6) components in cell C3. When we hit enter, it returned the 4 distinctive values.

Using Power Query

You can use Power Query to solely keep distinctive information in your Excel sheet. Power Query nonetheless doesn’t clear repeated values in several circumstances. For instance, in case your vary comprises Apple and apple, Power Query won’t get rid of any of those values because the instrument view them as distinctive entries.

1. Select your vary within the Excel grid.
2. Head to Insert > Table.
3. Select Data from the menubar.
4. In the Get & Transform part, click on From Table.
5. Right-click on the header and choose Remove Duplicates.
6. From the house tab, choose the flyout menu for Close & Load > Close & Load To.
7. Configure the way you need to view your information and the place you need to paste your new information.
8. Click Load.