Excel’s Unique Values Feature; How to Use?

Excel’s Unique Values Feature, When combining data from many users, you could come across a number of redundant values. You may find unique values from your data collection using a variety of Excel capabilities.

Depending on whether you wish to eliminate duplicate values or separate out unique entries from repeated ones, you must employ various techniques.

Read More: External Links in Excel; How to Find and Edit?

Excel’s Unique Values Feature, Apply an advanced filter

The Filter tool can be set up to display only distinct values. Excel gives you the option to move the unique items to a separate grid cell or to paste them in the same place. You can create a replica of your values using only unique values in this manner.

1. Select your data range and open your workbook.
2. Access the Data tab.
3. Click Advanced in the Sort & Filter section.
4. Depending on your preferences, pick one of these choices:
   In-place list filtering: to eliminate from the list every duplicate value.
   Copying to a different location to copy the specific values and paste them where you indicated in the Copy to section.
5. Turn on the checkbox next to Only unique records > OK.

Excel’s Unique Values Feature, Applying the UNIQUE Function

If you want to paste only unique entries from a cell range, you can use the unique function in Excel. However, this feature is only available in Office 365 and the online version of Microsoft Excel.

As an illustration, we created six entries in the range A1:A6. In cell C3, we typed the formula =UNIQUE(A1:A6). The four distinct values were returned when we pressed enter.

Power Query usage

Excel’s Unique Values Feature, To only keep unique data on your Excel sheet, you can utilize Power Query. However, Power Query does not remove repeated data in various scenarios. For instance, Power Query won’t remove either of the values in your range if it sees Apple and apple as unique entries.

1. In the Excel grid, choose your range.
2. Go to Table > Insert.
3. From the menu bar, choose Data.
4. Click From Table in the Get & Transform section.
5. Remove Duplicates by selecting with the right mouse click on the header.
6. Choose Close & Load > Close & Load To from the flyout menu on the home tab.
7. Set the location where you want to paste fresh data and how you want to view your data.
8. Click “load”