Microsoft Excel is a powerful tool that allows you to organize and analyze data in various ways. One common task is to find and extract unique or distinct values from a list or column. In this blog post, we will explore different methods to achieve this goal.
There is slight difference between Unique and Distinct values. Unique values are values that only appears once in the list while distinct values are values that are all the different values and are counted only once in the list. In this blog we will focus on how to get distinct values of a list in Excel using the following methods.
- Remove duplicate feature (works with Excel 2013 and above)
- Using the Advanced Filter (works with Excel 2016 and above)
- Using directly Unique Formula (works with Excel 2021 and above)
Method 1: Using the Remove Duplicates feature
Works with Excel for Microsoft 365, Excel 2021, Excel 2019 and Excel 2016
Excel provides a built-in feature called “Remove Duplicates” that can quickly help you identify and remove duplicate values from a range of cells or a column. Here’s how you can use it:
- Select the column (or range of cells) from which you want to extract unique values.
- Go to the Data tab in the Excel ribbon and Click “Remove Duplicates”, a dialog box will appear.
- In the dialog box, make sure you check the box next to the column containing the values you want to make distinct is selected.
- Click “OK”
You’ll now have a clean list of distinct values in your chosen column. Excel will remove duplicate entries, leaving only unique values in the selected column.
Method 2: Using the Advanced Filter
Works with Excel for Microsoft 365, Excel 2021, Excel 2019 and Excel 2016
The Advanced Filter feature in Excel allows you to filter data based on specific criteria, including unique values. Here’s how you can use it:
- Select the range of cells or the column from which you want to extract distinct values.
- Go to the “Data” Tab and click “Advanced” button in the “Sort & Filter” group.
- In the “Advanced Filter” dialog box, select the option “Copy to another location”
- In the “Copy to” field, enter the starting cell reference where you want the distinct values to be copied.
- Check “Unique records only” checkbox.
- Click the “OK” button, and Excel will copy the distinct values to the specified location.
Method 3: Build-in Unique formula
Works with Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones
Excel 2021 and later version has a in-build function to get distinct values of list.
- Create a new column next to the column containing the values you want to extract.
- In the first cell of the new column, enter the following formula:
<strong><em>=UNIQUE(<range>,FALSE,FALSE)</em></strong>, where <range>is the range or column containing the values you want to extract. - The formula will return the distinct values from the range or column.
By using these methods, you can easily get distinct values from a list or column in Excel. Whether you prefer using built-in features or formulas, Excel provides multiple options to cater to your needs. So go ahead and start extracting those unique values!
Summary
In this blog we have shown three different ways to get distinct value of a list in Ms Excel. Microsoft has recognized utility of distinct function and has added a build in function in Ms Excel version 2021 and later to get unique and distinct value. However, in earlier version the same could be achieved by using Advanced filters and remove duplicate feature.
Related Posts
C P Gupta is a YouTuber and Blogger. He is expert in Microsoft Word, Excel and PowerPoint. His YouTube channel @pickupbrain is very popular and has crossed 9.9 Million Views.