by The tutorial demonstrates the most efficient ways to find, filter and highlight unique and distinct values in Excel.
In last week's tutorial, we explored different ways to count unique values in Excel. But occasionally you may want to view only unique or distinct values in a column - not how many, but the actual values. Before moving further, let's make sure we are on the same page with the terms. So, what are distinct and what are unique values in Excel?
- Unique values are the items that appear in a dataset only once.
- Distinct values are all different items in a list, i.e. unique values and 1st occurrences of duplicate values.
And now, let's investigate the most efficient techniques to deal with unique and distinct values in your Excel sheets.
How to find unique /distinct values in Excel
The easiest way to identify unique and distinct values in Excel is by using the IF function together with COUNTIF. There can be a few variations of the formula depending on the type of values that you want to find, as demonstrated in the following examples.
Find unique values in a column
To find distinct or unique values in a list, use one of the following formulas, where A2 is the first and A10 is the last cell with data.
How to find unique values in Excel:
=IF(COUNTIF($A$2:$A$10, $A2)=1, "Unique", "")
How to get distinct values in Excel:
=IF(COUNTIF($A$2:$A2, $A2)=1, "Distinct", "")
In the distinct formula, there is just one small deviation in the second cell reference, which however makes a big difference:
Tip. If you'd like to search for unique values between 2 columns, i.e. find values that are present in one column but absent in another, then use the formula explained in How to compare 2 columns for differences.
Find unique / distinct rows in Excel
In a similar manner, you can find unique rows in your Excel table based on values in 2 or more columns. In this case, you need to use the COUNTIFS function instead of COUNTIF to evaluate the values in several columns (up to 127 range/criteria pairs can be evaluated in a single formula).
For example, to find unique or distinct names in the list, use the following formulas:
Formula to get unique rows:
=IF(COUNTIFS($A$2:$A$10, $A2, $B$2:$B$10, $B2)=1, "Unique row", "")
Formula to find distinct rows:
=IF(COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)=1, "Distinct row", "")
Find case-sensitive unique / distinct values in Excel
If you are working with a data set where case matters, you'd need a bit more trickier array formula.
Finding case-sensitive unique values:
=IF(SUM((--EXACT($A$2:$A$10,A2)))=1,"Unique","")
Finding case-sensitive distinct values:
=IF(SUM((--EXACT($A$2:$A2,$A2)))=1,"Distinct","")
Since both are array formulas, be sure to press Ctrl + Shift + Enter to complete them correctly.
When the unique or distinct values are found, you can easily filter, select and copy them as demonstrated below.
How to filter unique and distinct values in Excel
To view only unique or distinct values in the list, filter them out by performing the following steps.
- Apply one of the above formulas to identify unique / distinct values or rows.
- Select your data, and click the Filter button on the Data tab. Or, click Sort & Filter > Filter on the Home tab in the Editing group.
- Click the filtering arrow
in the header of the column containing your formula and select the values you want to view:
How to select distinct / unique values
If you have a relatively small list of unique / distinct values, you can simply select it in the usual way using the mouse. If the filtered list contains hundreds or thousands of rows, you can use one of the following time-saving shortcuts.
To quickly select the unique or distinct list including column headers, filter unique values, click on any cell in the unique list, and then press Ctrl + A.
To select distinct or unique values without column headers, filter unique values, select the first cell with data, and press Ctrl + Shift + End to extend the selection to the last cell.
Tip. In some rare cases, mostly on very large workbooks, the above shortcuts may select both visible and invisible cells. To fix this, press either Ctrl + A or Ctrl + Shift + End first, and then press Alt + ; to select only visible cells, ignoring hidden rows.
If you have difficulties with remembering that many shortcuts, use this visual way: select the entire unique / distinct list, then go to the Home tab > Find & Select > Go to Special, and select Visible cells only.
Copy unique or distinct values to another location
To copy a list of unique values to another location, just do the following:
- Select the filtered values using the mouse or the above mentioned shortcuts.
- Press Ctrl + C to copy the selected values.
- Select the top-left cell in the destination range (it can be on the same or different sheet), and press Ctrl + V to paste the values.
How to highlight unique and distinct values in Excel
Whenever you need to highlight anything in Excel based on a certain condition, head right to the Conditional Formatting feature. More detailed information and examples follow below.
Highlight unique values in a column (built-in rule)
The fastest and easiest way to highlight unique values in Excel is to apply the inbuilt conditional formatting rule:
- Select the column of data where you want to highlight unique values.
- On the Home tab, in the Styles group, click Conditional Formatting > Highlight Cells Rules >Duplicate Values...
- In the Duplicate Values dialog window, select Unique in the left-hand box, and choose the desired formatting in the right-hand box, then click OK.
Tip. If you are not happy with any of the predefined formats, click Custom Format... (the last item in the drop-down list) and set the fill and/or font color to your liking.
As you see, highlighting unique values in Excel is the easiest task one could imagine. However, Excel's built-in rule works only for the items that appear in the list only once. If you need to highlight distinct values - unique and 1st duplicate occurrences - you will have to create your own rule based on a formula. You would also need to create a custom rule to highlight unique rows based on the values in one or more columns.
Highlight unique and distinct values in Excel (custom rule)
To highlight unique or distinct values in a column, select the data without a column header (you don't want the header to get highlighted, do you?), and create a conditional formatting rule with one of the following formulas.
Highlight unique values
To highlight the values that appear in a list just once, use the following formula:
=COUNTIF($A$2:$A$10,$A2)=1
Where A2 is the first and A10 is the last cell of the applied range.
Highlight distinct values
To highlight all different values in a column, i.e. unique values and 1st duplicate occurrences, go with the following formula:
=COUNTIF($A$2:$A2,$A2)=1
Where A2 is the top-most cell of the range.
How to create a formula based rule
To create a conditional formatting rule based on a formula, do the following:
- Go to the Home tab > Styles group, and click Conditional Formatting > New rule > Use a formula to determine which cells to format.
- Enter your formula on the Format values where this formula is true box.
- Click the Format... button and choose the fill color and/or font color you want.
- Finally, click the OK button to apply the rule.
For more detailed steps with screenshots, please see the following tutorial: How to create an Excel conditional formatting rules based on another cell value.
The below screenshot demonstrates both rules in action:
Highlight entire rows based on unique / distinct values in one column
To highlight entire rows based on unique values in a specific column, use the formulas for unique and distinct values we used in the previous example, but apply your rule to the whole table rather than to a single column.
The following screenshot demonstrates the rule that highlights rows based on distinct numbers in column A:
How to highlight unique rows in Excel
If you want to highlight rows based on the values in 2 or more columns, use the COUNTIFS function that allows specifying several criteria in a single formula.
Highlight unique rows
=COUNTIFS($A$2:$A$10,$A2, $B$2:$B$10,$B2)=1
Highlight distinct rows (unique + 1st duplicate occurrences)
=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=1
This is how you can find, filter and highlight distinct or unique values in Excel. To consolidate your knowledge, you can download the sample Find Unique Values workbook and reverse-engineer the formulas for better understanding.
Fast and easy way to find and highlight unique values in Excel
As you have just seen, Microsoft Excel provides quite a lot of useful features that can help you identify and highlight unique values in your worksheets. However, all those solutions can hardly be called intuitive and easy-to-use because they requires memorizing a handful of different formulas. Of course, it's no big deal for Excel professionals :) For those Excel users who want to save their time and effort, let me demonstrate a quick and straightforward way to find unique values in Excel.
In this final section of our today's tutorial, we are going to use our Duplicate Remover add-in for Excel. Please don't be confused by the tool's name. Apart from duplicate records, the add-in can perfectly handle unique and distinct entries, and you will make sure of it in a moment.
- Select any cell in a table where you want to find unique values and click the Duplicate Remover button on the Ablebits Data tab in the Dedupe group.
The wizard will run and the entire table will get selected automatically. So, just click Next to proceed to the next step.
Tip. When using the add-in for the first time, it makes sense to check the Create a backup copy box, just in case.
- Depending on your goal, select one of the following options, and then click Next:
- Unique
- Unique +1st occurrences (distinct)
- Select one or more columns where you want to check the values.
In this example, we want to find unique names based on values in 2 columns (First name and Last name), therefore we select both.
Tip. If your table has headers, be sure to select the My table has headers box. And if your table may have empty cells, make sure the Skip empty cells option is checked. Both options reside in the upper part of the dialog window and are usually selected by default.
- Choose one of the following actions to perform on the found values:
- Highlight unique values with color
- Select unique values
- Identify in a status column
- Copy to another location
Click the Finish button, and get the result in seconds:
This is how you can find, select and highlight unique values in Excel using our Duplicate Remover add-in. It just couldn't be simpler, right?
If finding duplicate and unique values in Excel is a common part of your daily work, just try this dedupe tool and you will be amazed with the results! Duplicate Remover as well as our other time-saving tools are included with Ultimate Suite for Excel.
Available downloads
Find Unique Values - formula examples (.xlsx file)
Ultimate Suite - trial version (.exe file)
50 comments
Hi,
please I need help with formula: =IF(COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)=1, "Distinct row", "")
I want upgrade above formula to list distinct values without blanks.
My excel is 2016.
Thank you.
P.S. Sorry for my poor english
Hello Antonio!
For your version of Excel, I can recommend these instructions to you: Get a list of distinct text values ignoring numbers and blanks. This formula extracts distinct values from a column.
=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10&"") + IF(ISTEXT($A$2:$A$10)=FALSE,1,0), 0)), "")
If you want to get distinct values from a row, use Excels filter to filter out only those rows that have the value “Distinct row” in the column with your formula.
You can solve the problem without using formulas. Pay attention to the Duplicate Remover tool. It allows you to extract distinct rows from a table. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
Hi,
thank you for fast answer but I didn't explain well my problem.
In column A I have names, column B surnames and I need in columns C and D list of distinct names and surnames without blanks rows:
A B C D
First Name Last Name First Name Last Name
Ronnie Anderson Ronnie Anderson
Robert Jones Robert Jones
Sally Brooke Sally Brooke
David White David White
Ronnie Anderson Mattias Waldau
Mattias Waldau Robert Brooke
Robert Brooke
David White
Robert Jones
I need solve that with formula (INEDX, MATCH, COUNTIF(S) and similar) because this is small part of huge excel table.
Thank you.
Hi! I have already answered what you can do in your old version of Excel. You can use Excel Filter and copy the filtered data to the right place. Or use Duplicate Remover.
Your problem can be solved by using the UNIQUE function:
=UNIQUE(A2:B20)
However, the UNIQUE function is currently only available in Excel for Microsoft 365 and Excel 2021.
Hi, I have gone through the above article and its very useful.
based on this I tried to find out some solution, however I could not able to find out the solution.
I need to extract non unique data from the 2 different sheets.
In April 24, I have employee number 1001, 1002, 1003, 1004, 1005, 1006, 1007 and in May 2024 I have employee number 1001, 1002, 1003, 1004, 1007, 1008, 1009
I should get answer as new joinees in May 2024 - 1008, 1009 and Resigned in April - 1005, 1006
I tried solution with Unique with VSTAK, however I am not getting the correct solution. Request you to guide which formula I need to use to get the correct answer.
Thanks
Hi! Use COUNTIF function to determine matching and non-matching values in the two lists. The non-matching values will return 0. Then use FILTER function to show the non-matching values.
=FILTER(A2:A8,COUNTIF(B2:B8,A2:A8)=0)
=FILTER(B2:B8,COUNTIF(A2:A8,B2:B8)=0)
Here is the article that may be helpful to you: How to compare two columns in Excel for matches and differences
Thank you for reply, your solution is working perfectly
Thank you.
Imagine writing is entire guide and then finding out there's a unique function...
Hi James,
Firstly, at the time of writing this guide the UNIQUE function did not exist.
Secondly, the UNIQUE function extracts distinct values but does not label/identify them in the source list.
Thirdly, we have a guide for the UNIQUE function too :)