Free Practice WorkbookSometimes, you may need to remove duplicate entries from a database and get a clean and unique list. Excel is a great tool to get this work done. In this article, you will learn how to find unique values in Excel.
5 Key Takeaways
- UNIQUE function can extract unique data from a list.
- Combine UNIQUE function with SORT function to get an ordered list.
- Advanced Filter can be used to get unique records in large datasets.
- Use UNIQUE with FILTER to get unique records without blank cells.
Table of Contents
How to Find Unique Values in Excel
UNIQUE Function
Creating a clean, sorted list of non-duplicate items will remove confusion and streamline the data analysis process. To achieve this in Excel, you can combine the UNIQUE function with the SORT function.
=SORT(UNIQUE(range))
The UNIQUE function will provide a list of unique items from the range. Then, the SORT function will put these unique values in order, be it numerical or alphabetical.
Filtering Unique Records
STEP 1: Click a cell within your data set.
STEP 2: On the Data tab, select ‘Advanced’ in the Sort & Filter group.
STEP 3: Follow the actions below –
- Select the Product ID column as ‘List Range’.
- Opt for ‘Copy to another location’.
- Select cell F1 as ‘Copy to’.
- Check the box for ‘Unique records only’.
STEP 4: Click OK.
Advanced Techniques
Unique values in Excel ignoring blanks
To extract unique values while ignoring blanks in Excel, you can combine the UNIQUE and FILTER functions.
The FILTER function will make sure that any cell that isn’t empty ("") is included, and then UNIQUE will check that the result consists of non-repetitive values.
Extract Unique Values from Multiple Columns
You can include all the columns in the formula to extract unique values.
This means Excel looks at data from columns A to D (rows 1 to 31) and returns only the values that are not repeated.
Tips, Tricks, and Troubleshooting
NAME Error
This error appears when Excel does not understand your formula. It can be because:
- The function name is typed incorrectly
- The function is not available in your Excel version
SPILL Error
A #SPILL! error occurs if one or more cells in the intended spill range are occupied.
To pinpoint the issue, Excel offers a way to click the error and select ‘Select Obstructing Cells’ to identify and clear them. Always ensure that the spill range is clear of any data or formatting to allow arrays to display their results fully.
FAQs
How to find unique values in a specific column?
To find unique values in a specific column in Excel, you can use the UNIQUE function.
=UNIQUE(range)
How to get a list of unique values ignoring blanks?
Combine the UNIQUE and FILTER functions to get a list of unique values ignoring blanks,
=UNIQUE(FILTER(range, range<>""))
What to do when the UNIQUE function is not working?
If the UNIQUE function isn’t working; make sure that you are using the version that supports this function. Check the spelling and syntax of the formula.
How to count distinct values across multiple criteria?
To count distinct values across multiple criteria in Excel, use a formula combining SUM, IF, and COUNTIFS
=SUM(IF(COUNTIFS(range1, criteria1, range2, criteria2, ...) = 1, 1, 0))
What’s the difference between unique and distinct values?
Unique values in a dataset occur only once, whereas distinct values refer to all different items, regardless of how often they appear.
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.
30 Days - Full Access 
